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

Understanding SQL count(*) Performance

Consider a query that executes a count operation on a table with over 20 million rows. The execution time varies significantly depending on the count expression, with significant delays for expressions that require comparison (e.g., count(*) = 1).

Root Cause of Slow Executions

The explanation for this performance difference lies in the optimization technique employed by SQL Server. For the first query (count() = 0), the server optimizes it to check for the presence of any rows (exists(select from BookChapters)) rather than counting them.

In contrast, for the other queries (count() = 1 or count() > 1), SQL Server uses a non-clustered index to count the rows. However, since the table in this case lacks any non-clustered indexes, the server must scan the entire table, resulting in substantial execution time.

Performance Improvements

To improve the performance of count(*) queries:

• Use Optimized Queries: Prefer existence checks (if exists(select from BookChapters)) instead of comparisons (if count() = 0).
• Create Non-Clustered Index: Introduce a non-clustered index on an appropriate column to enhance row counting efficiency.

Alternative Methods for Fast Row Counts

• sysindexes System Table: Utilize the sysindexes table to quickly obtain row counts for tables with clustered indexes.
• partition spart Rows Sum: For partitioned tables, sum the rows value from the sys.partitions table (spart) to obtain a quick row count.
• Maximum ROWS from sysindexes: In SQL 2000, retrieve the maximum ROWS value from the sysindexes table to approximate the current row count (may vary depending on update frequency).

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