Slow sp because of join order?

RichardBrianSmith 2015-09-08 08:56:42

i suspect there's a join order issue after insert #mst – as the estimated rows are 33800 and the actual rows is 1!

SQLkiwi 2015-09-08 11:30:49
The cardinality estimate issue starts at the Index Scan:

alt text

Looking at the properties:

alt text

The predicate SiteProductID = @SiteProductID OR @SiteProductID = 0 is difficult to estimate since the value of the local variable @SiteProductID is unknown. The optimizer makes a guess about the chances of @SiteProductID being zero at runtime, which turns out to be wrong:

alt text

The easiest solution in this case is to put OPTION (RECOMPILE) on this statement, so the optimizer can see the runtime value of @SiteProductID when compiling the plan. All things being equal, you should find this allows the optimizer to choose a more appropriate plan.

Your other main options are to use an OPTIMIZE FOR (@SiteProductID = typical_value) hint, or separate the cases @SiteProductID = 0 and @SiteProductID <> 0 into separate queries using an IF … ELSE … block.