Count(*) returns ROWS, not field counts. Count(FieldName) returns
non-null field count, not rows. So, if your table had this data:
MyField
------------
1
2
null
3
Then count(*) returns 4, and count(MyField) returns 3. So make sure you
know what you are asking when you count certain fields.
By this same token, count(*) should be faster because it does not have
to check if it's null or not. (IsNull() does not have to be run on each
row in the subset)
And I agree with Ray, that count(*) is more readable, especially when
you think of it as "rows" versus "fields" or "values" that you're
counting.
-----Original Message-----
From: Adam Haskell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 13, 2004 14:40
To: SQL
Subject: Re: Faster count?
oh yeah I agree. There difference in time is well within 3 standard
deviations, so I would never argue one was faster than the other,
sorry it was not my intent. Oh and that was milliseconds, not seconds
:) Like I said they are literally the same thing to the SQL server.
I do have a question (jsut for aguements sake ;-)): How does count(*)
more clearly indicate the purpose?
* is generally a wild card and thus ambiguous where as
Count(customer_id) clearly indicates you are counting customers.
To each his own I suppose :)
Adam H
----- Original Message -----
From: Ray Thompson <[EMAIL PROTECTED]>
Date: Tue, 13 Jul 2004 15:22:35 -0400
Subject: RE: Faster count?
To: SQL <[EMAIL PROTECTED]>
OK. 200 iterations with a total difference of 2.42 seconds using almost
6
million records. That is a difference of 120 milliseconds per iteration.
That is a trivial difference. I consider count(*) more readable than
count(column) and more clearly indicates the purpose. Make code
readable.
Machines can be easily upgraded to be faster if performance is an issue,
humans are not easily and cheaply upgraded.
Ray Thompson
Tau Beta Pi ( <http://www.tbp.org/> www.tbp.org)
The Engineering Honor Society
865-546-4578
_____
From: Adam Haskell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 13, 2004 3:00 PM
To: SQL
Subject: Re: Faster count?
I just ran a test (200 itterations) on a table with 5,801,195 records.
The average for count(id) was: 98.804
The average for count(*) was: 101.22
If you look in QA they actualy run the same exact execution. count(*)
exectued on object ref_id_idx which is the colum I used in the
count(id). So atleast in my example count(id) and count(*) are the
same exact thing.
Adam
----- Original Message -----
From: Raster, Tim <[EMAIL PROTECTED]>
Date: Tue, 13 Jul 2004 13:31:48 -0500
Subject: RE: Faster count?
To: SQL <[EMAIL PROTECTED]>
Actually, I think count(*) is faster. It is a special signal to the
server that it doesn't have to count field values, but rows, which saves
time having to evaluate rows. Unlike select *, it does NOT mean all
fields... it means all rows. So count(*) is faster.
-----Original Message-----
From: Adam Haskell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 13, 2004 13:28
To: SQL
Subject: Re: Faster count?
no...its slower. Always count an indexed column if possible.
----- Original Message -----
From: Michael Dinowitz <[EMAIL PROTECTED]>
Date: Tue, 13 Jul 2004 14:24:59 -0400
Subject: Faster count?
To: SQL <[EMAIL PROTECTED]>
Is count (*) faster than count(specific_field)?
Thanks
--
Michael Dinowitz
http://www.houseoffusion.com
For all your ColdFusion needs________________________________
________________________________________________________________
_____________________________________
________________________________
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
