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.