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.