• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1373
  • Last Modified:

Performance difference in count(1) vs. count(*)

Is there a performance difference in count(1) vs. count(*)? A link to reliable documentation about it would help me a lot in settling an argument :)
0
amit_g
Asked:
amit_g
  • 6
  • 5
  • 2
  • +1
2 Solutions
 
cyberdevil67Commented:
Hi amit_g,

 Yes there is because it is not doing the count on the entire row, but just one field. The same that it is quicker
 to do this

  select field from table

than it is to do

  select * from table

Cheers!
0
 
mokuleCommented:
No it's just the oposite count(1), and count(*) is equally fast.
Both this do the same retrieving no information from rows.
count(SomeField) is slower as it retrieves information on this field
0
 
cyberdevil67Commented:
Actually thats not true...

I ran a query for select(*) and select(field)

On a table that has around 100,000 records..

Select (*) took 100ms and select(field) took 10ms.

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
mokuleCommented:
Hey,
We are not talking about select but on count
0
 
cyberdevil67Commented:
Yeah sorry I forgot to type the count in my last message.
0
 
Anthony PerkinsCommented:
>>Yeah sorry I forgot to type the count in my last message.<<
Did you also forget to lose the cache in your "test"?
0
 
cyberdevil67Commented:
We don't cache results in development servers.
0
 
Anthony PerkinsCommented:
>>We don't cache results in development servers.<<
I suggest you quit while you are ahead.
0
 
Anthony PerkinsCommented:
But to answer the original question, as mokule has stated already there is no difference between the two.
0
 
Anthony PerkinsCommented:
>>A link to reliable documentation about it would help me a lot in settling an argument :)<<
Advice on using COUNT( )
http://www.sqlservercentral.com/columnists/chedgate/adviceoncount.asp
0
 
cyberdevil67Commented:
That was exactly what I did to do my test, used query annalyzer and displayed the time to return the results back to me. I don't use caching so why is it that every table I choose it returns quicker when I go Count(Fieldname)?
0
 
Anthony PerkinsCommented:
>>I don't use caching<<
I did not say you did, but MS SQL Server does it.

>>why is it that every table I choose it returns quicker when I go Count(Fieldname)?<<
No telling.  Try it this way:

CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

-- run first query

CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

-- run second query
0
 
Anthony PerkinsCommented:
amit_g,
Do you still need help with this question?
0
 
amit_gAuthor Commented:
Sorry acperkins. I got overwhelmed with other things.

Thanks mokule and acperkins.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now