Chat now with support
Chat with Support

SQL Optimizer for SAP ASE 3.8 - 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

SQL Termination (Options)

Index Advisor > Batch Run > SQL Termination (Options)

The Index Advisor provides several index candidates for the SQL statement. The SQL statement will run faster with some of these index alternatives and it may run longer with other index alternatives. Therefore, you will want to terminate SQL statement when it is running longer. The SQL Termination tab is used to set the termination criteria for the SQL statement with each index set. If the current run time for the SQL statement exceeds the termination time, then it is terminated automatically.

Note: The termination time has a percentage delay added to it.

To define your termination criteria select one of the following options

Used Index

The Used Index option enables you to retrieve the run time of SQL statements that run faster than the original SQL statement. It terminates the SQL statement when it runs longer using an Index alternative than it ran with the current or "used" indexes. If you choose this option, the Used Index is automatically selected on the Selected Index Set tab. During the Batch Run, you cannot terminate the SQL statement when it is running with the Used Indexes because this run time is needed to determine when to terminate the Index alternatives.

Best running time SQL

The Best running time SQL option allows you to retrieve the run time of the SQL statement that is faster than the current best run time. With this option, the run time from the first execution of the SQL statement is used as the termination time. When a faster run is found, the faster time is used as the new termination time. So you are always using the current fastest run time as the termination time for the next execution.

Note: The first index is either the Used Index or the index with the lowest Est. I/O Cost. This depends on whether the Always run Used Index first option is checked on the Selected Index Set tab.

User-defined time

The User-defined time option retrieves the run time of the SQL statement when it is less than the defined time.

Combining Criteria

You can also combine User defined time with Used Index or Best running time SQL by clicking the Or user-defined time checkbox next to each one.

Run without Termination

The Run without termination option allows you to retrieve the run time of the SQL statements without any termination criteria. The SQL statement is run to completion with each Index Set.

 

Related Topic

Determine Best Performing Index Alternative

SQL Termination (Percentage Delay)

Index Advisor > Batch Run > SQL Termination (Percentage Delay)
ClosedView Batch Run Criteria--SQL Termination Window

The SQL Termination tab is used to determine the percentage delay calculation and then adds the additional time to the termination time. It is used to account for the time it takes the SQL statement to travel from the PC to the database server over the network. It also enables you to find other SQL statement that are executing close to the termination time.

Percentage delay (%)

The value entered into this field is used as a percentage to calculate the additional time that is added to the termination time. For example, if the termination time is 10 minutes and the percentage delay is 5%, then all SQL statements executed are terminated if the run time exceeds 10.5 minutes. (10+ (10 * 5%))

Minimum delay time (secs) - Range 1 - 99 (Default: 5)

This is the minimum number of seconds that is added to the termination time. It is necessary to factor into the overall termination time the time needed for the SQL statement to be sent to the database server from the PC before it starts to run. This number is only used if the Percentage delay calculation is lower than this value.

Maximum delay time (mins) - Range 1 - 9999 (Default 60)

This is the maximum number of minutes that can be added to the termination time. This number is only used if the Percentage delay calculation is higher than this value.

 

Related Topic

Determine Best Performing Index Alternative

Batch Termination

Index Advisor > Batch Run > Batch Termination
ClosedView Batch Run Criteria--Batch Termination Window

The Batch Termination page of the Batch Run Criteria window is used to determine if and when to terminate the Batch Run. It enables you to find alternative SQL statements that give you good performance improvement without having to execute every SQL alternative.

No termination

Specify to run the Batch Run to completion.

Terminate Batch Run if the specified number of index sets fall in the criteria.

Specify to terminate the Batch Run when a specified number of index set are found that meet the following requirements for terminating the Batch Run.

Number of index sets (excluding the Used Index)

Specify how many index sets must be found which show performance improvement over any index that is currently being used.

Count the index set if its elapsed time is faster than

Specify one of the following criteria to select how the performance improvement is determined.

Used Index

Count each index set where the run time for the SQL statement is faster than the time using the indexes currently on the database.

Used Index with a percentage of improvement

Count all index sets where the run time for the SQL statement is the specified percentage faster than the time using the indexes currently on the database.

User-defined time (mins/secs)

Count all index sets where the run time for the SQL statement is faster than a specified number of minutes and/or seconds.

 

Related Topic

Determine Best Performing Index Alternative

Run Time Mode

Index Advisor > 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.

 

Related Topic

Determine Best Performing Index Alternative

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating