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.
Check Abstract Plan Compatibility
View Abstract Plan Matrix Window
To check the compatibility between the original SQL statement and the abstract plan from the optimized SQL statements
Click 
The Abstract Plan Matrix window is mainly divided into a top and bottom section. The top section shows a matrix of the SQL (source, Alt1, Alt2 …) and the abstract plan (AP0, AP1, AP2 …). The bottom section has 3 panes which show the SQL text, abstract plan, XML plan, query plan, and trace on details. To display the third pane with the XML plan query plan, and trace on information (the right-most pane), select the Show SQL Information based on abstract plan checkbox.
Note: The trace on information is only displayed if the dbcc traceon option is selected on the Database Setting tab in the Preferences window. The XML Plan is only displayed if you are connected to Adaptive Server 15.0 or later.
The Abstract Plan Matrix provides the following functions:
|
Function |
Description |
| Check Selected | Check the compatibility of the selected abstract plan and SQL. |
|
Check All |
Check the compatibility of all the abstract plans and SQL statements. |
|
Check Column |
Check the compatibility of the selected SQL statement with all the abstract plans. |
|
Check Row |
Check the compatibility of the selected abstract plan with all the SQL statements. |
Save Abstract Plan
To save the abstract plan from the SQL Optimizer window to the database
-
Open the Save Abstract Plan window by clicking
.
-
In the Save to group drop-down field, select the group where you want to store your abstract plan.
-
In the SQL drop-down field, select the SQL statement you want to associate with an abstract plan. This is usually your original SQL statement.
-
In the Abstract plan from SQL drop-down field, select the SQL statement that you determined is the best abstract plan for your application.
-
Click Save.
Note: Only save an abstract plan that is compatible with the original SQL text. Otherwise, the abstract plan will not be used the next time you execute the original SQL.
When the Abstract Plan is saved, it is only saved for the user that you are logged on as. In order for another user to use this abstract plan, you must export/import the plan to another user.
At the prompt "Plan has been created successfully. The id is nnnnnnnnnn". Click OK. The abstract plan is saved in Adaptive Server.
Note: Adaptive Server saves the abstract plan in the sysqueryplans system table. When a query is executed, Adaptive Server looks in the sysqueryplans table for a stored SQL text that matches the query. If a match is found, the saved abstract plan is used to execute the query.
Saving the abstract plan onto the database means that when the same SQL statement is executed, the query plan is based on the abstract plan.
Warning: When saving the abstract plan, Adaptive Server automatically trims the white-spaces from the SQL text replacing it with one space. You need to make sure the SQL statement you execute in your application is the same as the original SQL text that you saved with the abstract plan. If the SQL text does not match, then the abstract plan will not be used.
Here are some examples you need to be aware of:
Spaces in between functions
where substring ( EMP_NAME, 1, 5 ) = 'SMITH'
This is not the same as
where substring(EMP_NAME,1,5) = 'SMITH'
Spaces in between database, scheme and object name
from sqlexp . sqlexp . EMPLOYEE
This is not the same as
from sqlexp.sqlexp.EMPLOYEE
Parameter replacement
where EMP_ID = @var_a
This is not the same as
if @var_a = 56
where EMP_ID = 56
Comments
where EMP_ID =123 /* comment */
This is not the same as
where EMP_ID = 123
Test for Scalability
You can test a single SQL statement to find out how it will perform under different amounts of data in your database in Quest's Benchmark Factory program (version 4.6 or later).
To send SQL statement to Benchmark Factory:
-
Optimize the original SQL statement.
-
Select the original or one of the SQL alternatives
-
Click
.