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

Batch Run Schedule

Index Advisor > Batch Run > Batch Run Schedule

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

Schedule setting



Specify to start the Batch Run immediately.

Date and Time

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


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



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.


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 create the remaining indexes and benchmark the SQL statement against them.


Related Topic

View Batch Run Details

Index Advisor > Batch Run > View Batch Run Details

The Batch Run Details window displays a summary of the run time information of the SQL statements with the alternative indexes. 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.

You can review the Batch Run Details window after a Batch Run by selecting View | Show Batch Run Details when the Index Advisor 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

SQL Optimizer

SQL Optimizer Overview

SQL Optimizer > SQL Optimizer Overview

The SQL Optimizer module automates the optimization of SQL statements. It employs a unique engine that uses Artificial Intelligence to generate all the possible SQL alternatives that can be mathematically proven to be "semantically equivalent" to the original SQL statement which guarantees that the SQL alternatives will produce the exact same results as the original SQL statement. After the alternatives are generated, you can compare each SQL statement to any other SQL statements to see the different SQL coding techniques for achieving the same results. You can then test these alternative SQL statements in your environment to find the best one for your database environment.

SQL Syntax Transformation

The first step of this engine transforms the original SQL statement and produces a group of alternative SQL statements where the syntax was rewritten. Then, the SQL Optimizer rewrites each newly created SQL statement to produce another group of alternatives. The engine continues rewriting each alternative until all the SQL statements cannot be rewritten any further or until the user-defined quota for the number of SQL statements generated by syntax transformation is reached.

In the Optimization settings, you can also control whether the optimization process will generate SQL statements make use of temporary tables. You can also select to use the JOIN clause (INNER JOIN, CROSS JOIN) from the Ansi-92 SQL standard or to use the original SQL syntax for joining tables.

Applying Adaptive Server Optimization Techniques

After the SQL Optimizer has exhausted rewriting the syntax of the SQL statement, the various techniques available in Adaptive Server for optimizing a SQL statement are applied to the original SQL statement and each of the SQL alternatives until all selected options have been applied to all the SQL alternatives or until the user-defined quota is reached.

Eliminating Duplicate Query Plan

For each rewritten SQL statement, the query plan is compared to all the other query plans. One SQL alternative is selected for each unique query plan. Although the optimization process may generate hundreds of SQL alternatives, you will see only some of the alternatives since the alternatives with a duplicate query plan are eliminated.

Testing for Best Alternative

Although all the SQL alternative statements produce the same result, Adaptive Server will likely use a different path to retrieve the data for each one. It is difficult to decide which SQL statement will run faster without taking into account the database structure, indexes, and data volume, so it is important to test the SQL alternatives in your database environment using the Batch Run to determine the best SQL alternative from the run time statistics.

Intelligence Level Settings

The settings in the Optimizer options affect the amount of time it takes and the number of alternatives that are generated by the optimization process. You can quickly select to increase or decrease the intensity of the optimization process using the Intelligence Level settings to automatically select more or less options.

Create Your Own Alternative SQL

You also have the ability to test and compare your own SQL alternatives using the  Insert User-Defined SQL function.


Related Topic

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating