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

Optimize Using Abstract Plan Only

SQL Optimizer > SQL Editor > Optimize Using Abstract Plan Only

This function is only available if you are connected to Adaptive Server 15 or later.

The Optimize using abstract plan function optimizes the original SQL with the objective of producing an optimal abstract plan. Therefore the transformed SQL statements are not shown. After optimization, the alternative abstract plans are shown with the original SQL statement in the left pane of the SQL Optimizer window. All abstract plans are compatible with the original SQL statement.

To optimize using only the abstract plan

  1. In the SQL Optimizer window, enter the SQL statement in the SQL Editor pane of the SQL Optimizer.

  2. Click image\B_OptimizeUsingAP.gif.

The time it takes to optimize is dependent on the complexity of the original SQL statement and the quota values set in the Preferences window.

To stop the optimization process

Click image\B_AbortOptimize.gif.

It may take a few seconds to terminate all processes.

Note: After the optimization, the Abstract Plan page remains blank since the abstract plan displays with the original SQL statement.

 

Insert User-Defined SQL

SQL Optimizer > Optimized SQL > Insert User-Defined SQL

Once you have entered the original SQL statement in the SQL Optimizer module, you can add your own alternative SQL statement. You can do this either before or after you have optimized the original SQL statement. With this feature, you can benchmark test your own SQL alternatives with the alternatives created by the SQL Optimizer. Or, you can simply test your own alternatives against the original SQL statement.

To insert your own SQL alternative

  1. Select the original SQL statement or the alternative SQL statement most like the one you want to insert.

  2. Click image\B_InsertUser-DefinedSQL.gif.

  3. Create your SQL statement.

The query plan for your SQL statement is checked to see if it matches any of the query plans for the SQL alternatives or the original SQL. If it does, you will be prompted to decide whether to insert your alternative.

Note: The User-Defined SQL statements are not checked to see if they are semantically equivalent to the original SQL. When you include a User-Defined SQL in a Batch Run, be sure to check the Remarks column of Run Time pane to see if the record count for the User-Defined SQL matches the record count for the original SQL.

 

Related Topic

Optimized SQL

Open Optimized SQL

SQL Optimizer > Optimized SQL > Save Optimized SQL > Open Optimized SQL

After you have saved the optimized SQL statements to a file, you can load them back through the SQL Optimizer window:

To load a saved optimized SQL statements and alternatives

  1. Select SQL | Open Optimized SQL.

  2. Select the file you want to load and click Open. This loads the saved SQL statements to the SQL Optimizer window. The Open Optimized SQL Details window displays the following:

Summary tab

Optimization Information

Displays the original connection and optimization settings information.

Last Saved Query plan Information

Displays the saved and current connection information and whether there are any changes in SQL structure and query plans.

User-Defined Temp Table Tab

If the SQL statement uses a temporary table, the User-Defined Temp Table tab displays in this window. It displays the DDL used to create the temporary table.

Changes Tab

If there are any changes to the SQL structure or the access plans, the Changes tab displays inI this window. It displays the SQL text along with the saved and current query plan.

If there are any changes in either SQL structure or query plans it is advisable that you refresh the query plans so that the reloaded image is a truth reflection of the current environment before any further testing is done. Click Refresh Plan from the Open Optimized SQL Details window. If there are changes in the query plan the corresponding SQL statements run time and statistics information are deleted. All invalid SQL statements are removed, except for the original SQL statement. You have an option to eliminate SQL statements with duplicate query plans.

After refresh, the Refresh Plan Details window can be displayed. This window displays the number of query plans refreshed, the total eliminated, and invalid plans.

The Open Optimized SQL Details and Refresh Plan Details windows can be reviewed at a later stage.

To view the details

Select View | Show Open Optimized SQL Details and View | Refresh Plan Details.

 

Related Topics

View Open Optimized SQL Details

Refresh Plan Details Window

Save Optimized SQL

Optimized SQL

Create Benchmark Factory Import File

SQL Repository > Create Benchmark Factory Import File

All the SQL statements can be saved in a text file from the SQL Scanner, the SQL Repository, the SQL Inspector, and the SQL Optimizer windows. These SQL statements can then be imported into Benchmark Factory 4.6 or later.

To create a file to import into Benchmark Factory

  1. Right-click and select Create Benchmark Factory Import File.

  2. Select the specific SQL statements that you want to save.

  3. Enter the filename and select the file location.

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating