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.
