Optimize Using Abstract Plan Only
This function is only available if you are connected to Adaptive Server 15 or later.
The Optimize using abstract plan function optimizes the original SQL with the objective of producing an optimal abstract plan. Therefore the transformed SQL statements are not shown. After optimization, the alternative abstract plans are shown with the original SQL statement in the left pane of the SQL Optimizer window. All abstract plans are compatible with the original SQL statement.
To optimize using only the abstract plan
-
In the SQL Optimizer window, enter the SQL statement in the SQL Editor pane of the SQL Optimizer.
-
Click
.
The time it takes to optimize is dependent on the complexity of the original SQL statement and the quota values set in the Preferences window.
To stop the optimization process
Click 
It may take a few seconds to terminate all processes.
Note: After the optimization, the Abstract Plan page remains blank since the abstract plan displays with the original SQL statement.
Insert User-Defined SQL
Once you have entered the original SQL statement in the SQL Optimizer module, you can add your own alternative SQL statement. You can do this either before or after you have optimized the original SQL statement. With this feature, you can benchmark test your own SQL alternatives with the alternatives created by the SQL Optimizer. Or, you can simply test your own alternatives against the original SQL statement.
To insert your own SQL alternative
-
Select the original SQL statement or the alternative SQL statement most like the one you want to insert.
-
Click
.
-
Create your SQL statement.
The query plan for your SQL statement is checked to see if it matches any of the query plans for the SQL alternatives or the original SQL. If it does, you will be prompted to decide whether to insert your alternative.
Note: The User-Defined SQL statements are not checked to see if they are semantically equivalent to the original SQL. When you include a User-Defined SQL in a Batch Run, be sure to check the Remarks column of Run Time pane to see if the record count for the User-Defined SQL matches the record count for the original SQL.
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
-
Select SQL | Open Optimized SQL.
-
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.
Create Benchmark Factory Import File
All the SQL statements can be saved in a text file from the SQL Scanner, the SQL Repository, the SQL Inspector, and the SQL Optimizer windows. These SQL statements can then be imported into Benchmark Factory 4.6 or later.
To create a file to import into Benchmark Factory
-
Right-click and select Create Benchmark Factory Import File.
-
Select the specific SQL statements that you want to save.
-
Enter the filename and select the file location.