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

Retrieve Query plan

SQL Optimizer > SQL Editor > Retrieve Query plan

By examining the query plan, you can see exactly how the database executes your SQL statement, helping you judge whether the SQL statement is the most efficient or any changes to the table structure are needed such as adding a new index.

To view the query plan of the original SQL statement in the SQL Optimizer window

Click image\B_ShowPlan.gif.

If parameters exist in the original SQL statement, you are required to define the parameters and data types of the parameters before the query plan is retrieved. Or, alternatively use the SQL | Show Default Plan to retrieve the query plan without the need to enter parameter details. Show Default Plan allows you to quickly view the query plan without the need to enter the data type variables.

For more specific information about each step of the query plan, click any text in the step and the Plan Detail window displays.


Related Topics

Show Default Plan

SQL Optimizer > SQL Editor > Show Default Plan

When the original SQL statement has parameters, you need to determine the data type of the parameters when executing the Show Plan, Optimize, Run Result, Run for all Records, or Run for First Record functions.

If you would like to view the query plan without needing to enter all the data types and values for the parameters, use the SQL | Show Default Plan to retrieve the query plan. In this case, all the parameters are assumed to have a BINARY data type when executing the Show Plan function. This is useful when you want to quickly investigate the query plan of the original SQL statement.


Related Topic

Optimize Original SQL Statement

SQL Optimizer > SQL Editor > Optimize Original SQL Statement

If sending a SQL statement from the SQL Scanner, SQL Worksheet, Database Explorer, or SQL Formatter to the SQL Optimizer (Edit | Send to SQL Optimizer), these statements are automatically optimized.

To optimize a SQL statement

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

  2. Click image\B_Optimize.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.

During optimization, the unique optimization engine uses multiple SQL syntax transformation rules to produce a list of semantically equivalent SQL statements. Selected optimization forces from the Preferences window are also applied to produce the list of optimized SQL statements.

To stop the optimization process

Click image\B_AbortOptimize.gif.

It may take a few seconds to terminate all processes.

Note:When setting the searching quota values that, the higher the quota, the longer it may take to optimize a complicated SQL statement.


Related Topic

SQL for Cursor Checkbox

SQL Optimizer > SQL Editor > SQL for Cursor Checkbox

Adaptive Server uses a different query plan for a SQL statement that is embedded in a cursor declaration from the query plan when the SQL statement is not embedded in a cursor. This needs to be taken into account when retrieving the query plan or run time and also when generating SQL alternatives.

Therefore, if the original SQL statement comes from or will be embedded in a cursor declaration then you need to select SQL for Cursor in the SQL Editor pane of SQL Optimizer window. This enables cursor simulation when retrieving the query plan and run time information.

This checkbox is automatically selected in the SQL Editor pane when you use the Send to SQL Optimizer function from the SQL Scanner if the SQL was extracted from within a cursor declaration.

In Adaptive Server 15 or later, the Declare Cursor Setting window is also available to select specific cursor settings.

Cursor Arguments

The cursor arguments should match the settings used for the SQL statement in your application code.

Cursor Type Description


Specify that the worktable which holds the result set is populated only as the rows are fetched. Therefore changes to the data that occur while the cursor is opened may be visible in the result set.


Specify that the data is copied to a worktable when the cursor is open which makes the data insensitive to changes in the data that may occur while the cursor is opened.


Specify that the cursor is scrollable meaning that you can position the cursor anywhere in the cursor result set for as long as the cursor is open. All scrollable cursors are read only.


Specify that the rows are retrieved one row at a time. All update cursors are non-scrollable.


Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating