Which one runs faster?

Question

Which one runs faster?

Santhoshkumar KB

SSCertifiable

Points: 5066
More actions
July 29, 2009 at 12:42 am

#140188

Hi,
Which query executes faster in sql server 2005, if table has some 50k rows.
select count(id) from table --id being primary key
OR
select count(*) from table
how to check this?
Thanks,
KB
Thanks,
Santhosh
Human Knowledge Belongs To The World !!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

http://sqlinthewild.co.za/index.php/2009/04/14/on-counts/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Amit-1103237 SSC Veteran Points: 207 More actions · Answer 2

Hi KB,

Performance of Count(*) and Count(ID) completly depends on the records table has.

Both work equally fast, just that Count(ID) will evaluate the result considering the IDs whether or not they are null, means if there are null values for ID then count(ID) will give different result than what it can if there are no Null values for it.

Count(*) on the other hand counts total no. of rows in a table irrespective of null values...

So its u who have to decdide whether what to use....

I do recommend Count(*) for consistent and quick result.

Thanks

Amit

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Amit H (7/29/2009)
Both work equally fast,

No they don't. Count(column) can in some cases be as fast as count(*), it can also be significantly slower.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Amit-1103237 SSC Veteran Points: 207 More actions · Answer 4

Hi Gail,

I do agree with you that it completly depends on the scenerio of what are u standing in. With my comment to "Both work equally fast" i meant with considering the equal table structure as well as data in the table for both the cases i.e. Count(*) and count (columnName), there will be more or less no difference in using either of them..

If my views differ then I would like to correct myself with some clarification..

Thanks

Amit

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

KB (7/29/2009)
Hi,
Which query executes faster in sql server 2005, if table has some 50k rows.
select count(id) from table --id being primary key
OR
select count(*) from table
how to check this?
Thanks,
KB

What is preventing you from just running a test to see which is faster?

ps. One Orange Chip Points: 29422 More actions · Answer 6

The link provided by Gail explains clearly on why count(*) would be faster than count(column). Thanks for writing that article!!

Pradeep Singh

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 7

Michael Valentine Jones (7/29/2009)
KB (7/29/2009)
Hi,
Which query executes faster in sql server 2005, if table has some 50k rows.
select count(id) from table --id being primary key
OR
select count(*) from table
how to check this?
Thanks,
KB
What is preventing you from just running a test to see which is faster?

Hi Jones,

I do not have sysadmin server role to clean buffer after first run.

I can run for the first time by setting statistics io and time.

On second run, both will be executing in 1ms.

Is there any other way to clean buffer and rerun the query?

I have been granted with security_admin and ddladmin roles.

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Santhoshkumar KB SSCertifiable Points: 5066 More actions · Answer 8

Hi All,

One more on performance.

TableA has 140columns.

TableB has 40columns.

In one application, it has been used the 1st query below.

(but for processing only 15 columns are needed)

If I change the query inside the application to 2nd one below. Will there be any performance gain?

--1

select *

from TableA inner join TableB on TableA.id=TableB.id

where TableA.name='KB'

--2

select ID,Name,...(upto max of 15columns) --instead of *

from TableA inner join TableB on TableA.id=TableB.id

where TableA.name='KB'

Thanks,

KB

Thanks,
Santhosh

Human Knowledge Belongs To The World !!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Amit (7/29/2009)
With my comment to "Both work equally fast" i meant with considering the equal table structure as well as data in the table for both the cases i.e. Count(*) and count (columnName), there will be more or less no difference in using either of them..

If the column you're using is nullable and has no index, there most definitely will be a difference in using them. It's trivial to test and I did so in the blog post that I referenced above.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Amit-1103237 SSC Veteran Points: 207 More actions · Answer 10

Hi Gail

It was nice of you to correct me with the proper explaination...

Appreciated.

Thanks,

Amit