4

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?

1 Answer 1

5

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 using Merge Interval, and doing a Nested 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 a Compute Scalar along with a big Concatenation 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 giant PIVOT was in the first place, over a normal GROUP 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 not INNER JOIN? Are the join columns nullable?
  • The CROSS APPLY isn't actually applying any outer references, it could be CROSS JOIN, and in itself serves no purpose in the query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.