Generating the SQL query plan takes 5 minutes, the query itself runs in milliseconds. What's up?

Question

I have a fairly complex (or ugly depending on how you look at it) stored procedure running on SQL Server 2008. It bases a lot of the logic on a view that has a pk table and a fk table. The fk table is left joined to the pk table slightly more than 30 times (the fk table has a poor design - it uses name value pairs that I need to flatten out. Unfortunately, it's 3rd party and I cannot change it).

Anyway, it had been running fine for weeks until I periodically noticed a run that would take 3-5 minutes. It turns out that this is the time it takes to generate the query plan. Once the query plan exists and is cached, the stored procedure itself runs very efficiently. Things run smoothly until there is a reason to regenerate and cache the query plan again.

Has anyone seen this? Why does it take so long to generate the plan? Are there ways to make it come up with a plan faster?

Answer 1

You can try using a Plan Guide. Plan generation will still last some time, but should be significantly shorter.