SQL Optimizer for SAP ASE 3.8 - Release Notes

SQL Optimizer Tutorial

Due to complex nature SQL, there may be many SQL statements that return the same result set, but only a few that may be efficient. The SQL Optimizer applies advanced SQL transformation technology to generate a list of semantically equivalent SQL statements. To identify the most efficient SQL statement for your database environment you can benchmark test the SQL alternatives in your database.

Optimize a SQL statement

1. Open the SQL Optimizer window by clicking .

2. After entering a SQL statement in the SQL Editor pane of the window, click . This step launches the SQL Optimizer that automatically transforms the SQL statement.

   Notes:

• The use of forces and other optimization options such as temp table generation, ANSI JOIN syntax are optional and configurable in the Preferences.
• The intensity of the SQL transformation process is controlled by the Intelligence Level in the Preferences. The Intelligence Levels control how many forces are applied to transformed SQL and how many SQL alternatives are created.
• If your SQL statement uses a temporary table, see section User-Defined Temp Table for the steps to create a temporary table in the User-Defined Temp Table module.

3. After optimization, the Optimization Details window shows the total number of semantically equivalent SQL statements, the number of alternative statements with query plans different from your original SQL statement, and a warning message if the number of SQL transformations reaches any of the optimization quotas set in the Preferences.

4. Click OK to close the Optimization Details window.

5. In the SQL Optimizer window, look at the tabs which are labeled ALT1, ALT2, ALT3, etc. By clicking the tabs you can see the alternative SQL statements that were created by the SQL optimization process. The query plan for each SQL statement displays beside the SQL text.

6. At the bottom left of the SQL Optimizer window are three tabs, Time, Statistics, and Charts, which display the statistics for each SQL statement after it is executed. At this point since you have not yet run the SQL statements, it displays only the Estimated I/O Cost values. These are only estimations of how each statement will perform. You need to test each statement to obtain its actual run time statistics.

7. In the SQL Optimizer window, look at the right pane to see the query plan for the SQL statement.

Compare SQL alternatives

8. To see how an alternative SQL statement differs from your original SQL, you can compare these statements side-by-side.

• Click . Your original SQL statement displays in one pane of the window and an alternative statement in another pane. Blue highlighted items in one pane show the area where there is a difference from the SQL statement displayed in the other pane.
• At the bottom of the SQL Comparer window, click the Show query plan checkbox to display the query plan for both SQL statements.
• Select File | Close SQL Comparer.

Batch test SQL alternatives

9. To prepare to execute the original and the alternative SQL statements, click .

10. In the Batch Run Criteria window, select the SQL statements that you would like to run in batch. Notice the tabs at the top of the Batch Run Criteria window.

11. Selected SQL Tab

• Select which SQL statements are to be executed. The blue checkmark in the left column indicates that the SQL statement is selected. By default, all statements are executed.
• To deselect a statement, click that SQL statement in the SQL column, for instance click ALT1.
• To deselect all the SQL statements, right-click and select Unselect All.
• To save time when testing run all generated alternatives, a filter function is provided to help you to precisely select the alternatives to test run. Click the Apply SQL Selection filter checkbox. Click to select and apply criteria to narrow down the SQL alternatives to test run.

12. SQL Termination Tab
    Select the option for terminating the execution of your original and the alternative SQL statements. The SQL Optimizer generated all the alternative SQL statements in order to find the optimal SQL. Some of those alternatives may run faster than the original SQL, others may run longer. Therefore, you can set the termination criteria to cancel the longer running SQL statements and save database-processing time for the overall batch test. You have these options for terminating your SQL.

• Original SQL: Terminate the SQL statement when it has run as long as the original SQL.
• Best running time SQL: Run the first SQL statement and use the time from that statement as the termination time. When a SQL statement runs faster than this time, use the faster time as the new termination time, so you are always using the fastest run time as the termination time for the next SQL statement.
• User-defined time: Set your own termination time. If your original SQL statement takes a long time to execute and there are many alternative statements, executing all statements may take considerable time. In that event, consider setting an aggressive user-defined termination time. If the original takes 1 hour, try a 5-minute termination time. If no alternative statements execute in under that period, raise the termination time to 10 minutes, etc.

• You can also combine User-defined time with Original SQL or Best running time SQL by clicking the Or User-defined time checkbox next to each one.

13. Batch Termination Tab
    Select the option for terminating the Batch Run.

• No termination: Specify to run the Batch Run to completion.
• Terminate Batch Run if the specified number of SQL falls in the criteria:

Specify to terminate the Batch Run when a specified number of SQL statements are found that meet the following requirements for terminating the Batch Run.

Number of SQL (excluding Original): Specify how many SQL statements must be found that a show performance improvement over the Original SQL.

Count the SQL if it elapsed time is faster than: Specify one of the following criteria to determine how the performance improvement is determined.

Original SQL: Count all SQL statements that run faster than the run time from the Original SQL.

Original SQL with a percentage of improvement: Count all SQL statements where the run time for the alternative SQL statement is the specified percentage faster than time for the Original SQL statement.

User-defined time (mins/secs): Count all SQL statements that run faster than a specified number of minutes and/or seconds.

14. Run Time Mode Tab

• Run to retrieve: Select First Record to find the time to process the first record. Select All Records to find the time to process all records. You must run the Batch Run twice to get both times.
• Retrieve the run time by executing: Select the number of times to run each SQL alternative. SQL statements can be run more than once to eliminate data caching time and obtain more accurate run time statistics.

Note: The Batch Run function provides an efficient way of benchmarking SQL. It runs the selected SQL statements in the database and the SQL statements that exceed the termination time are cancelled. The Batch Run retrieves the time the SQL statement executes in the database and does not retrieve the result set from the database server to the client; so it does not create additional network traffic. For SQL statements such as SELECT...INTO, INSERT, DELETE and UPDATE, each statement is run in a transaction that is ROLLBACK, therefore maintaining the consistency of your data.

15. Batch Run Schedule Tab

• Start: Select the time you would like the Batch Run to starting executing.
• Until: Select the option for when the Batch Run should finish.

16. To execute the original and the selected alternative SQL statements, click OK. The Batch Run window opens enabling you to view the results as each statement executes.

17. At the completion of the entire job, the Batch Run Details window replaces the Batch Run window. This window provides greater detail about each SQL statement. Click OK.

Review test results

18. Click the Time tab in the SQL Optimizer window to see the columns in the SQL Run Time pane which contain the time to execute the entire statement (all records) and/or the time to retrieve only the first record for each SQL statement from the Batch Run.

19. Once you have identified the most-efficient alternative SQL statement to deploy it:

• You can copy and paste it back in your application.
• If your SQL statement comes from a database objects, you can open the database object source code in the SQL Worksheet anYou can save the alternative SQL statement in a text file either individually or multiple SQL statements in the optimized SQL report. d modify the SQL with the best alternative.
• You can save your SQL optimization results for later review. Select SQL | Saved Optimized SQL.
• If you want to use abstract plans to implement the most-efficient query plan, you should have enabled before optimization dump abstract plan in the Optimization Preferences. If abstract plans are displayed in the SQL Optimizer window, click . Select the abstract plan group and check that the abstract plan is correct. Only save abstract plans that are compatible. Select Save. This process saves the abstract plan to the database in the specified abstract plan group, so the next time you execute the same SQL statement, the saved abstract plan determines the query plan.

Related Topic

SQL Optimizer Overview