?
Solved

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

Posted on 2005-04-20
14
Medium Priority
?
1,353 Views
Last Modified: 2012-08-14
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
Comment
Question by:amit_g
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 9

Expert Comment

by:cyberdevil67
ID: 13829788
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
 
LVL 17

Accepted Solution

by:
mokule earned 500 total points
ID: 13830038
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
 
LVL 9

Expert Comment

by:cyberdevil67
ID: 13830079
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 17

Expert Comment

by:mokule
ID: 13830123
Hey,
We are not talking about select but on count
0
 
LVL 9

Expert Comment

by:cyberdevil67
ID: 13830136
Yeah sorry I forgot to type the count in my last message.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13830894
>>Yeah sorry I forgot to type the count in my last message.<<
Did you also forget to lose the cache in your "test"?
0
 
LVL 9

Expert Comment

by:cyberdevil67
ID: 13830933
We don't cache results in development servers.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13830952
>>We don't cache results in development servers.<<
I suggest you quit while you are ahead.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13830956
But to answer the original question, as mokule has stated already there is no difference between the two.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 500 total points
ID: 13830975
>>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
 
LVL 9

Expert Comment

by:cyberdevil67
ID: 13831016
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13833717
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13906246
amit_g,
Do you still need help with this question?
0
 
LVL 58

Author Comment

by:amit_g
ID: 13911221
Sorry acperkins. I got overwhelmed with other things.

Thanks mokule and acperkins.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question