-----Original Message-----
From: Adam Haskell [mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 14, 2004 09:55
To: SQL
Subject: Re: Faster count?
Nice report, but was that average over multiple iterations or just one
run? If it was just one run I would love to see the results over
multiple runs it would be much more telling/accurate.
Adam
----- Original Message -----
From: Raster, Tim <[EMAIL PROTECTED]>
Date: Wed, 14 Jul 2004 09:32:11 -0500
Subject: RE: Faster count?
To: SQL <[EMAIL PROTECTED]>
SQL2k.
Sorry, the nice neat formatting I put on my report didn't come through
the list server. :-( I really did a better job than that appears!
-----Original Message-----
From: Nathan C. Smith [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 13, 2004 16:49
To: SQL
Subject: RE: Faster count?
Which SQL server did you say that was?
-----Original Message-----
From: Raster, Tim [mailto:[EMAIL PROTECTED]
Sent: Tuesday, July 13, 2004 4:31 PM
To: SQL
Subject: RE: Faster count?
Interesting debate, eh?
Here are my (more detailed) results on 9.08 million rows in TableName
with 100 iterations each:
Key:
FieldName1 = integer, indexed aid
FieldName2 = integer, not indexed plus4
FieldName3 = integer, not indexed stateid
x, y = integer constants
Green, Yellow, Red, Grey = First, Second, Third, Fourth place times
Paraphrased SQL
Description
Results
Select _____
>From TableName
straight row count
Count(*) = 2.3330342 seconds
Count(FieldName1) = 6.5305333 seconds
Count(Fieldname2) = 7.2271000 seconds
Count(Fieldname3) = 2.2319333 seconds
Count(*) is big winner
Select _____
>From TableName
Where FieldName3 = x
straight row count, 940k rows allowed through by where clause
Count(*) = 2.9682333 seconds
Count(FieldName1) = 3.0871666 seconds
Count(FieldName2) = 3.6076333 seconds
Count(FieldName3) = 3.0338666 seconds
Count(*) decent winner.
Select _____
>From TableName
Where FieldName2 between x and y
straight row count, 1.3 million rows allowed through by where clause
Count(*) = 3.3613666 seconds
Count(FieldName1) = 4.0607000 seconds
Count(FieldName2) = 4.8925000 seconds
Count(Fieldname3) = 3.7892000 seconds
Count(*) decent winner.
Select _____
>From TableName
Where FieldName1 between x and y
straight row count, 855k rows allowed through by where clause
Count(*) = 0.0364333 seconds
Count(FieldName1) = 0.0359333 seconds
Count(FieldName2) = 2.6652333 seconds
Count(FieldName3) = 0.3201333 seconds
Count(FieldName1) very slight winner only when counted field name
matches where clause (takes advantage of index rules).
Count(FieldName2) HUGE loser, so Count(*) would be much better way to go
if where clause fieldname not indexed.
It seems to me that Count(*) is either a clear winner, or in relatively
few cases, basically tied for the best performance. So, if you need to
count rows (rather than values), I'd stick with
Count(*).________________________________
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
