Time Out Limit for Statement Optimization

  • 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.

  • Maybe Grants blog[/url] will help you to better understand what's going on.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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

  • 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).

  • 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