Is there a way to set a predefined elapsed time in optimizer???

[Red_dog]

I have some sql where the original sql statement will take days to finish if it finishes at all. Is there a way where I can say skip the original statement and pick any alternative that finishes equal to or less than some time say 30 minutes?

This way I can tune very poor sql where the original statement takes along time to finish.

Thanks

[gregsika]

Hi,

You can specify a manual cancelation time in the Options dialog.

• First open the Options Dialog

• Then Select the Batch Optimize branch, Execution branch, Termination Criteria leaf

• Then set the "User defined time (mins/secs)" radio option and set a duration of 30:00 minutes

Press OK and you should be good to go.

Regards,

Greg

[cid:image001.png@01CB21B9.710E73B0]

[Red_dog]

Thanks Greg that should work. However, will the termination criteria work on the original statement.

Thanks

[gregsika]

Hi,

Not sure if the execution of the original statement can be skipped or terminated. Will discuss it internally and reply when more data is available.

Thanks,
Greg

[AlexLuk]

Hi,

The termination criteria also applied to the Original SQL. So if you know that your Original SQL runs longer than 30 mins, then you can exclude the Original SQL in the test run. To do so, you can use the Batch Run Selected function to only execute the other alternatives.

Regards,
Alex

[gregsika]

My prior instructions were given for when you are using the Batch Optimizer to optimize a group of SQL statements at once. When using the Optimize SQL module to optimize a single SQL statement, the settings are in a different location within the Options dialog.

• First open the Options Dialog
• Then navigate to the **Optimize SQL, Batch Run, Order and Termination **leaf
• Finally, set the "User defined time" option and set the termination time you desire

The actual termination time equals the cancelation delay plus the user defined termination time.
Regards,
Greg