SQL count(*) performance

by

Mikael Eriksson has a good explanation bellow why the first query is fast:

SQL server optimize it into:
if exists(select * from BookChapters). So it goes looking for the presence of one row instead of counting all the rows in the table.

For the other two queries, SQL Server would use the following rule. To perform a query like SELECT COUNT(*), SQL Server will use the narrowest
non-clustered index to count the rows. If the table does not have any
non-clustered index, it will have to scan the table.

Also, if your table has a clustered index you can get your count even faster using the following query (borrowed from this site Get Row Counts Fast!)

--SQL Server 2005/2008
SELECT OBJECT_NAME(i.id) [Table_Name], i.rowcnt [Row_Count]
FROM sys.sysindexes i WITH (NOLOCK)
WHERE i.indid in (0,1)
ORDER BY i.rowcnt desc

--SQL Server 2000
SELECT OBJECT_NAME(i.id) [Table_Name], i.rows [Row_Count]
FROM sysindexes i (NOLOCK)
WHERE i.indid in (0,1)
ORDER BY i.rows desc

It uses sysindexes system table. More info you can find here SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2012

Here is another link Why is my SELECT COUNT(*) running so slow? with another solution. It shows technique that Microsoft uses to quickly display the number of rows when you right click on the table and select properties.

select sum (spart.rows)
from sys.partitions spart
where spart.object_id = object_id(’YourTable’)
and spart.index_id < 2

You should find that this returns very quickly no matter how many tables you have.

If you are using SQL 2000 still you can use the sysindexes table to get the number.

select max(ROWS)
from sysindexes
where id = object_id(’YourTable’)

This number may be slightly off depending on how often SQL updates the sysindexes table, but it’s usually corrent (or at least close enough).

More Related Contents:

  1. Fastest way to determine if record exists
  2. SQL QUERY COUNT THEN ADD
  3. How do I obtain a Query Execution Plan in SQL Server?
  4. Count(*) vs Count(1) – SQL Server
  5. What is the best way to paginate results in SQL Server
  6. When should I use CROSS APPLY over INNER JOIN?
  7. INNER JOIN vs LEFT JOIN performance in SQL Server
  8. SQL JOIN vs IN performance?
  9. Is a view faster than a simple query?
  10. Multiple INSERT statements vs. single INSERT with multiple VALUES
  11. Fastest way to remove non-numeric characters from a VARCHAR in SQL Server
  12. Does Foreign Key improve query performance?
  13. Counting DISTINCT over multiple columns
  14. Why is a UDF so much slower than a subquery?
  15. Favourite performance tuning tricks [closed]
  16. SQL performance: WHERE vs WHERE(ROW_NUMBER)
  17. Can an INNER JOIN offer better performance than EXISTS
  18. How to check which locks are held on a table
  19. SQL Server SELECT LAST N Rows
  20. SQL Server 2008 paging methods?
  21. When using GETDATE() in many places, is it better to use a variable?
  22. When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?
  23. How can I query a value in SQL Server XML column
  24. Write a number with two decimal places SQL Server
  25. How to insert multiple records and get the identity value?
  26. How can a LEFT OUTER JOIN return more records than exist in the left table?
  27. Merge overlapping date intervals
  28. Improving performance of cluster index GUID primary key
  29. How to find out what is locking my tables?
  30. Convert bigint to datetime

Leave a Comment