Why is SQL COUNT(*) Slow, and How Can I Speed It Up?

SQL Count(*) Performance: Why It's Slow and How to Improve It

When dealing with large tables, it is important to understand how certain queries, such as 'COUNT()', affect performance. This article explores why a simple 'COUNT()' query can lead to significant performance differences based on its parameters.

In the given example, the query 'if (select count() from BookChapters) = 0' executes quickly because SQL Server optimizes it into 'if exists(select from BookChapters)'. This optimized version scans only a single row instead of counting all rows.

However, the queries 'if (select count() from BookChapters) = 1' and 'if (select count() from BookChapters) > 1' perform slower because SQL Server employs a different logic. When a table lacks any non-clustered indexes, SQL Server uses the narrowest non-clustered index for 'COUNT(*)' operations. Without a non-clustered index, it must scan the entire table, which can be time-consuming for large datasets.

Optimizing Count(*) Speed

To improve the performance of 'COUNT(*)' queries, consider these strategies:

• Add a non-clustered index: SQL Server can use a non-clustered index to narrow down the search and reduce table scanning.
• Use the system table sysindexes: This table provides row counts without incurring the overhead of a full table scan. In SQL Server 2005 or later, use the following query:

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

• For 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

By utilizing these techniques, you can significantly accelerate the execution time of 'COUNT(*)' queries, especially on large datasets.

The above is the detailed content of Why is SQL COUNT(*) Slow, and How Can I Speed It Up?. For more information, please follow other related articles on the PHP Chinese website!