Chat now with support
Chat with Support

SQL Optimizer for SAP ASE 3.9 - User Guide

Introduction Tutorials Preferences Editor Functions SQL Information and Functions Performance Monitor SQL Inspector SQL Collector for Monitor Server SQL Scanner Index Advisor SQL Optimizer
SQL Optimizer Overview Optimization Engine Common Coding Errors in SQL Statements What Function Should l Use to Retrieve the Run Time? Unsatisfactory Performance Results SQL Optimizer Functions SQL Editor Optimized SQL Activity Log
SQL Worksheet SQL Formatter Database Explorer Code Finder Object Extractor SQL Repository Index Impact Analyzer Index Usage Analyzer Configuration Analyzer Migration Analyzer Abstract Plan Manager User-Defined Temp Tables SQL History Legal Information

Run Time Mode

SQL Optimizer > Optimized SQL > Batch Run > Run Time Mode

The Batch Run is designed to give the most accurate result by providing options for obtaining the most accurate run time taking into account the effects of caching the data, indexes and the SQL statements. This section allows you to select one of the four options best suited to your SQL statement.

Run to retrieve Description

All records

Specify to retrieve the run time for processing All Records.

First n Records(s)

 Specify to retrieve the run time for processing n records where you specify the number of records retrieved.
Retrieve the run time by executing Description

Run SQL options

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.

Include trace statistics

 Select this checkbox to include trace statistics in the retrieved SQL.

 

Related Topic

Retrieve Run Time for a Group of SQL

Batch Run Schedule

SQL Optimizer > Optimized SQL > Batch Run > Batch Run Schedule
ClosedView Batch Run Criteria Window--Batch Run Schedule

The Batch Run Schedule criteria enable you to schedule when to start and stop the Batch Run.

Schedule setting

Start

Now

Specify to start the Batch Run immediately.

Date and Time

Specify to start the Batch Run at a specific date and time.

After

Specify to start the Batch Run after it has been running for a specified number of hours and minutes.

Until

Finished

Specify to run the entire Batch Run until all SQL statements are executed.

Date and Time

Specify to terminate the Batch Run on the specified date and time.

Duration

Specify to terminate the Batch Run after it has executed a specified number of hours and minutes.

Terminate immediately (Date/Time and Duration only)

Specify to terminate the currently executing SQL statement and all remaining unexecuted SQL statements.

Terminate after currently executing SQL is finished (Date/Time and Duration only)

Specify to finish running the currently executing SQL statement and do not run the remaining unexecuted SQL statements.

 

Related Topic

Retrieve Run Time for a Group of SQL

Commit or Rollback

SQL Optimizer > Optimized SQL > Batch Run > Commit or Rollback

In order to obtain the correct result during the Batch Run or Run Time of alternative SQL statement, the SQL statement executes against the database. However, when executing UPDATE, INSERT, DELETE statements, any changes to the database are rolled back.

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

 

Related Topic

Retrieve Run Time for a Group of SQL

View Batch Run Details

SQL Optimizer > Optimized SQL > Batch Run > View Batch Run Details

The Batch Run Details window displays a summary of the run time information for all SQL statements executed. The Batch Run Details window displays after the batch run process is completed unless the Show details on next batch run checkbox in the Batch Run Details window is unchecked.

To review the Batch Run Details window after a Batch Run

Select View | Show Batch Run Details when the SQLOptimizer window is active.

If a SQL statement has a database error during the Batch Run, an explanation of the error is included in the Batch Run details.

 

Related Topic

Retrieve Run Time for a Group of SQL

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating