April 28, 2011 at 1:05 pm
I have a procedure that produces a poor execution plan. The plan has a property 'Reason for early termination of statement optimization' with a value 'Time Out'.
I can use a 'divide and conquer' approach, breaking the sql code into steps and using temp tables to store intermediate results. This generates a "Good enough plan found' value for the above property.
My question: Is there a way to set the statement optimization time out limit? I would like to try some tests on a development machine, varying this time out limit and comparing the plans SQL generates.
Thanks for any help.
April 29, 2011 at 5:42 am
No, there's no way to change it. It's funny that it's called a timeout because it actually isn't. It's a set number of times that the optimizer will attempt to find a plan and once that limit is reached, it "times out" and brings back the current least cost plan.
To get around it, yes, divide & conquer. You can try query hints, depending on the issues that you're running into, but I'm generally pretty anti-hint. Other than that, tune & tweak the query to help the optimizer as much as you can.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2011 at 12:32 pm
Thanks for the replies. I wonder if adjusting this 'parameter' might be in the cards for a future release ? (My googling indicated it is an adjustable parameter in Sybase).
April 29, 2011 at 1:01 pm
I haven't heard anything publicly that would indicate it. If I had heard anything privately I couldn't tell you about it (but I haven't).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply