Optimize SQL Statements
SQL Optimizer generates semantically equivalent alternatives with unique execution plans for your original SQL statement. You can then test run the alternatives to benchmark their performance. SQL Optimizer provides execution times and run-time statistics for each alternative to help you identify the best SQL statement for your database environment.
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To optimize a SQL statement
- Select the SQL Details tab in the main window.
- Enter a SQL statement in the Alternative Details pane of the SQL Details tab.
- Click
to optimizer the SQL statement.
-
Select a connection to use. Review the following for additional information:
Select Connection Description Connection
Click
to select a previously created connection.
Tip: Click
to open the Connection Manager to create a new connection. Connect to the Database
Select Schema and SQLID Description Schema
Click SQLID
Click -
When SQL Optimizer is finished generating alternatives, use one of the following methods to test run the SQL alternatives:
To test run all SQL alternatives, click
.
- To test run a single SQL alternative, select the alternative and click
.
To test run two or more alternatives simultaneously, select the alternatives, click the arrow beside
The execution statistics display in the Alternatives pane once the test run completes. Select an alternative to see more information in the Alternative Details and Execution Plan panes.
Tips:
- Click
to stop the test run process.
- You can select an executing SQL statement and click
to abort only the selected statement.
To clear the SQL Rewrite window
To clear only the results and retain the original SQL statement, right-click within the Alternative Details pane and select Clear Optimization Results | Keep Original Scenario.
To clear the SQL Rewrite window, right-click within the Alternative Details pane and select Clear Optimization Results | Clear Original Scenario.