Query Sql per determinare quante righe ha la tabella?

La funzione Count() viene spesso usata per recuperare il numero di righe contenuto in una tabella, secondo la forma

SELECT Count(*) FROM NomeTabella

In particolare, se poi si desidera filtrare in qualche modo i record da contare nella tabella, si può sempre scrivere:

SELECT Count(*) FROM NomeTabella WHERE

Perciò, se ad esempio si vuole sapere quanti nostri clienti risiedono in Italia, la SELECT COUNT (con clausola WHERE) è l’unica possibilità:

SELECT Count(*) FROM dbo.Clienti WHERE CodiceNazione = 'IT';

La SELECT Count() ha però una controindicazione: il suo utilizzo comporta una Table Scan, la tabella viene cioè letteralmente scandita riga per riga. Questo comportamente rende la SELECT Count() poco indicata per tabelle con elevato numero di record.

Un metodo più efficiente consiste nell’interrogare la vista di sistema sys.partitions; questa riporta, per ogni tabella presenta nel database, il numero di righe associate ad ognuno dei suoi indici (tranne quelli FullText, XML e Spatial); in particolare, sono tre i campi della vista che ci interessano ai fini dell’interrogazione:
object_id : è l’id dell’oggetto (in questo caso della tabella) a cui è associato l’indice.
index_id : è l’id dell’indice per la tabella; si noti che può assumere i valori
0 : è l’indice heap, creato per default se non viene creata una chiave primaria o un indice clustered
1 : è l’indice clustered (normalmente la chiave primaria)
2 o maggiore : sono gli indici non clustered
rows : riporta il numero di entry per quell’indice, in pratica il numero di righe nella tabella

Dal momento che ogni volta che aggiungiamo un record ad una tabella, viene aggiunta una entry per quel record ad ognuno degli indici creati sulla tabella e vengono aggiornate i dati delle entries per la tabella sottostante la vista, interrogando la vista sys.partitions possiamo recuperare molto velocemente il numero di record nella tabella:

SELECT partitions.rows
FROM sys.partitions
WHERE partitions.object_id = object_id(  )
  AND partitions.index_id < 2

Il trucco di limitare i record con index_id < 2 serve per ottenere un unico risultato e perdipiù basato sull’indice heap o clustered (i due sono mutualmente esclusivi).

A questo punto, per validare quanto detto, ho eseguito una serie di test confrontando diverse query per ottenere il numero di record contenuti in una tabella presente in un database residente in una istanza di Sql Server 2008R2 che gira sul mio PC di sviluppo. La prima query interroga la vista sys.partitions, la seconda esegue la classica SELECT COUNT(*), la terza esegue una SELECT COUNT(1) ed infine la quarta esegue una SELECT COUNT(ID) dove ID è la chiave primaria (quindi univoca e clustered) della tabella stessa. Prima di presentare i risultati, specifico che ogni istruzione è stata eseguita esattamente 5 volte; prima di ogni serie di 5 esecuzioni ho resettato le statistiche per avere una situazione il più possibile coerente; come “bersaglio” ho usato una tabella alquanto popolato (11 milioni di records.

Per ogni query, ho indicato dapprima l’istruzione eseguita, poi il risultato ottenuto (spoiler alert: tutte hanno restituito il valore corretto) ed infine le statistiche sull’esecuzione; in particolare è interessante confrontare l’ultimo valore di "Total Time Execution", che esprime in millisecondi la media calcolata sulle cinque esecuzioni del tempc omplessivo di esecuzione della query.

SELECT partitions.rows
FROM sys.partitions
WHERE partitions.object_id = object_id('[dbo].[TabellaPopolosa]')
AND partitions.index_id < 2
GO

(No column name)
11362689
5° tent. 4° tent. 3° tent. 2° tent. 1° tent. Valore medio
Query Profile Statistics
  Number of INSERT, DELETE and UPDATE statements 0 0 0 0 0 0.0000
  Rows affected by INSERT, DELETE, or UPDATE statements 0 0 0 0 0 0.0000
  Number of SELECT statements 1 1 1 1 1 1.0000
  Rows returned by SELECT statements 1 1 1 1 1 1.0000
  Number of transactions 0 0 0 0 0 0.0000
Network Statistics
  Number of server roundtrips 1 1 1 1 1 1.0000
  TDS packets sent from client 1 1 1 1 1 1.0000
  TDS packets received from server 1 1 1 1 1 1.0000
  Bytes sent from client 320 320 320 320 320 320.0000
  Bytes received from server 49 49 49 49 49 49.0000
Time Statistics
  Client processing time 0 6 1 7 1 3.0000
  Total execution time 1 7 1 7 2 3.6000
  Wait time on server replies 1 1 0 0 1 0.6000
SELECT Count(*) FROM [dbo].[TabellaPopolosa];
 GO

(No column name)
 11362689
5° tent. 4° tent. 3° tent. 2° tent. 1° tent. Valore medio
Query Profile Statistics
  Number of INSERT, DELETE and UPDATE statements 0 0 0 0 0 0.0000
  Rows affected by INSERT, DELETE, or UPDATE statements 0 0 0 0 0 0.0000
  Number of SELECT statements 1 1 1 1 1 1.0000
  Rows returned by SELECT statements 1 1 1 1 1 1.0000
  Number of transactions 0 0 0 0 0 0.0000
Network Statistics
  Number of server roundtrips 1 1 1 1 1 1.0000
  TDS packets sent from client 1 1 1 1 1 1.0000
  TDS packets received from server 1 1 1 1 1 1.0000
  Bytes sent from client 132 132 132 132 132 132.0000
  Bytes received from server 39 39 39 39 39 39.0000
Time Statistics
  Client processing time 1 2 3 2 3 2.2000
  Total execution time 480 484 485 481 483 482.6000
  Wait time on server replies 479 482 482 479 480 480.4000
SELECT Count(1) FROM [dbo].[TabellaPopolosa];
 GO

(No column name)
 11362689
5° tent. 4° tent. 3° tent. 2° tent. 1° tent. Valore medio
Query Profile Statistics
  Number of INSERT, DELETE and UPDATE statements 0 0 0 0 0 0.0000
  Rows affected by INSERT, DELETE, or UPDATE statements 0 0 0 0 0 0.0000
  Number of SELECT statements 1 1 1 1 1 1.0000
  Rows returned by SELECT statements 1 1 1 1 1 1.0000
  Number of transactions 0 0 0 0 0 0.0000
Network Statistics
  Number of server roundtrips 1 1 1 1 1 1.0000
  TDS packets sent from client 1 1 1 1 1 1.0000
  TDS packets received from server 1 1 1 1 1 1.0000
  Bytes sent from client 132 132 132 132 132 132.0000
  Bytes received from server 39 39 39 39 39 39.0000
Time Statistics
  Client processing time 2 1 2 3 2 2.0000
  Total execution time 475 477 486 474 479 478.2000
  Wait time on server replies 473 476 484 471 477 476.2000
SELECT Count(ID) FROM [dbo].[TabellaPopolosa];
 GO

(No column name)
 11362689
5° tent. 4° tent. 3° tent. 2° tent. 1° tent. Valore medio
Query Profile Statistics
  Number of INSERT, DELETE and UPDATE statements 0 0 0 0 0 0.0000
  Rows affected by INSERT, DELETE, or UPDATE statements 0 0 0 0 0 0.0000
  Number of SELECT statements 1 1 1 1 1 1.0000
  Rows returned by SELECT statements 1 1 1 1 1 1.0000
  Number of transactions 0 0 0 0 0 0.0000
Network Statistics
  Number of server roundtrips 1 1 1 1 1 1.0000
  TDS packets sent from client 1 1 1 1 1 1.0000
  TDS packets received from server 1 1 1 1 1 1.0000
  Bytes sent from client 142 142 142 142 142 142.0000
  Bytes received from server 39 39 39 39 39 39.0000
Time Statistics
  Client processing time 3 2 1 2 2 2.0000
  Total execution time 493 480 488 478 496 487.0000
  Wait time on server replies 490 478 487 476 494 485.0000

Come si può vedere, le varie SELECT COUNT presentano tempi medi abbastanza omogenei, tra i 478ms ed i 487ms; la SELECT sulla vista invece ci ha impiegato mediamente 3.6ms. Ancora più impressionante poi è il dettaglio sul tempo passato in attesa di una risposta da parte del server, che per le tre SELECT COUNT costituiscono la quasi totalità del tempo di escuzione complessivo, mentre nel caso di interrogazione della vista varia tra 0 (ZERO!) e 1 (UNO!) millisecondi!!!

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *

*