SQL Optimizer identifies variables defined within the SQL statement for each SQL or index-set alternative and highlights them in red in the SQL Optimizer window. When the test run process begins, SQL Optimizer prompts you to enter a value for the variable and to select the data type from the Parameters window.
The run time calculation from the Run for All Records, Run for First Record and the Batch Run is used to compare the alternative SQL and index-set alternatives in the SQL Optimizer window. It is used to help determine the best performing SQL statement versions and the best indexes for your applications. All run times are calculated to the nearest milliseconds in the format of HH:MM:SS.MS.
The calculation of the run time is based on the CPU time of the database server. The data is not moved to the client computer.
If you execute an alternative more than once, there will be a slight fluctuation in the run time as database optimizer will store the SQL statement in memory. The optimizer does not need to re-parse the SQL statement because the parse tree and the access plan are already in the available cache. Other factors, such as, memory cache status, data pages and index pages distribution status, also affect the performance of SQL statements. In addition, other jobs may be competing for CPU time while the SQL statement is executing. These factors all contribute to a fluctuation of the run time from one execute to the next.
The execution process retrieves the run time for all records using the following steps.
Set the Row array size for getting elapsed time according to the Options setting on the General tab. This will have the server allocate enough buffer to accommodate the specified number of records for each Fetch call.
Get the current time as the start time.
Run the SQL.
Fetch the data until no more record are returned. Each fetch will retrieve the specific number of records from the Row array size for getting elapsed time setting to the buffer in the database server. Note that records are not actually transferred back to the client but are put into a buffer in database.
Get the current time as the end time.
Calculate the run time (end time - start time = run time).
For example: if you have a SQL that returns 250 records and the Options setting for array size is 100, then the calls that contribute to the run time would be:
Get the current time as the start time.
Execute the SQL.
Fetch (first 100 records)
Fetch (second 100 records)
Fetch (last 50 records)
Get the current time as the end time.
Calculate the run time (end time - start time = run time).
Note: Each of the calls to the network does involve the network time, since the call travels over the network from the client computer to database server. However, the data does not travel over the network, since it is retrieved to a database buffer.
Now with the same SQL but the array size changed to 500, the number of calls would be:
Get the current time as the start time.
Execute the SQL.
Fetch (all 250 records)
Get the current time as the end time.
Calculate the run time (end time - start time = run time).
You can see from this example how the Row array size for getting elapsed time setting in the Options affects the number of fetch calls and in turn affects the amount of network traffic because each fetch travels over the network.
The run time for retrieving the first record is calculated using the following steps.
Set the row array to 1.
Get the current time as the start time.
Run the SQL.
Fetch the first record to the database buffer.
Get the current time as the end time.
Calculate the run time (end time - start time = run time).
In order to obtain the correct result, SQL Optimizer executes the SQL statement against the database. After executing an UPDATE, INSERT, and DELETE statement, SQL Optimizer prompts you to commit changes that the execution made to the database:
Select Yes to commit the changes or No to roll back the changes.
SQL Optimizer provides two different measurements of performance; run time for all records and the run time the first record. Both measurements will provide an indication on the fastest running SQL statement–but with two different aims. It is best to understand the intention of the SQL statement and in what type of source code it will be embedded. Generally, if the SQL statement is used for reports, then select the SQL statement with the run time for all records. If the SQL statement is used for on-line query, then select the SQL statement with the best run time for the first record.
We recommend the use of the actual run time of the SQL or index-set alternative rather than its DB2 LUW cost to determine which alternative is the most suitable. The DB2 LUW cost is just an estimate of the alternative's performance in relation to the other alternatives. Actual performance may be much different.
After selecting the alternative with the desired results, copy and paste it into the source program (or use the displayed index DDL to create indexes as needed). We recommend that the alternative be verified and tested before pasting it into the source program.
Caution: Do not select the SQL or index-set alternative with the lowest cost value without retrieving the run time because experience has shown that alternative with the lowest cost is not necessary the SQL with the best performance.
Note: If the aim of the SQL statement is unknown, then we recommend the use of run time for all records as a performance indication.
