The Optimization process numbers the SQL alternatives according to the Estimated I/O cost.
This is a cost estimation calculated by Adaptive Server. It is only an estimation and should not be used to determine the best performing SQL.
This shows the SQL statements that have abstract plans that are compatible with the original SQL statement.
The execution time (in clock time) that it takes the SQL statement to select all records or the first record. This figure may include time that Adaptive Server spent on processing other tasks or waiting for disk or network I/O to complete. This is the accumulative run time for the SQL statement. The individual values for the Elapsed Time can be view by clicking All Records or First Record on the top right pane of the SQL Optimizer window.
If the original SQL statement run time is available, the Times of Improvement indicates how much faster the alternative SQL is than the original SQL.
The CPU Time is accumulative total for the CPU time to execute the SQL statement. The individual values for the CPU time can be view by clicking All Records or First Record on the top right pane of the SQL Optimizer window.
The Actual I/O Cost is a calculation from Adapter Server after the SQL statement is executed in the Batch Run or the Run Time function.
The Records column shows the number of records processed by the SQL statement. This number should be constant. It is an indication that the alternative SQL statements are semantically equivalent to the original SQL statement.
Information from the Batch Run is included in this column. It includes:
If the SQL was terminated by the termination criteria.
If the SQL was run more than once.
If a database error occurred.
Note: Estimated I/O cost, All Records Actual I/O Cost and First Record Actual I/O Cost are not available for logon users without sa_role privileges or with the "allow resource limits" configuration parameter turned off for the Adaptive Server. To retrieve the Estimated I/O Cost, All Records Actual I/O Cost, and First Record Actual I/O Cost estimations, you should either grant yourself sa_role, logon as another user with sa_role privileges, or turn on the "allow resource limits" parameter:
sp_configure "allow resource limits", 1
© ALL RIGHTS RESERVED. Conditions d’utilisation Confidentialité Cookie Preference Center