Execution plan with a lot of ComputeScalar operations
-
I executed the same query with and without OPTION (RECOMPILE). When I compare the 2 plans, visually the one main difference I see is that the plan without option recompile shows a lot of ComputeScalar operators and the other one doesn't have any.
Here are the two plans:
Without OPTION Recompile: https://www.brentozar.com/pastetheplan/?id=S1OcZGi85 With OPTION recompile: https://www.brentozar.com/pastetheplan/?id=ryJAfMsL5
Why does one plan use a ton of compute scalar and the other one doesn't? The query without option recompile takes nearly 4 minutes to execute. Are the compute scalar operations causing the slowness?
-
The compiler is taking your
IN
clause and attempting to optimize by removing duplicate values. It does this by taking all of your parameters, sorting them, merging them in order usingMerge Interval
, and doing aNested Loop Join
on the result.The problem is that this can take a very long time to compile with a large number of values: each one needs its own virtual table, resulting in a
Constant Scan
and aCompute Scalar
along with a bigConcatenation
at the end. This is what is causing the slowdown at compile time, before the query is run.Meanwhile, the
OPTION (RECOMPILE)
version can embed the parameters directly into the query, which means that the values would be folded together before the optimization phase of the compile even starts, speeding up overall compile time significantly. This at the cost of recompiling on every run.The upshot of all of this is that very long
IN
lists, as well as lots of parameters, can be very inefficient.I suggest that instead you consider using a Table Valued Parameter, a temp table, or a table variable (in all cases indexed with a primary clustering key) and simply joining that in the normal fashion.
As to the actual query itself, there are a number of strange things with it.
- Your final result is just
COUNT(*)
so it's unclear what the point of the giantPIVOT
was in the first place, over a normalGROUP BY
. - Likewise it's unclear why most of the tables are even there, or what the final result is supposed to signify.
- Once you've worked that out, why
LEFT JOIN
and notINNER JOIN
? Are the join columns nullable? - The
CROSS APPLY
isn't actually applying any outer references, it could beCROSS JOIN
, and in itself serves no purpose in the query.
- Your final result is just