The SQL statement that you want to optimize is referred to as the original SQL statement.
To optimize a SQL statement
Click .
Enter the SQL statement by typing it in, opening an existing file, or pasting it. Parameters within the SQL statement can be prefixed with a "@" sign or without.
To help construct a SQL statement use the member lookup, argument lookup, auto correction, indent, outdent , comment, and uncomment functions.
For SQL statements that involve temporary tables, you need to create the temporary table first before optimizing the SQL statement using the User-Defined Temp Table window.
Only one SQL Optimizer window can be opened at any time. The system will prompt you to save the original SQL statement if you attempt to open another file.
You can perform the following functions from the SQL Optimizer window:
Note: The SQL Optimizer supports only a single SELECT, SELECT INTO, DELETE, UPDATE and INSERT SQL statement. To optimize SQL within Transact SQL, use the SQL Scanner module to identify potential problematic SQL statements (e.g. database objects such as Procedures.)
By examining the query plan, you can see exactly how the database executes your SQL statement, helping you judge whether the SQL statement is the most efficient or any changes to the table structure are needed such as adding a new index.
To view the query plan of the original SQL statement in the SQL Optimizer window
Click .
If parameters exist in the original SQL statement, you are required to define the parameters and data types of the parameters before the query plan is retrieved. Or, alternatively use the SQL | Show Default Plan to retrieve the query plan without the need to enter parameter details. Show Default Plan allows you to quickly view the query plan without the need to enter the data type variables.
For more specific information about each step of the query plan, click any text in the step and the Plan Detail window displays.
When the original SQL statement has parameters, you need to determine the data type of the parameters when executing the Show Plan, Optimize, Run Result, Run for all Records, or Run for First Record functions.
If you would like to view the query plan without needing to enter all the data types and values for the parameters, use the SQL | Show Default Plan to retrieve the query plan. In this case, all the parameters are assumed to have a BINARY data type when executing the Show Plan function. This is useful when you want to quickly investigate the query plan of the original SQL statement.
If sending a SQL statement from the SQL Scanner, SQL Worksheet, Database Explorer, or SQL Formatter to the SQL Optimizer (Edit | Send to SQL Optimizer), these statements are automatically optimized.
To optimize a SQL statement
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.
During optimization, the unique optimization engine uses multiple SQL syntax transformation rules to produce a list of semantically equivalent SQL statements. Selected optimization forces from the Preferences window are also applied to produce the list of optimized SQL statements.
To stop the optimization process
Click .
It may take a few seconds to terminate all processes.
Note:When setting the searching quota values that, the higher the quota, the longer it may take to optimize a complicated SQL statement.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center