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

Verify Correctness of Optimized SQL Statements

You can verify the correctness of optimized SQL statements by comparing it with the original SQL statement using the Run Result function and by looking at the No. of Records column in the Time tab in the SQL Optimizer window after retrieving the run time. This information enables you to see whether the optimized SQL statement provides the same results as the original SQL statement.

Run Result

The Run Result function retrieves the queried records from the connected database.

Number of Records

In the Run Time pane of the SQL Optimizer window, the No. of Records displays the total number of records influenced by the SQL statement. This figure should remain constant for both the original and optimized SQL statements.

First Record

In the Run Time pane of the SQL Optimizer window, the First Record indicates the first record retrieved either 0 or the number of records that was selected for First n Records in the Run Time Mode page in the Batch Run Criteria window . This figure should remain constant for both the original and optimized SQL statements.

 

Related Topic

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

Check Abstract Plan Compatibility

To check the compatibility between the original SQL statement and the abstract plan from the optimized SQL statements

Click .

The Abstract Plan Matrix window is mainly divided into a top and bottom section. The top section shows a matrix of the SQL (source, Alt1, Alt2 …) and the abstract plan (AP0, AP1, AP2 …). The bottom section has 3 panes which show the SQL text, abstract plan, XML plan, query plan, and trace on details. To display the third pane with the XML plan query plan, and trace on information (the right-most pane), select the Show SQL Information based on abstract plan checkbox.

Note: The trace on information is only displayed if the dbcc traceon option is selected on the Database Setting tab in the Preferences window. The XML Plan is only displayed if you are connected to Adaptive Server 15.0 or later.

The Abstract Plan Matrix provides the following functions:

Function

Description

Check Selected Check the compatibility of the selected abstract plan and SQL.

Check All

Check the compatibility of all the abstract plans and SQL statements.

Check Column

Check the compatibility of the selected SQL statement with all the abstract plans.

Check Row

Check the compatibility of the selected abstract plan with all the SQL statements.

 

Related Topics

Save Abstract Plan

To save the abstract plan from the SQL Optimizer window to the database

  1. Open the Save Abstract Plan window by clicking image\B_SaveAbstractPlan.gif.

  2. In the Save to group drop-down field, select the group where you want to store your abstract plan.

  3. In the SQL drop-down field, select the SQL statement you want to associate with an abstract plan. This is usually your original SQL statement.

  4. In the Abstract plan from SQL drop-down field, select the SQL statement that you determined is the best abstract plan for your application.

  5. Click Save.

Note: Only save an abstract plan that is compatible with the original SQL text. Otherwise, the abstract plan will not be used the next time you execute the original SQL.

When the Abstract Plan is saved, it is only saved for the user that you are logged on as. In order for another user to use this abstract plan, you must export/import the plan to another user.

At the prompt "Plan has been created successfully. The id is nnnnnnnnnn". Click OK. The abstract plan is saved in Adaptive Server.

Note: Adaptive Server saves the abstract plan in the sysqueryplans system table. When a query is executed, Adaptive Server looks in the sysqueryplans table for a stored SQL text that matches the query. If a match is found, the saved abstract plan is used to execute the query.

Saving the abstract plan onto the database means that when the same SQL statement is executed, the query plan is based on the abstract plan.

Warning: When saving the abstract plan, Adaptive Server automatically trims the white-spaces from the SQL text replacing it with one space. You need to make sure the SQL statement you execute in your application is the same as the original SQL text that you saved with the abstract plan. If the SQL text does not match, then the abstract plan will not be used.

Here are some examples you need to be aware of:

Spaces in between functions

where substring ( EMP_NAME, 1, 5 ) = 'SMITH'

This is not the same as

where substring(EMP_NAME,1,5) = 'SMITH'

Spaces in between database, scheme and object name

from sqlexp . sqlexp . EMPLOYEE

This is not the same as

from sqlexp.sqlexp.EMPLOYEE

Parameter replacement

where EMP_ID = @var_a

This is not the same as

if @var_a = 56

where EMP_ID = 56

Comments

where EMP_ID =123 /* comment */

This is not the same as

where EMP_ID = 123

 

Related Topics

Related Documents