How many of us have typed ‘select count(*) from TABLE’ when we want to know how many rows in a table?
There is another way, by querying sys.partitions.
I’ve compared the performance and IO.
1: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
2:
3: SET STATISTICS TIME ON;
4:
5: SET STATISTICS IO ON;
6:
7: SELECT COUNT(1)
8: FROM MYBIGTABLE
9:
10: DECLARE @objid AS BIGINT;
11:
12: SELECT @objid = object_id
13: FROM sys.tables
14: WHERE name = 'MYBIGTABLE';
15:
16: SELECT SUM(rows)
17: FROM sys.partitions
18: WHERE index_id IN (0, 1)
19: AND [object_id] = @objid;
20:
21: SET STATISTICS TIME OFF;
22:
23: SET STATISTICS IO OFF;
The results from STATISTICS IO and TIME are as follows;
Table 'MYBIGTABLE'. Scan count 1, logical reads 115328, physical reads 0, read-ahead reads 115328, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 27628 ms, elapsed time = 61186 ms.
So over a minute.
For the query on sys.partitions;
Table 'syssingleobjrefs'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysidxstats'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 4, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 140 ms.
(1 row(s) affected)
Table 'sysrowsets'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 35 ms.
So 140+35=175ms, so less than a quarter of a second compared with 61 seconds.
Much much much much much much faster!
