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.)