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.
The calculation of the run time is based on the CPU time of the database server. Thus network traffic is excluded from the 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.
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
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.
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.
To retrieve Run Result data from the database
Click in the SQL Optimizer window.
If the original SQL statement contains a result set then the Run Result window displays the data retrieved; otherwise it will display an information dialog field showing the number of rows affected.
You can display up to 4 result sets in the Run Result window.
Notes:
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 'sqlexp' 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
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Conditions d’utilisation Confidentialité Cookie Preference Center