Criteria for Terminating SQL in the Batch Run

This tips covers the options for terminating the SQL alternatives when they are executed in the Batch Run in the Optimize SQL module. The SQL optimization process in the Optimize SQL module generates several alternative SQL statements that produce the equivalent results to your original SQL statement. You are obviously looking for the SQL alternatives that run faster than your original SQL statement. Most likely, some of the alternative SQL statements Quest SQL Optimizer will run longer than your original statement and some will run faster.

When testing the performance of the alternatives, you will undoubtedly want to terminate the longer running SQL statements, once you have determined that they do not meet your performance criteria so that you do not waste time waiting for them to finish executing. You can do this using the Option settings in the Optimize SQL | Optimizer | Batch Run | Order and Termination | SQL Termination Criteria section.

When a SQL statement is terminated by the criteria that you select, ?Terminated by criteria ?is displayed in the Status column of the Alternatives window to let you know that the SQL statement exceeded the termination time specified in the options and did not run to completion.

In Quest SQL Optimizer 7.4 for Oracle or later, these three options are provided for choosing when to terminate the SQL alternatives.  

The Run time of fastest SQL option cancels SQL statements that run longer than the current best run time. With this option, your original SQL statement is run and the time from that statement is used as the termination time for the next SQL statement. When a SQL statement runs faster than this time, the faster time is used as the new termination time for the next SQL statement. So, you are always using the fastest run time that has been found so far as the termination time for the next SQL statement. This option is the default setting.

The This percentage of the original SQL run time option cancels SQL statements whose total elapsed time is longer than the specified percentage of the total elapsed time for the original SQL statement. It terminates all SQL statements that run past the calculated termination time. The default value is set at 100% so a SQL alternative will be terminated if it runs longer than your original statement.

The User defined time option cancels SQL statements that run longer than a time you specify. The option can be useful in a couple of ways. For example, if you know that your original SQL statement runs in half an hour, you can select to not run the original statement and set the termination time for 30 minutes, thereby saving yourself a half hour of testing time.

Another example for using the user defined time to cancel the SQL statements is if your original statement takes a long time to execute and there are many alternative statements, executing all statements may take considerable time. In that event, consider setting an aggressive user defined termination time. If the original SQL takes 1 hour, try a 5 minute termination time. If you find one that executes that quickly, you will certainly save yourself considerable time in testing. If no alternative statements execute in under 5 minutes, raise the termination time to 10 minutes, etc.