Slow sp because of join order?

Looking at the properties:

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:

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.