Chatee ahora con Soporte
Chat con el soporte

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

Optimization Engine

SQL Optimizer > Optimization Engine

During optimization, the SQL Optimizer employs a unique optimization engine that uses multiple SQL syntax transformation rules to produce a list of semantically equivalent SQL statements. This engine first transforms the original SQL statement, hence producing a group of optimized SQL statements. It then goes on rewriting each optimized SQL statement to produce another group of alternatives. The engine continues rewriting until the SQL statements cannot be rewritten any further or a set of user-defined quotas are reached.

One of the searching rules is illustrated in the following example:

SELECT *

FROM table_a  

WHERE table_a.key in (SELECT table_b.key  

  FROM table_b)                       

If table_b.key is an indexed column, the following transformation is executed:

SELECT *

FROM table_a  

WHERE EXISTS (SELECT 'x'  

FROM table_b                 

WHERE table_b.key = table_a.key)                

 

Although the above two SQL statements produce the same result, the database may evolve two different query plans. It is difficult to decide which SQL statement will run faster without taking into account the database structure, indexes, and data volume, so testing the SQL alternatives in your database environment in important to the process of selecting the best SQL alternative.

 

Related Topic

Common Coding Errors in SQL Statements

SQL Optimizer > Common Coding Errors in SQL Statements

The performance of database applications can be downgraded simply because common coding errors existing in SQL statements introduced by database changes such as adding a new index. Therefore it is important to review every SQL statement existing on the database server and source code using the SQL Scanner module. Performance of SQL statements can normally be dramatically improved by correcting common coding errors based on database information obtained during login.

The SQL Optimizer not only optimizes the input SQL statements but also corrects common coding errors based on database information obtained during login.

The followings are some examples of common mistakes found in SQL statements:

Enable index search (Built-in De'Morgan Law Engine is used to solve this problem)

SELECT *

FROM table_a  

WHERE NOT(table_a.key + 5) > 15 

 

SELECT *

FROM table_a  

WHERE table_a.key <= 15 - 5  

 

Remove unnecessary function calls (If A.key is a not null column detected from database)

SELECT *

FROM table_a  

WHERE ISNULL(table_a.key,0) = 10 

 

SELECT *

FROM table_a  

WHERE table_a.key = 10 

 

Related Topic

What Function Should l Use to Retrieve the Run Time?

SQL Optimizer > What Function Should l Use to Retrieve the Run Time?

The SQL Optimizer provides two different measurements of performance; All Records and First Record. Both measurements give you an indication on the fastest running SQL statement but with two different aims. The All Records is the time it takes to retrieve all the records from the query. The First Record is the time it takes to retrieve the first record from the query. You must understand the intention of the SQL statement and what type of source code it is be embedded in. Generally, if the SQL statement is used for reports, then you should use the Run for All Records or Batch Run with Run Time Mode set to All Records. If the SQL statement is used for on-line query, then use the Run for First Record or Batch Run with Run Time Mode set to First n Record(s).

Note: If the aim of the SQL statement is unknown, then use All Records as a performance indication.

 

Related Topics

Unsatisfactory Performance Results

SQL Optimizer > 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.

 

Documentos relacionados

The document was helpful.

Seleccionar calificación

I easily found the information I needed.

Seleccionar calificación