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

Unsatisfactory Performance Results

After optimization, you may discover that the performance of the optimized SQL statements is still not satisfactory. To remedy this, first check that the searching quota has not been reached in the Optimization Details window. If it has, then you should increase the intelligence level or optimization options in the Preferences window and optimize again to ensure all transformed SQL statements are given. Review the query plan of the optimized SQL statement to check if there should be any alterations to the database structure, for example adding a new index.

Rerun the SQL statement optimization after the review.

 

SQL Optimizer Functions

Below is a list of available functions within the SQL Optimizer window.

SQL Editor Functions

These functions are available only in the SQL Editor.

Button or Menu Function

SQL Menu

image\B_Optimize.gif image\B_AbortOptimize.gif

Optimize/Abort Optimize

SQL Menu

image\B_OptimizeUsingAP.gif image\B_AbortOptimizeUsingAbstractPlan.gif

Optimize Using Abstract Plan/Abort Optimize Using Abstract Plan

SQL Menu

image\B_ShowPlan.gif

Show Plan

SQL Menu

Show Default Plan

File Menu

Open SQL from SQL Repository

SQL Editor and Optimized SQL Functions

These functions are available for both the SQL Editor and Optimized SQL.

Button or Menu Function

SQL Menu

image\B_RunResult.gif image\B_AbortRunResut.gif

Run Result/Abort Run Result

SQL Menu

image\B_RunForFirstRecord.gif image\B_AbortRunForFirstRecord.gif

Run for First Record/Abort Run for First Record

SQL Menu

image\B_RunForAllRecords.gif image\B_AbortRunForAllRecords.gif

Run for All Records/Abort Run for All Records

SQL & Right-click Menu

image\B_TestForScalability.gif

Testing for Scalability

SQL Menu

Open Optimized SQL

Edit Menu

image\B_CopytoIndexAdvisor.gif

Send to Index Advisor

Edit Menu

image\B_CopyToSQLWorksheet.gif

Copy to SQL Workshop

File Menu

Save to SQL Repository

Right-click Menu

Create Benchmark Factory Import File

Optimized SQL Functions

These functions are available only for the Optimized SQL.

Button or Menu Function

SQL Menu

image\B_BatchRun.gif image\B_AbortBatchRun.gif

Batch Run/Abort Batch Run

SQL Menu

image\B_AbstractPlanMatrix.gif

Abstract Plan Matrix

SQL Menu

image\B_SaveAbstractPlan.gif

Save Abstract Plan

SQL Menu

image\B_SQLComparer.gif

SQL Comparer

SQL Menu

Save Optimized SQL

SQL Menu

Find SQL. . . /Find Next SQL

Navigate Menu

image\B_FirstSQL.gif image\B_PreviousSQL.gif image\B_NextSQL.gif image\B_LastSQL.gif

First SQL/ Previous SQL/ Next SQL/ Last SQL

Navigate Menu

Go to SQL

Report Menu

Optimized SQL

View Menu

Show Optimization Details

View Menu

Show Batch Run Details

 

Related Topics

SQL Editor

SQL Optimizer Window

The SQL Optimizer window allows you to enter a SQL statement for optimization. The optimization process analyzes the original SQL statement entered in the SQL Editor pane and uses the built-in Feedback-Searching engine to reproduce a list of new, semantically equivalent SQL statements.

The SQL Optimizer supports only a single SELECT, SELECT INTO, DELETE, UPDATE, or INSERT SQL statement. To optimize SQL within Transact SQL, use the SQL Scanner module to identify potential problematic SQL statements within the Transact SQL code. Then optimize each SQL statement in the SQL Optimizer module.

The SQL Editor pane of the SQL Optimizer window is where you input your original SQL statement so that you can optimize it.

SQL Text

The top left pane is used to enter and display SQL statements. The SQL Editor tab allows you to enter the SQL statement you want to optimize. When a SQL statement is copied to this pane from other modules, the optimization process automatically begins.

After optimization, multiply tabs are added to display the optimized SQL statements.

SQL Information

The top right pane [SQL Information] displays SQL information according to the button you select from the button bar at the top of this pane. These buttons include: query plan, abstract plan, and Information (SQL classification and warning messages), All Records, and First Record. The SQL Information Pane provides detailed information about the SQL statement.

Run Time Information

The Run Time information displays in three tabs in the bottom pane.

 

Related Topics

Related Documents