Chat now with support
Chat with Support

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

Why Save the Abstract Plan?

Saving the abstract plan for a SQL statement preserves how the SQL statement is executed. Therefore, when changes occur in the database environment, the query plan used to execute the SQL statement remains constant.

One major advantage of saving the abstract plan is that you can optimize the SQL statement without altering the SQL text. This is an ideal solution for when you do not have the source code from a vendor but want to improve the performance of the database application. The saving of the abstract plan can be very useful when you are considering making changes to the configuration parameters or you are migrating to another version of Adaptive Server and you want to preserve the current performance of SQL statements that are critical to the performance of your applications.

These database environment changes may include:

  • Parallel degree

  • Table partitioning

  • Indexing

  • Database software upgrades

  • Changes in data columns

You can save an abstract plan from the following modules:

To save in... See:

SQL Optimizer

Save Abstract Plan

SQL Scanner

Save Abstract Plan.

Index Impact Analyzer

Right Pane for SQL Statements

Configuration Analyzer

Right Pane for SQL Statement

Migration Analyzer

Right Pane for SQL Statement

SQL Repository

SQL Repository Window

 

Related Topics

Abstract Plan Compatibility with Original SQL

Abstract Plan Manager Window

Open the Abstract Plan Manager

Abstract Plan Group Functions

Export a Group to a Table

Import an Abstract Plan for each User

Abstract Plan ID Functions

Use Saved Abstract Plans

Abstract Plan Compatibility with Original SQL

Adaptive Server generates an abstract plan based on the SQL syntax itself. If a SQL statement is transformed to another syntax that the Adaptive Server optimizer cannot transform internally, then the abstract plans are not compatible. Therefore, even though two SQL statements may be semantically equivalent, their abstract plans are not compatible.

For example, take this SQL statement as your original SQL statement:

SELECT A CITY FROM A, B, C

WHERE A.CITY =B.CITY

AND B.CITY=C.CITY

And take this semantically equivalent alternative SQL:

SELECT A.CITY FROM A, B, C

WHERE A.CITY =B.CITY

AND B.CITY=C.CITY

AND A.CITY=C.CITY

The transforms adds the new condition, A.CITY=C.CITY, and if the Adaptive Server optimizer cannot generate A.CITY=C.CITY internally for your original SQL, then an index scan from A.CITY to C.CITY or from C.CITY to A.CITY is not possible for your original SQL. So, the new abstract plan is not compatible for the original SQL.

Also, if an OR condition is transformed to a UNION, the new abstract plan is so different from the original SQL that there is no way for the original SQL to be compatible with the new abstract plan.

 

Related Topics

Abstract Plan Group

Why Save the Abstract Plan?

Open the Abstract Plan Manager

Abstract Plan Group Functions

Export a Group to a Table

Import an Abstract Plan for each User

Abstract Plan ID Functions

Abstract Plan Manager Window

Left Pane

The left pane is a tree structure for the managing of the elements of the abstract plan: Groups, Users, and Plan IDs.

Right-click any element to bring up the right-click menu with the functionality for that element.

Right Pane

The right pane displays the information that corresponds to the group, users, or plan ID that is selected in the left pane.

 

Related Topics

Abstract Plan Compatibility with Original SQL

Open the Abstract Plan Manager

Abstract Plan Group Functions

Export a Group to a Table

Import an Abstract Plan for each User

Abstract Plan ID Functions

Use Saved Abstract Plans

Open the Abstract Plan Manager

To open the Abstract Plan Manager

Click ..

 

Related Topics

Abstract Plan Compatibility with Original SQL

Abstract Plan Manager Window

Abstract Plan Group Functions

Export a Group to a Table

Import an Abstract Plan for each User

Abstract Plan ID Functions

Use Saved Abstract Plans

Related Documents