June 20, 2008 at 11:14 am
between this:
SELECT COUNT(0) FROM CUSTOMER
and this:
SELECT COUNT(*) FROM CUSTOMER
Both would return the number of records, so what is going on in the background for either case?
June 20, 2008 at 12:15 pm
Select count(0) doesn't have to get any column metadata. Select count(*) does have to gather column names. It's a very minor difference and won't matter in almost any case, but the 0 is microscopically faster in some tests.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2008 at 12:23 pm
GSquared (6/20/2008)
Select count(0) doesn't have to get any column metadata. Select count(*) does have to gather column names. It's a very minor difference and won't matter in almost any case, but the 0 is microscopically faster in some tests.
It's funny - I read one of Tony R's entries concluding exactly the opposite. Count(*) is a signal stating it could care less about nullability of anything on a row, so it relies on just finding a count of records qualifying for the WHERE. Anything else, and count needs to worry about nullability of the count operand even if a constant, so the difference was in lower reads in favor of count(*).
Hmm... not that I think the difference is even worth haggling over, but it might be nice to get a definitive answer.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 20, 2008 at 12:27 pm
Matt Miller (6/20/2008)
GSquared (6/20/2008)
Select count(0) doesn't have to get any column metadata. Select count(*) does have to gather column names. It's a very minor difference and won't matter in almost any case, but the 0 is microscopically faster in some tests.It's funny - I read one of Tony R's entries concluding exactly the opposite. Count(*) is a signal stating it could care less about nullability of anything on a row, so it relies on just finding a count of records qualifying for the WHERE. Anything else, and count needs to worry about nullability of the count operand even if a constant, so the difference was in lower reads in favor of count(*).
Hmm... not that I think the difference is even worth haggling over, but it might be nice to get a definitive answer.
Funny. THIS claims that they are the SAME in terms of speed 😀
* Noel
June 20, 2008 at 12:27 pm
Truth be told, I'm going by something I read a while back, and haven't tested it myself....
So ... give me a few minutes here. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2008 at 12:37 pm
I just did a test on a 1-million row table with 7 columns of random data (Jeff's usual test table).
Without an index on the column I had in the Where clause, both versions took 188 ms of CPU time and 93 ms total time. Identical IO stats in terms of scans and reads.
With an index, both took 0 CPU and 1 total, and still identical IO.
Here are the details:
declare @T int
select @t = count(*)
from dbo.sometable
where col3 = 'aa'
/*
count(*)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 6 ms.
Table 'SomeTable'. Scan count 3, logical reads 16625, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 187 ms, elapsed time = 92 ms.
=================================
SQL Server parse and compile time:
CPU time = 6 ms, elapsed time = 6 ms.
Table 'SomeTable'. Scan count 3, logical reads 16625, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 188 ms, elapsed time = 95 ms.
=================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 6 ms.
Table 'SomeTable'. Scan count 3, logical reads 16625, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 172 ms, elapsed time = 95 ms.
=================================
=================================
=================================
count(0)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'SomeTable'. Scan count 3, logical reads 16625, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 171 ms, elapsed time = 114 ms.
=================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 6 ms.
Table 'SomeTable'. Scan count 3, logical reads 16625, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 188 ms, elapsed time = 93 ms.
=================================
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 6 ms.
Table 'SomeTable'. Scan count 3, logical reads 16625, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 188 ms, elapsed time = 94 ms.
=================================
=================================
=================================
both, with index
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'SomeTable'. Scan count 1, logical reads 6, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
*/
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2008 at 12:49 pm
GSquared (6/20/2008)
Truth be told, I'm going by something I read a while back, and haven't tested it myself....So ... give me a few minutes here. 🙂
Are you maybe thinking about Conor's post on EXISTS where he notes that Select 1 is slightly better than select *?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 20, 2008 at 2:28 pm
That's similar to what I read, but I don't remember the exact thing. Didn't consider it important enough to bookmark it. Was part of some longer rant about T-SQL coding practices.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2008 at 7:25 pm
GSquared (6/20/2008)
I just did a test on a 1-million row table with 7 columns of random data (Jeff's usual test table).Without an index on the column I had in the Where clause, both versions took 188 ms of CPU time and 93 ms total time. Identical IO stats in terms of scans and reads.
With an index, both took 0 CPU and 1 total, and still identical IO.
Close enough for me...So it's down to personal pref?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 20, 2008 at 7:55 pm
I would say so. much ado about nothing. 🙂
---------------------------------------
elsasoft.org
June 20, 2008 at 9:51 pm
And, apparently, another myth dies...
The old myth was that COUNT(*) and SELECT * performed better than anything else because it would look for the best index to use. Using (1) or a columnname might not work as well because 1 has no index and columnname might not.
I'm thinking the tests that Gus ran kinda bust up that myth. Thanks, Gus. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
June 21, 2008 at 9:00 am
Jeff Moden (6/20/2008)
And, apparently, another myth dies...The old myth was that COUNT(*) and SELECT * performed better than anything else because it would look for the best index to use. Using (1) or a columnname might not work as well because 1 has no index and columnname might not.
I'm thinking the tests that Gus ran kinda bust up that myth. Thanks, Gus. 🙂
I'm looking at this now - and I'm not so sure at all about this. I don't get the same results out of Count(*) as I do out of count(1), and count(columnname) is an altogether different effort.
My latest run on a million row table:
--COUNT(*)
Table 'TestData'. Scan count 1, logical reads 1490, physical reads 1, read-ahead reads 1492, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 94 ms, elapsed time = 362 ms.
(1 row(s) affected)
--COUNT(1)
Table 'TestData'. Scan count 1, logical reads 1490, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 143 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
(1 row(s) affected)
--COUNT(COLUMNAME)
Table 'TestData'. Scan count 1, logical reads 1490, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 297 ms, elapsed time = 290 ms.
There does seem to be some fluctuation, and count(1) does seem to at times return just as fast as count(*), but not always, and I'm not seeing it flat out beat count(*).Of course we're talking a handful of ms' each time, so I'm not sure how significant that is. I suppose setting it up to perform this a few hundred times might yield something useful.
And the count(columnname), well, that's an altogether different effort, so that takes quite a bit longer.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 21, 2008 at 9:12 am
Matt Miller (6/21/2008)
I'm looking at this now - and I'm not so sure at all about this. I don't get the same results out of Count(*) as I do out of count(1), and count(columnname) is an altogether different effort.
I see what you've posted, but I have to ask to make sure... by "results", do you mean the timing or the actual count produced?
And, based on what you've found, guess I'm going to have to setup a test matrix and do some testing...
--Jeff Moden
Change is inevitable... Change for the better is not.
June 21, 2008 at 10:06 am
Jeff Moden (6/21/2008)
Matt Miller (6/21/2008)
I'm looking at this now - and I'm not so sure at all about this. I don't get the same results out of Count(*) as I do out of count(1), and count(columnname) is an altogether different effort.I see what you've posted, but I have to ask to make sure... by "results", do you mean the timing or the actual count produced?
And, based on what you've found, guess I'm going to have to setup a test matrix and do some testing...
Timing. The IO is the same in all cases, and the count(*) and count(1) return the same results (the columnname version returns non null rows, so not always same results).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 21, 2008 at 12:22 pm
Hmm - here's a battery of things I ran repeatedly. Captured the stats via a trace to a table.
here's what I come up with:
(Average CPU, MaxCPU, MinCPU, repetitions)
select count(*) from testdata 14118793135
select count(1) from testdata 13720278135
select count(rownum) from testdata 14320378135
select count(SomeCode) from testdata 300343234135
select count(someid) from testdata 144234935135
The first 2 are fairly obvious. The next 3:
- rownum is the clustered Key, also tagged as PK.
- SomeCode is non-clustered indexed but is not tagged as NOT null (and in fact contains nulls)
- SomeID is not indexed at all, but is tagged as NOT NULL.
Interestingly - if you simply remove the NOT NULL constraint from SomeID (even though it might not hold any nulls), its perf jumps up to the same as SomeCode, even with an index in place.
So - what I see here looks something like the following:
- ultimately no diff on COUNT(*) and COUNT(any constant). with enough runs - it just looks like it even itself out. If there is a diff it's going to be REALLY small.
- COUNT(columnname) seems to operate the same as count(*) on any column specifically constrained to be not NULL.
- even with indexes, COUNT(columnname) on anything not specifically constrained to not be NULL takes quite a bit longer.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply