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

Test for Scalability

You can test a single SQL statement to find out how it will perform under different amounts of data in your database in Quest's Benchmark Factory program (version 4.6 or later).

To send SQL statement to Benchmark Factory:

  1. Optimize the original SQL statement.

  2. Select the original or one of the SQL alternatives

  3. Click image\B_TestForScalability.gif.

 

Save Optimized SQL

Save Optimized SQL

You can save the Optimized SQL statements. Unlike a report, this file is used to reload the SQL back to the SQL Optimizer window using the Open Optimized SQL function.

To save the original SQL and the SQL alternatives

Select SQL | Save Optimized SQL.

 

Related Topics

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