SQL Optimizer for SAP ASE 3.9

Parameters, also called variables, can be embedded in a SQL statement without pre-defining the data type and value. All variable names are highlighted in red (by default) after the formatting. Variables with or without a "@" sign are recognized. When you optimize the SQL statement or retrieve the query plan, run time, the run result for a SQL statement that uses a parameter, you are prompted to enter the variables using the Parameters window.

Note: You can retrieve the query plan for a SQL statement that has a variable without the need to enter the data type or value by executing the Show Default Plan function from the SQL menu.

Parameters Window

SQL Information and Functions > Parameters Window

View Parameters Window

The data type and value of parameters are entered using the Parameters window. If parameters are embedded in the SQL statement, the Parameters window displays each time you optimize the SQL statement, execute the Show Plan, Run Result, Run Time, Batch Run functions, or create a temporary table through the User-Defined Temp Table window.

The parameter values that you have previously entered can be saved by selecting the Enable SQL parameter history Preferences setting. If the same parameter is used in a SQL statement again, the value and the data type from the last time it was used is automatically entered for you.

To help identify the data type and value, click Browse to expand the Parameters window. The bottom pane displays displaying object and data information corresponding to the selected objects from the SQL statement. Select the object to browsed using the Objects and Columns drop-down list. If a column is selected (asterisk * selects all columns from the selected object), the corresponding data type is shown.

Click Load Data and highlight the cell with the variable value from the data grid. Then click Use Data & Datatype or double-click the cell to copy the compatible data type and value to the parameter selected at the top of the window.

Click Use Datatype to copy a compatible data type to the parameter selected at the top of the window.

After the data type and value of all the parameters have been selected, click OK.

Notes:

The values entered in the Parameters window has a direct effect the on the query plan, run time and run result retrieved and on the resulting SQL alternatives that are generated during the optimization process.
You can edit the data using the Button Bar at the bottom of the Parameter window.
If the Parameters window displays when you do not have variables in the SQL statement, this may be caused by incorrect spelling of column or table names, having the wrong database or user selected, or the tables or column do not exist in the database.

Filter Database Objects

SQL Information and Functions > Filter Database Objects

View Add Database Objects Window

When you are selecting database objects from the list of all objects, you can filter the list in order to more quickly locate specify objects.

To open the Add Database Objects window.

Click Browse.
Enter the following information to filter the database objects.

Field	Description
Database	Specify the database name where the objects reside.
User	Specify the owner of the database objects.
Object type	Select the database object type from the list.
Filter	Using the % wildcard that replaces multiple characters, enter the filtering criteria. The filter is case sensitive, so you must match the upper or lowercase characters of the database object name.

SQL Information Pane

SQL Information and Functions > SQL Information Pane

The SQL Information pane displays in several modules. It provides a variety of information about the SQL statement like the query plan, abstract plan, trace on and run time information. The specific information is dependent upon the function of the module.

The SQL Information pane displays SQL information according to the button you select from the button bar $image\BB_SQLInformation--Sybase.gif$ at the top of this pane. The exact buttons that you find at the top of this pane varies from one module to the next. These buttons include: query plan, abstract plan, trace on, other information (SQL classification, connection information, warning messages, etc), all records statistics, first record statistics, Scanner temp table and checked details.

Query Plan

Displays the SQL query plan. When two SQL Information panes are displayed side-by-side, as in the SQL Comparer module and the Analyzer modules, the differences between the operations are highlighted in green.

Abstract Plan

The Abstract Plan button is only available for Adaptive Server 15 or later and you have selected Dump abstract plan option in the Preferences window.

Displays the abstract plan which describes the query plan using a language created for that purpose. This language contains operators to specify the choices and actions that can be generated by the optimizer.

Trace On

The Trace On button is only available if you have selected dbcc traceon (3604, 302, 310) option in the Preferences window and have sa_role privilege.

Trace on shows the reason why the Adaptive Server optimizer chose a particular way of executing the original SQL statement, displaying the reasons for index and table joins selection.

Information

The exact information depends on the module. It always displays the SQL classification type. This classification is dependent on the parameters set in the Preferences window to identify whether the original SQL statement is potentially Problematic. It may also display:

Warning or alert information about the SQL statement.
SQL statement type classification: Problematic, Complex, Simple, or Invalid.
Database error message if SQL is classified as Invalid.
SQL conversion applied, if conversions have been added to the scanned SQL text in order for it to generate a query plan, this information is also displayed.
Start position of the SQL statement (SQL Scanner only, for database object, TXT and SQL files).
Connection information (Login name, Server name, Database and User)
Database Settings from the Preferences window.
Session settings for the optimization timeout limit and optimization goals. (Available in Adaptive Server 15 or later) The optimization timeout limit is an Adaptive Server configuration parameter that specifies the amount of time Adaptive Server can spend optimizing a query as a fraction of the total time spent processing the query. The optimization goals are selected by you in the Preferences settings.

All Records

Displays the run time information that is collected when retrieving all records of the query.

First Record

Displays the run time information that is collected when retrieving the first record of the query.

Both All Records and First Record buttons display the run time statistical information:

The amount of disk activities needed to execute the SQL statement.

Number of scans performed

Number of pages read from the data cache

Number of pages placed into the cache

Number of pages read from disk

Number of scans performed

Number of milliseconds required to parse, compile, and execute each SQL statement.
SQL script used to test run the SQL statement.

Scanner Temp Table (SQL Scanner)

Displays the temporary table SQL statement assumed to create or modify the temporary table used on the scanned SQL statement if the SQL Scanner finds it in the source.

Checked Details (SQL Scanner)

If the selected SQL statement is checked, then this page displays the date time of when the SQL statement was checked, the name of person who checked the SQL, the status, and the checked description.

Index Advisor Window (Index Advisor)

Displays information about how the index will be created and the estimated amount of space required for the index.

Documentos relacionados

The document was helpful.

Selecione a classificação

I easily found the information I needed.