Chat now with support
Chat with Support

SQL Optimizer for SAP ASE 3.8 - User Guide

Introduction Tutorials Preferences Editor Functions SQL Information and Functions Performance Monitor SQL Inspector SQL Collector for Monitor Server SQL Scanner Index Advisor SQL Optimizer
SQL Optimizer Overview Optimization Engine Common Coding Errors in SQL Statements What Function Should l Use to Retrieve the Run Time? Unsatisfactory Performance Results SQL Optimizer Functions SQL Editor Optimized SQL Activity Log
SQL Worksheet SQL Formatter Database Explorer Code Finder Object Extractor SQL Repository Index Impact Analyzer Index Usage Analyzer Configuration Analyzer Migration Analyzer Abstract Plan Manager User-Defined Temp Tables SQL History Legal Information

Parameters Window

SQL Information and Functions > Parameters Window
ClosedView 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.

 

Related Topics

Parameters

Input Parameter Values

Filter Database Objects

SQL Information and Functions > Filter Database Objects
ClosedView 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.

  1. Click Browse.

  2. 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.

 

Related Topic

Add Database Objects (Add Jobs Wizard)

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.

 

Query Plan

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating