September 23, 2009 at 10:33 am
So I have a fairly complicated peice of code. When executed without hints, takes roughly 18 minutes to run. I added the option (recompile) thinking maybe it had a bad plan (or at least that is my train of thought from my limited query engine knowledge). This worked and the query executes in 20 seconds. But now if I take the recompile off, it goes back to 18 minutes. How do I get this to keep the plan it uses with the recompile? It was my understanding that using the recompile option is not the greatest solution...
Link to my blog http://notyelf.com/
September 23, 2009 at 11:05 am
I would read up on interacting with the data cache and the procedure cache.
September 24, 2009 at 8:22 am
Is this a stored procedure or adhoc SQL? If it is a stored procedure you might be dealing with parameter sniffing.
In some cases option recompile might be the best option. It really depends on the distribution of data and the validity of the statistics.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 24, 2009 at 9:28 am
It is an ad hoc query that will be turned into a stored procedure once we weed out the performance issues 🙂
Link to my blog http://notyelf.com/
September 24, 2009 at 4:57 pm
You can try to identify the parameter(s) for which it seems to work best and use OPTIMIZE FOR or you can create a PLAN GUIDE that works best for the majority of cases.
Have you examined and compared the query plans for each run?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply