SQL Optimizer for SAP ASE 3.9 - User Guide

Select one of the following options:

• Run all SQL twice if original SQL runs faster than (seconds)—Combines the Original SQL twice and all others once and the All SQL twice options into one option and allows you to determine (by the number of seconds a SQL statement runs) which option to use. The original SQL statement always runs twice. The SQL alternatives run twice if the original SQL statement runs in less time than the value specified. Otherwise, the SQL alternatives all run once.
• First one twice using the second run time and others once—The first time you access data from table, the data is cached into memory. This process takes a few moments. The next time you access that data, it is already in memory so the following SQL statements run faster. So to have a comparable test, the first SQL statement is run twice and the time from the second run is compared to the time from the other statements.
• All once—For long running SQL statements, there is no need to run them twice since the effect from caching diminishes over time.

• All twice—Running all SQL statements twice enables you to eliminate some factors that can affect the accuracy of the results. If some SQL statements have been recently executed, then the SQL information is likely to be resident in cache and it may execute faster because of that. Also, if the SQL statements use different indexes, some indexes may be resident in cache and the others are not.

  Five methods of calculating the run time are available if you select this option: Excluding the first run time, Average, Sum, Maximum, and Minimum.

• Excluding the first run time—Specify to throw out the first run time and use the second one when all SQL statements should have the necessary items in cache. This minimizes the effect that caching of the SQL statement and the indexes may have on the accuracy of the run time results.

• All multiple times—This option is suitable for SQL statements with very short run times. It executes the SQL statements the selected number of times. It calculates the run time based upon the selected calculation method. This option helps to negate the factor of other activity running on the CPU at the same time as the SQL is executed. Since the run time is calculated from the moment the SQL statement starts running to the time it finishes, if SQL statement shares the CPU with other activity, the run time includes this other activity. Four methods of calculating the run time are available if you select this option: Average, Sum, Maximum, and Minimum.

  This option is only available for SELECT statements since the INSERT, UPDATE, and DELETE statements would need a rollback between each of the multiple executions.