Feeds:
Posts
Comments

Posts Tagged ‘SQL Server – Best way to get Table Row Count’

Today, we will discuss the efficient way to count records of any table. Lets suppose you have millions of records in a table. How will you calculate the record count quickly ?

Let me explain this with simple examples :

Example 1 :
First, lets use the traditional way to count records from the table.

SET STATISTICS TIME ON

Select Count(*) as [Total Records]
from [tbl_Sample]

--Result
SQL Server parse and compile time:
   CPU time = 2 ms, elapsed time = 2 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

Total Records
-------------
44040192

(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 5046 ms,  elapsed time = 26518 ms.

As per the results, the above query took almost 26 seconds.

Example 2 :
Lets go to SSMS to view how SQL Server calculates the record count.

Right click on the table and go to properties. It will give you a lot of information including record count in a fraction of seconds.

Now, we need to find out what query is running behind these properties. So lets open the SQL Server profiler. A lot of queries were running to calculate different information but I grabbed the query that calculates record count.

Given below is the query that SQL Server uses to calculate the record count of any table.

SET STATISTICS TIME ON

select SUM([rows]) as [Total records] from sys.partitions
where object_id=object_ID('tbl_Sample') And [index_id] =1

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 3 ms.

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
Total records
--------------------
44040192

(1 row(s) affected)

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.

Conclusion :
You can view a remarkable difference between both queries.
In Example 1 , the SQL needs to calculate the record count from the table but in Example 2, the Information is already calculated and you just need to query it.

Advertisements

Read Full Post »