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

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

SEMI-SENSITIVE

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.

INSENSITIVE

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.

SCROLL

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.

NO SCROLL

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

 

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

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

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

Related Documents