Reporting Querys and COUNT(*) vs COUNT(1)

I was working on a reporting query today and used several legacy queries as a base. When debugging and optimizing the query I found the query took 35+ seconds to crunch down to 72 records. Before worrying about indexes, I went over the query syntax and structure and found a COUNT(*) in one of the subqueries. Changing the COUNT(*) to a COUNT(1) turned the 35 second query into a 300ms query. Take a look for yourself:

Query with COUNT(*)

Query with COUNT(1)

While the use of the * in SQL Queries is always a bad idea and considered very poor form, it can be baffling how bad it affect query performance. What other common mistakes do you see that have dramatic effects on queries?

The database in question is a development SQL Server 2005 database. I'd be interested to know if other databases suffer equally.

There are no comments for this entry.

Add Comment Subscribe to Comments

4/20/12 12:01 PM # Posted By Michael Zock

Did you try counting the key as well?

4/20/12 12:49 PM # Posted By Mike Henke

I read about something similar a couple weeks ago. Seems you must have nulls in column 1 position of the query.

4/20/12 2:12 PM # Posted By Adam Ness

Wow, that's pretty crazy... Are you sure there aren't caching effects at work here? I had a DBA who was crazy about about SELECT * being horribly inefficient, and got on our case about this with SQLServer 2000, but after several benchmarks, SELECT COUNT(*) was indistinguishable from SELECT COUNT(1).

I know that I tested the same in SQL Server 2008, and the results were identical. You might want to check your query plan, and make sure there's not some weird joining going on with the query optimizer.

4/20/12 2:18 PM # Posted By Adam Ness

Also, a more efficient way to write the SELECT COUNT(...) would potentially be to do the group on a smaller record set, i.e.:

SELECT TotalOrders, OrderVendors....
SELECT COUNT(*), OrderVendor_ID
FROM Orders
AND Orders.CreateDate > ?
GROUP BY OrderVendor_ID
) TotalOrderCounts INNER JOIN OrderVendors ON OrderVendors.OrderVendor_ID = TotalOrderCounts.OrderVendor_ID


4/21/12 10:20 AM # Posted By Marc Esher

I've observed the opposite in several cases, on SS2008. Queries with select(1) were in the hundreds of milliseconds, and replacing with select(id) got them down to tens of milliseconds.

4/22/12 4:11 PM # Posted By Phillip Senn

What about during meetins? Is it ok to write SELECT * on the whiteboard?

I've found it more efficient in meetings to write SELECT (fieldlist) so that someone doesn't feel obligated to point out that SELECT * is inefficient.

Add Comment Subscribe to Comments