Ok, so I have a non-stored-procedure query that we're using in an SSRS report. This query has been hellishly slow (I've had the original version of this query running for the past two hours, still not done), in an effort to improve it I rewrote it from scratch, and I came up with the following:
Now here's the boring word-problem portion:
We want to pull a list of the
TOP 5 clients per sales rep, but exclude the
TOP 10 total clients from that list. (So if John Doe has clients A, B, C, D, and E, and client C is one of the top 10, then only pull A, B, D, and E.)
To do this, the first query used a
IN (... NOT IN ( ) ), so I thought that the nesting of
IN was the issue, to rewrite it I did an
OUTER APPLY that really broke everything.
Anyway, I fixed all that and I ran the query, and it still took 10-15 seconds which I assumed was parameter sniffing. To investigate I ran the query in SSMS, added
OPTION (RECOMPILE) (to see what query plan would generate), and got the following:
It can be viewed here on Brent Ozar's 'Paste The Plan'. The query that generated this was:
DECLARE @Top10Temp TABLE (Id INT) INSERT INTO @Top10Temp SELECT TOP 10 Id FROM Object1 WHERE Column2 = @ReportId AND Column3 = 0 GROUP BY Id ORDER BY SUM(Column4 + Column5) DESC SELECT Object2.* FROM Object1 AS Object2 OUTER APPLY ( SELECT TOP 5 Object3.Id, SUM(Object3.Column4 + Object3.Column5) AS Column6 FROM Object1 AS Object3 WHERE Object3.Column3 = 0 AND Object3.Column7 = Object2.Column7 AND Object3.Column2 = @ReportId GROUP BY Object3.Id ORDER BY SUM(Object3.Column4 + Object3.Column5) DESC ) AS Object4 WHERE Object2.Column2 = @ReportId AND Object2.Column3 = 0 AND Object2.Id = Object4.Id AND Object2.Id NOT IN (SELECT Id FROM @Top10Temp) ORDER BY Object2.Column7 OPTION (RECOMPILE)
Now the same query but with
OPTION (OPTIMIZE FOR UNKNOWN) generated the following plan:
Which can also be viewed at 'Paste the Plan'. This plan executed in less than 1 second.
If I add
OPTION (OPTIMIZE FOR (@ReportId = #)), where
# is the same as the
@ReportId variable, I get the same query plan as the second.
Did I do something wrong? I'm having trouble understanding what happened, so any information is much appreciated. (I also really don't like trying to influence the optimizer via hints, but if it's necessary I'll keep it.)