SQL performance of count with where clause vs select

Question

I am updating some code (which i did not write) that first runs a query such as

SELECT COUNT(*) 
  FROM dbo.APP_Person as Person
 WHERE Person._pk > 0 
   AND Person.last_name LIKE 'LName%' 
   AND Person.first_name LIKE 'FName%' 
   AND Person._pk IN (SELECT _pk FROM dbo.APP_PersonView )

If the resulting count is not too large, it later runs much the same query, but with SELECT Person._pk in place of SELECT COUNT(*). Otherwise the user is told to refine the set of selections and try again.

(The fields to be checked, and the values to compare to, are from user input.)

Is there a good reason not to simply run the second form and look at the size of the result, so i don't need to run the query twice? will the SELECT COUNT(*) be that much faster than SELECT Person._pk (the primary key)?

This will run under SQLserver 2005 or later, probably 2008 or later. The table is fairly large.

yes count(*) will be faster than selecting a particular column — radar, Nov 13 '14 at 19:23
@RADAR i think `COUNT(Person._pk)` will increase performance as it is on single column — Dgan, Nov 13 '14 at 19:42
No, @Ganesh_Devlekar, you should not be able to count more efficiently than `COUNT(*)` does, because `COUNT(*)` doesn't have to look at *any* columns. — John Bollinger, Nov 13 '14 at 20:09
A lot of useful discussion about the COUNT([Column]) vs. COUNT(*) differences can be found [here](http://stackoverflow.com/questions/59294/in-sql-whats-the-difference-between-countcolumn-and-count) — AHiggins, Nov 13 '14 at 20:33

score 0 · Answer 1 · answered Nov 13 '14 at 19:27

0

Yes, there is a good reason not to simply run the second form and look at the size of the result: you can't get the size of the result without scanning through the whole result set.

You could process the results as if there were few enough until there prove to be too many, but if that processing is expensive then that's not necessarily a better option. Unless this has proven to be a bottleneck for your system, you probably have better things to do than try to improve it.

answered Nov 13 '14 at 19:27

John Bollinger

160,171
8
81
157

Thanks. This item is a bottleneck, and is slowing down users. Note that the application is not written in SQL, it sends isolated SQL calls off to SQLserver and gets the results of any single call in a block. Once I have a block of results, determining the row count is fast and easy, it's in the metadata about the returned data object. – David Siegel Nov 13 '14 at 19:52
I answered the question you posed, plus offered an alternative that perhaps would work out better for you. If you're convinced that your idea is best, then why not make the change and test? Be sure in particular to test scenarios that return far too many results. And don't forget to check how much improvement results in other cases. – John Bollinger Nov 13 '14 at 20:03

SQL performance of count with where clause vs select

1 Answers1