SQL Optimizer for SAP ASE 3.9 - User Guide

Run Time and Run Result

Run Time

Run time can be generated for the original and optimized SQL statements to help you choose the best performing SQL statement that is most suitable for your application. All run times are calculated to the nearest milliseconds in format HH:MM:SS.MS. The actual execution time of each SQL statement can be obtained by executing the Run for First Record, Run for All Records and Batch Run functions.

Excludes network traffic time

The calculation of the run time is based on the CPU time of the database server. Thus network traffic is excluded from the time.

Fluctuation of All Records - or - First Record Time

If you attempt to execute the SQL statement more than once, you will notice a slight fluctuation in the run time. This fluctuation is due to data cache in the memory and other processes on the CPU and database server. Therefore, for a more stable result, it is advisable to repeat the test run process more than once.

Retrieve run time for cursor

For SQL statements embedded in the cursor, the run time is obtained by creating a stored procedure, executing it, and then dropping it.

Note: For UPDATE, INSERT and DELETE SQL statements, while retrieving the run time and run result you may encounter the following Adaptive Server error message:

Can't allocate space for object 'syslogs' in database 'database_name' because the 'logsegment' segment is full. If you ran out of space in syslogs, dump the transaction log. Otherwise, use ALTER DATABASE or sp_extendsegment to increase the size of the segment

This is due to the lack of space in system table (syslogs), in which all changes to the database are recorded. Empty the transaction log in the database and re-execute. Use the following command in the SQL Workshop module:

DUMP TRANSACTION database_name WITH TRUNCATE_ONLY

go

Retrieve Run Time

Two run time functions are available for retrieving how long it takes to run the SQL statement: Run for First Record and Run for All Records. Both of these functions return run time information. You should first determine the aim of the SQL statement before retrieving the run time information.

If the SQL statement is used for batch job, you want the best time for retrieving all records. Use .

If the SQL statement is used for on-line inquiry, then you may also want to know how long it takes to retrieve the first record. Use .

The time displays in the bottom section of the SQL Optimizer window in the Time tab.

Note: For UPDATE, INSERT, and DELETE SQL statements, retrieving the First Record information by using the Run for First Record function is irrelevant as these SQL statements are processed all at once. Therefore, retrieving the First Record for UPDATE, INSERT and DELETE SQL statements will give the same result as retrieving the All Records.

Related Topics

Commit or Rollback

Retrieve Run Time for a Group of SQL

Terminate a Run Time SQL Statement

To terminate the run time process for Run for All Records

Select SQL option.

The termination of the run time is not instantaneous because it takes time to rollback the transaction and close all opened processes.

Related Topic

Retrieve Run Time

Run Time