T-SQL optimization tips (3): Select count (*)

Select count (*) requires a full table scan to return the number of rows in the table. This is a very time-consuming operation for a table with a large data size.

To obtain the number of all rows in a table, consider the alternative solution obtained from the system table sysindexes.

Run the following statement in the query Analyzer:

 

1 Set statistics Io on
2 go
3 select count (*) from E1 daily sales details
4 go
5 select rows from sysindexes where id = object_id ('e1 daily sales details') and indid <2
6 go
7 set statistics Io off

 

The number of returned rows is 13239473.

Returned message display --

(The number of affected rows is 1)
Table 'e1 daily sales details '. Scan count68, Logical read51302Secondary, physical read5Times, pre-read49831Times.

(The number of affected rows is 1)
Table 'sysindexes '. Scan count1, Logical read2Secondary, physical read0Times, pre-read0Times.

 

DOS & donts --
4. Avoid using select count (*);
[Note: to attract attention, all the summary of DOS & donts in this series have certain preconditions. The general premise is "unless necessary" and "unless necessary".]