Suggestions for improving count performance on a SQL query?

Question

I've got a query that is taking a very long time to run due to a select similar to:

SELECT 
Count( Distinct t1.v1),
Count (Distinct 
Case t2.v1 When 'blah' then t1.v1
Else null
End ),
....
FROM t1
LEFT JOIN t2 ON t1.v3 = t2.v3
WHERE t1.myDate BETWEEN @start and @end
AND t2.v5 = @id

Can anyone suggest any good methods for improving this performance?

Answer 1

As there are No Where clause predicates (no filters) on this query it will involve a complete table scan or index scan no matter what you do (unless the inner join restricts the resultset...).

So the only improvement that you can get is possibly to affect what type of join is being done. To improve the performance of the join, make sure there is an index on the t1.v3 and t2.v3 columns....

Answer 2

Difficult to say without the big picture, what you're trying to achieve etc.

But the immediate thing to check based on that is indexes - do you have suitable indexes (e.g. on t1.v3 / t2.v3)? If you do have appropriate indexes, are they indexes fragmented/statistics out of date?

What does the execution plan say?

Answer 3

You are probably getting 1 scan on t1 and 1 seek on t2, if you are getting anything else, either the indexes are not appropriately in place, or you didn't give us enough information.

If you are getting 1 scan on t1 and 1 seek on t2, and you think this query is close to the appropriate solution (e.g. you cannot get the information you want from a faster query that looks nothing like this one), you may have the optimal plan.

Answer 4

Your LEFT JOIN on t2 with a filter on t2.v5 = @id changes this to an inner join. You'll need ...LEFT JOIN t2 ON t1.v3 = t2.v3 AND t2.v5 = @id...

Next, what indexes do you have? Based on what I see

• t1: (mydate, v3) INCLUDE (v1)
• t2: (v3, v5) INCLUDE (v1)

You could try reversing the key cols too

Finally, ensure all data types are correct and match (even in the 2nd count). Implicit conversions kill performance.