{"id":183,"date":"2009-04-14T16:58:57","date_gmt":"2009-04-14T14:58:57","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/?p=183"},"modified":"2010-10-06T17:41:08","modified_gmt":"2010-10-06T15:41:08","slug":"on-counts","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2009\/04\/14\/on-counts\/","title":{"rendered":"On Counts"},"content":{"rendered":"

Or “What’s the fastest way to count the rows?”<\/em><\/p>\n

It’s a fairly common to need to know the number of rows in a table, the number of rows that match a certain condition or whether or not there are any rows that match a condition. There’s also a number of ways of doing so, some better than others. The problem being that counting is not a cheap operation, especially on big tables. It’s not as bad as a sort, but it still can be expensive.<\/p>\n

So, given that, let’s take a look at some of the ways.<\/p>\n

Querying the metadata<\/h3>\n

If all that’s needed is the number of rows in the table, and it’s not 100% important that the value be completely accurate all the time, the system metadata can be queried. In SQL 2000 and below, that info was in sysindexes. In 2005 and higher it’s been moved into sys.partitions.<\/p>\n

SELECT OBJECT_NAME(object_id) AS TableName, SUM(rows) AS TotalRows\r\nFROM sys.partitions\r\nWHERE index_id in (0,1)\r\nAND object_id = OBJECT_ID('TableName')\r\nGROUP BY object_id<\/pre>\n
The advantage of this approach is that it is fast. Since it’s not actually counting anything and, in fact, isn’t even accessing the table that’s being counted, it’s the fastest way to get the count of rows in the table.<\/p>\n
The disadvantage is it can only get the number of rows in the table and cannot consider any criteria at all. It also may not be 100% accurate, depending how and when the table’s rowcount metadata is updated by the SQL engine.<\/p>\n
Checking for existence of rows<\/h3>\n
If all that’s needed is to know whether or not a row exists for a certain criteria, use EXISTS rather than counting the entire number of rows and checking that it’s greater than 0. It’s (slightly) quicker and it uses fewer IOs.<\/p>\n
CREATE TABLE TestingCounts (\r\nID INT IDENTITY PRIMARY KEY,\r\nAChar CHAR(1),\r\nSomePaddingColumn CHAR(400)\r\n)\r\n\r\nINSERT INTO TestingCounts (SomePaddingColumn, AChar)\r\nSELECT TOP (100000) a.name, LEFT(b.name,1)\r\nFROM sys.columns a CROSS JOIN sys.columns b\r\nGO\r\n\r\nSET STATISTICS IO ON\r\nSET STATISTICS TIME ON\r\nGO\r\n\r\nDECLARE @RowCount INT\r\nSELECT @RowCount = COUNT(*) FROM TestingCounts WHERE ID>50000\r\nIF (@RowCount > 0)\r\nPRINT 'Count - Rows Exist'\r\n\r\nIF EXISTS (SELECT 1 FROM TestingCounts WHERE ID>50000)\r\nPRINT 'Exists - Rows Exist'<\/pre>\n
Count:<\/p>\n
Table ‘TestingCounts’. Scan count 1, logical reads 2645, physical reads 0.
\nSQL Server Execution Times:
\nCPU time = 15 ms,\u00a0 elapsed time = 13 ms.<\/p><\/blockquote>\n
Exists:<\/p>\n
Table ‘TestingCounts’. Scan count 1, logical reads 3
\nSQL Server Execution Times:
\nCPU time = 0 ms,\u00a0 elapsed time = 0 ms.<\/p><\/blockquote>\n
Count(*) vs Count(ColumnName)<\/h3>\n
The difference between these two is not always understood, and I’ve often seen hints and tips that say that COUNT(ColumnName) is better than COUNT(*). That advice is wrong and it’s possible that it comes from the (correct) advice that SELECT <Column List> is better than SELECT *.<\/p>\n
In truth, COUNT(*) means to count the number of rows in the resultset. COUNT(ColumnName) means to count the number of rows in the resultset where that column is not null. Hence, COUNT(ColumnName) will return a different result to COUNT(*) if the column that’s used is nullable and contains any nulls.<\/p>\n
So what does that mean for the performance of the two?<\/p>\n
Because COUNT(*) just means count the rows then, assuming that there are no other criteria in the query, to satisfy it SQL will find the smallest index that exists on the table and scan the leaf pages to count the rows.<\/p>\n
COUNT(ColumnName) requires that the column specified be checked to see if there are any null values. If the column that’s specified is defined as NOT NULLable, them SQL treats it just like a COUNT(*). If the column is nullable then, regardless or whether or not it contains any NULLs, it has to be checked. That means that to evaluate a COUNT(ColumnName), SQL must either scan an index that has that column in it or it must do a full table scan.<\/p>\n
This means that, at best, a COUNT(Column) can be as fast as a COUNT(*), but it cannot be faster. It also means that COUNT(*) may return a different value to COUNT(Column)<\/p>\n
-- using the same table created above\r\n\r\nCREATE INDEX idx_AChar ON TestingCounts (Achar)\r\nGO\r\n\r\nSELECT COUNT(*) FROM TestingCounts WHERE ID%3 = 0\r\nSELECT COUNT(SomePaddingColumn) FROM TestingCounts WHERE ID%3 = 0\r\n<\/pre>\n
Count(*):<\/p>\n
Table ‘TestingCounts’. Scan count 1, logical reads 138.
\nSQL Server Execution Times:
\nCPU time = 16 ms,\u00a0 elapsed time = 15 ms.<\/p><\/blockquote>\n
COUNT(SomePaddingColumn):<\/p>\n
Table ‘TestingCounts’. Scan count 1, logical reads 5285
\nSQL Server Execution Times:
\nCPU time = 47 ms,\u00a0 elapsed time = 47 ms.<\/p><\/blockquote>\n
Of course, if there are other conditions in the query or a group by, the situation becomes rather more complicated as to what indexes will be used. The main point though remains true. Because COUNT(ColumnName) has to check that column for NULL values and COUNT(*) does not, COUNT(ColumnName) cannot be the faster option.<\/p>\n","protected":false},"excerpt":{"rendered":"
Or “What’s the fastest way to count the rows?” It’s a fairly common to need to know the number of rows in a table, the number of rows that match a certain condition or whether or not there are any… (Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[15,17],"tags":[],"class_list":["post-183","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-t-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-2X","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/183","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=183"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/183\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=183"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=183"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=183"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}