Why is SELECT COUNT(1) slow? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Why is SELECT COUNT(1) slow?

Author

Topic

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-20 : 11:08:20

Bit of an internal engine question

Say I try and get the count of rows from a huge table


SELECT COUNT(1) FROM <TheTable>

Why is this not an instant operation? Assuming the Table has a clustered index (which is a balanced tree), shouldn't the CI be able to report immediately how big it is?

What actually happens is a table scan and then a parallel count

Is there a better way to find out how many rows a huge table has?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2012-01-20 : 11:35:21

Does COUNT(*) optimise better/differently from COUNT(1)? (Sure its come up before, I can't remember the answer)

SELECT Total_Rows= SUM(st.row_count)
FROM sys.dm_db_partition_stats st
WHERE     object_name(object_id) = 'MyTable'
      AND (index_id < 2)

but assume it is not accurate to-this-moment-in-time

sp_spaceused might do? Similar inaccuracy, and presumably its using dm_db_partition_stats too

Does NOLOCK make a difference? are there routes that avoid using SNAPSHOT (assuming you have READ_COMMITTED+_SNAPSHOT on the DB)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-20 : 11:41:59

cheers Kristen, Thats good enough for me:

quote:

SELECT Total_Rows= SUM(st.row_count)
FROM sys.dm_db_partition_stats st
WHERE object_name(object_id) = 'MyTable'
AND (index_id < 2)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-20 : 11:42:17

Count(*) = Count(1) in every way.
It's not an instant operation because you are asking for a count of the rows that qualify for the query. That there's no filter on this particular query does not change that.

For an instant count of the rows, query sys.partitions or sys.dm_db_partition_stats. Both are transactionally accurate and consistent

--
Gail Shaw
SQL Server MVP

Kristen
Test

22859 Posts

Posted - 2012-01-20 : 12:07:22

"Both are transactionally accurate and consistent"

That's well worth knowing, because from memory I had read that they were not necessarily up-to-date.

I suppose I'm a bit surprised that there isn't an [internal] optimisation for COUNT(*) - but perhaps its as [or more] common to do a WHERE filter, rather than whole-table, in which case no way around actually traversing an index

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-20 : 12:28:11

There is an optimisation, SQL scans the smallest index it has on the table that is valid for the count. That's why count(column) is less efficient (as well as being different logically)

p.s. I should have said 'supposed to be', if those counts are wrong, it's a bug. sysindexes was known to be wrong in SQL 2000, sys.partitions is supposed to be correct.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-20 : 13:23:36

Define "Slow"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-20 : 13:24:35

Aren't

IF EXISTS (SELECT * FROM table)

different than

IF EXISTS (SELECT 1 FROM table)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-20 : 14:00:28

No, absolutely the same.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-20 : 16:29:44