The SQL Optimizer module automates the optimization of SQL statements. It employs a unique engine that uses Artificial Intelligence to generate all the possible SQL alternatives that can be mathematically proven to be "semantically equivalent" to the original SQL statement which guarantees that the SQL alternatives will produce the exact same results as the original SQL statement. After the alternatives are generated, you can compare each SQL statement to any other SQL statements to see the different SQL coding techniques for achieving the same results. You can then test these alternative SQL statements in your environment to find the best one for your database environment.
The first step of this engine transforms the original SQL statement and produces a group of alternative SQL statements where the syntax was rewritten. Then, the SQL Optimizer rewrites each newly created SQL statement to produce another group of alternatives. The engine continues rewriting each alternative until all the SQL statements cannot be rewritten any further or until the user-defined quota for the number of SQL statements generated by syntax transformation is reached.
In the Optimization settings, you can also control whether the optimization process will generate SQL statements make use of temporary tables. You can also select to use the JOIN clause (INNER JOIN, CROSS JOIN) from the Ansi-92 SQL standard or to use the original SQL syntax for joining tables.
After the SQL Optimizer has exhausted rewriting the syntax of the SQL statement, the various techniques available in Adaptive Server for optimizing a SQL statement are applied to the original SQL statement and each of the SQL alternatives until all selected options have been applied to all the SQL alternatives or until the user-defined quota is reached.
For each rewritten SQL statement, the query plan is compared to all the other query plans. One SQL alternative is selected for each unique query plan. Although the optimization process may generate hundreds of SQL alternatives, you will see only some of the alternatives since the alternatives with a duplicate query plan are eliminated.
Although all the SQL alternative statements produce the same result, Adaptive Server will likely use a different path to retrieve the data for each one. It is difficult to decide which SQL statement will run faster without taking into account the database structure, indexes, and data volume, so it is important to test the SQL alternatives in your database environment using the Batch Run to determine the best SQL alternative from the run time statistics.
The settings in the Optimizer options affect the amount of time it takes and the number of alternatives that are generated by the optimization process. You can quickly select to increase or decrease the intensity of the optimization process using the Intelligence Level settings to automatically select more or less options.
You also have the ability to test and compare your own SQL alternatives using the Insert User-Defined SQL function.
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.
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
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.
© ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center
