An alternative to the Count(*) function.

Sometimes rapid code development doesn’t always produce the most efficient code.
Take the age old line of code

SELECT COUNT(*) FROM MyTable.

Obviously this will give you the row count for a table, but at what cost?

Doing any SELECT * from a table will ultimately result in a table or clustered index scan.

ExecutionPlan

Turning on STATISTICS IO on reveals 4534 logical reads just to return the row count of 1,133,242.

Table 'tblERPInvoiceData'. Scan count 1, logical reads 4534, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Starting with SQL Server 2005, Microsoft introduced a DMV, sys.dm_db_partition_stats, that provides you with the same information at a fraction of the cost. It requires a little more coding, but once you turn on STATISTICS IO, you will see the performance benefit.

USE [db_name];
SELECT
s.name AS 'SchemaName'
,o.name AS 'TableName'
,SUM(p.row_count) AS 'RowCount'
FROM sys.dm_db_partition_stats p
JOIN sys.objects o ON o.object_id = p.object_id
JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE p.index_id < 2 AND o.type = 'U'
AND s.name = 'dbo'
AND o.name = 'tblERPInvoiceData'
GROUP BY s.name,o.name
ORDER BY s.name,o.name;
GO

Since we’re querying a DMV, we never touch the base table. We can see here we only need 16 logical reads to return the same row count of 1,133,242, and the tblERPInvoiceData table is nowhere in our execution plan.
Capture

Table 'sysidxstats'. Scan count 1, logical reads 9, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 0, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysclsobjs'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

By using the DMV, we have improved the query performance and reduced the total I/O count by nearly 100%. Another added benefit of using the DMV, is we won’t need locks on the base table and therefore will avoid the possibility of blocking other queries hitting that table.

Advertisements