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

Create Benchmark Factory Import File

All the SQL statements can be saved in a text file from the SQL Scanner, the SQL Repository, the SQL Inspector, and the SQL Optimizer windows. These SQL statements can then be imported into Benchmark Factory 4.6 or later.

To create a file to import into Benchmark Factory

  1. Right-click and select Create Benchmark Factory Import File.

  2. Select the specific SQL statements that you want to save.

  3. Enter the filename and select the file location.

 

Optimized SQL

Optimized SQL

The Optimized SQL information displays in the SQL Optimizer window after performing the Optimize function:

SQL Text

The top left pane allows you to select the of semantically equivalent SQL statements produced after optimization from the tabs at the bottom of the pane. The arrangement of the SQL statements on these tabs from left to right is the SQL Editor, then the Original, followed by optimized SQL statements starting with Alt1, Alt2, etc. The optimized SQL statements are ranked according to increasing Estimated I/O cost.

Note: The lower the Estimated I/O cost the better the estimated performance of the SQL statement. However, the cost value should not be used as the actual indication of performance.

Auto indentation format

All alternative SQL statements are transformed into a more readable format by automatically indenting and aligning.

Text color

All parameters are in red (default color), indicating that a value and data type needs to be defined upon execution. Other highlights are according to parameters set for syntax highlights under the Preferences window.

Comments

Comments can be entered in the original SQL statement but are not included in the SQL alternatives.

SQL Information

The top right pane, the SQL Information pane, displays detailed information about the SQL statement.

Run Time Information

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

Time

Statistics

Charts

 

Related Topic

Optimization Details Window

The Optimization Details window is optional and can be displayed after optimization. This window displays the following:

  • Time calculations for the optimization process.

  • Number of semantically equivalent SQL statements investigated

  • Number of alternative query plans produced.

  • How many SQL statements were eliminated because they had identical query plans.

  • A warning message if the Syntax Transformation Quota, Total Forces Quota, Parallel Quota, Total Quota, or Join Path Permutation Quota is reached.

Note: Generally speaking, SQL statements with the same query plan will produce the same run time because the database executes the SQL statement in the same way. Therefore, newly generated SQL statements with equivalent query plans are eliminated.

The Optimization Details window is shown every time optimization is finished unless it is disabled. This is done by unselecting the Show details on next optimization option in the Optimization Details window.

The Optimization Details window can be shown anytime after the optimization process by selecting View | Show Optimization Details when the SQL Optimizer window is the active window.

If no alternatives are available, the Advise Indexes button is shown for you to directly copy the original SQL statement from the SQL Optimizer to Index Advisor for generation index candidates. The generate process will start automatically.

 

Related Topic

Related Documents