Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To set order and termination options
Select Optimize SQL | Test Run | Order and Termination
Review the following for additional information:
Execution Order | Description |
Execution order for SQL |
Select one of the following options:
|
SQL Termination Criteria | Description |
Terminate execution of SQL alternative if it runs longer than |
Select one of the following options:
|
Cancellation delay (seconds) |
Adds a specified time to the termination time. It is important to factor a delay into the overall termination time to account for the time needed to send the SQL statement to the database server. |
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
To set the best alternative options
Select Optimize SQL | Test Run | Best Alternative Criteria
Review the following for additional information:
Best SQL Alternative Selection Criteria | Description |
Best alternative selected based on lowest |
Select one of the following :
Default: Elapsed Time |
Using Optimize SQL consists of two steps. In the first step, SQL Optimizer generates semantically equivalent alternatives with unique execution plans for your original SQL statement. In the second step, SQL Optimizer executes the alternatives to test runs each statement's performance. This provides execution times and run-time statistics that allow you to find the best SQL statement for your database environment.
Select the SQL Details tab in the main window.
Click to optimize the SQL statement
Select a connection. Review the following for additional information:
Select Connection | Description |
Connection |
Click Tip: Click |
Select Schema and SQLID | Description |
Schema |
Click |
SQLID |
Click |
Click after SQL Optimizer completes the SQL rewrite process to compare your original SQL statement with the alternatives generated.
The Test Run function provides an efficient way to benchmark alternatives generated by SQL Optimizer. You can test run selected alternatives to obtain actual execution statistics. This function does not affect network traffic since SQL Optimizer can provide these statistics without having to retrieve result sets from the database server. Additionally, data consistency is maintained when using SELECT, SELECT INTO, INSERT, DELETE, and UPDATE statements because these statements are run in a transaction that is rolled back after execution.
To test run a SQL statement alternative
Click .
Select Optimize SQL | Test Run | Test Run Settings.
Select Custom Settings or Predefined Settings.
Select Optimize SQL | Test Run | Execution Method.
Review the following for additional information:
Execution Method | Description |
Maximum rows to be retrieved (records) |
Select this checkbox and then enter the maximum number of rows to retrieve for a test run of all records. |
Run Time Retrieval Method | Description |
Run SQL options |
Select one of the following options:
|
Select Optimize SQL | Test Run | Order and Termination.
Select the This percentage of the original SQL run time checkbox.
Click .
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.
Using Optimize SQL consists of two steps. In the first step, SQL Optimizer generates semantically equivalent alternatives with unique execution plans for your original SQL statement. In the second step, SQL Optimizer executes the alternatives to test runs each statement's performance. This provides execution times and run-time statistics that allow you to find the best SQL statement for your database environment.
Select the SQL Details tab in the main window.
Click to optimize the SQL statement
Select a connection. Review the following for additional information:
Select Connection | Description |
Connection |
Click Tip: Click |
Select Schema and SQLID | Description |
Schema |
Click |
SQLID |
Click |
Click after SQL Optimizer completes the SQL rewrite process to compare your original SQL statement with the alternatives generated.
The Test Run function provides an efficient way to benchmark alternatives generated by SQL Optimizer. You can test run selected alternatives to obtain actual execution statistics. This function does not affect network traffic since SQL Optimizer can provide these statistics without having to retrieve result sets from the database server. Additionally, data consistency is maintained when using SELECT, SELECT INTO, INSERT, DELETE, and UPDATE statements because these statements are run in a transaction that is rolled back after execution.
To test run a SQL statement alternative
Click .
Select Optimize SQL | Test Run | Test Run Settings.
Select Custom Settings or Predefined Settings.
Select Optimize SQL | Test Run | Execution Method.
Review the following for additional information:
Execution Method | Description |
Maximum rows to be retrieved (records) |
Select this checkbox and then enter the maximum number of rows to retrieve for a test run of all records. |
Run Time Retrieval Method | Description |
Run SQL options |
Select one of the following options:
|
Select Optimize SQL | Test Run | Order and Termination.
Select the This percentage of the original SQL run time checkbox.
Click .
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.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Termini di utilizzo Privacy Cookie Preference Center