Chat now with support
Chat with Support

SQL Optimizer for SAP ASE 3.9.1 - 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

Options (SQL Scanner Tab)

Skip SQL within comments (Default = cleared)

Specify whether the scanning algorithm will ignore any SQL statements within comments enclosed by /* */, // and -- found in the source code. By default, the scanning algorithm will search for any SQL statements contained in comments.

Skip SQL that does not involve tables (Default = cleared)

Specify whether the scanning algorithm will ignore any SQL statements that do not involve tables.

Whole word matching for the first SQL keyword (Default = cleared)

Specify to search for SELECT, INSERT, UPDATE or DELETE as a whole word, the keyword must be preceded and followed by a space. The Scanner therefore will not find something like PROCEDUREINSERT and attempt to build a SQL statement from it.

Always use current database and user (Default = cleared)

Specify to always use the current database and user when the SQL Scanner is retrieving the query plan.

Automatically switch to current database and user when unable to set user to dbo

Specify to allow the SQL Scanner to switch the database and user for a job to the current database and current user when the login is unable to 'setuser dbo' during scanning. You will notice that the Job name is changed from "[xx] [dbo] yyy" to "[current db][current user] yyy" when the 'setuser dbo' fails. Also, in the information pane in the SQL Scanner window under "Connection Information," this line is added "Scanned using the current database and user setting".

When this option is not selected and the login is unable to ‘set user to dbo’, the SQL Scanner displays the ‘cannot set dbo’ status message in the Status column and it skips the Job.

Create Scanner Temp Table (Default = checked)

If selected, the scanning algorithm automatically creates a temporary table during scanning when a CREATE TABLE #TEMP or a SELECT INTO #TEMP statement is found. Tables created during the scanning process are dropped at the end of the process.

Include data (Default = cleared)

If selected, the SQL Scanner automatically executes any INSERT, DELETE, UPDATE, and SELECT INTO SQL statement that modifies a temporary table that is created by the SQL Scanner.

If this option is not selected, then a WHERE 0 =1 clause is added to the SELECT INTO statement so that no data is included when the temporary table is created.

Note: Selecting the Include data option may affect the total scanning time.

Override previous Scanner Temp Table (Default = checked)

If this option is selected, a temporary table is created with the first CREATE TABLE #TEMP statement. If another CREATE TABLE #TEMP with the same name is found, it will drop the current table and create a new one for every time if find a new CREATE TABLE #TEMP.

This option will not override any temporary tables created in User-Defined Temp Table window.

Override User-Defined Temp Table (Default = cleared)

If selected, a previous User-Defined Temp Table is overwritten if the SQL Scanner finds another create temporary table statement using the same table name. This option will not override any temporary tables created in the SQL Scanner.

Number of characters to be skipped at the beginning of every line for all files (Default = 0)

When a file is scanned, the SQL Scanner skips the number of characters specified at the beginning of every line.

Skip end of line continuation character (Default = <do not use>)

If the text of the SQL statement is on more than one physical line and there is a continuation character at the end of each physical line of the SQL text, then specify what the continuation character is so that it will be skipped. If this character is not skipped, it may cause the SQL Scanner to miss part of the SQL statement. Three options are included: <Do not use>, / (forward slash character), and _ (underscore character). In addition, you may add your own character. This character will be saved in the field as long as it is the selected character. Once you make another selection, your own character will not be remembered.

 

Related Topics

Abstract Plan (SQL Scanner Tab)

The Abstract Plan page on the SQL Scanner tab of the Preferences window allows you to include the retrieval of the abstract plan for the SQL statements. This requires Adaptive Server 15 or later.

Dump abstract plan (Default = cleared)

Specify whether to retrieve the abstract plan for the SQL statement whenever the query plan is retrieved. The abstract plan displays in the SQL Scanner window. The abstract plan is not saved on the database.

When this checkbox is selected, the selected abstract plan icon image\I_AbstractPlanGroup.gif and the abstract plan group name display on the bottom right of the main status bar.

Note: For third-party package applications, you are able to optimize SQL statements without changing the source code by saving the abstract plan.

Group name (Default = blank)

Specify the abstract plan group name where the abstract plans are saved. The default groups names in Adaptive Server are: ap_stdout and ap_stdin. These groups are usually used by the Database Administrator to enable server-wide abstract plan capturing and retrieving.

ap_stdout is used by default to capture an abstract plan.

ap_stdin is used by default to retrieve the abstract plan associated with a SQL statement during the execution of the SQL statement.

Abstract Plan Manager button

Opens the Abstract Plan Manager window to view, create, and modify abstract plan group.

 

Related Topics

General (SQL Scanner Tab)

Interface

Use color tabs for SQL classification (Default = checked)

When checked, the SQL statements are displayed with color-coded tabs representing the SQL's classification. Red for Problematic, purple for Complex, green for Simple, and blue for Invalid. If the SQL statement is checked, the tab is grey.

Show Checked SQL figures on the SQL Scanner window (Default = cleared)

When checked, an additional column of numbers is added to the SQL classification columns to indicate how many SQL statements have been checked for each Job. When a SQL statement is added to the Checked SQL list, it has a value in the appropriate SQL classification column in the SQL Scanner window.

Checked List

Action when performing Send to SQL Optimizer and Send to Index Advisor

Prompt user for action

If selected, the user is prompted to be added to the Checked List.

Always add SQL

If selected, all SQL sent is added to the Checked List.

Never add SQL (Default)

If selected, all SQL sent is not automatically added to the Checked List.

Do not remove Check SQL information when rescanning (Default = cleared)

Specify to keep the checked SQL information when the Job is rescanned. If unchecked, the checked SQL information is removed when the Job is scanned again.

Compare the current SQL with the rescanned SQL using

SQL text (Default)

Specify to match the SQL statements from the previous time the Job was scanned with the current scanning using only the SQL text as a comparison for the match. The checked SQL information is preserved for those SQL statements where the SQL text matches.

SQL text and query plan

Specify to match the SQL statements from the previous time the Job was scanned with the current scanning using the SQL text and the query plan as a comparison for the match. The checked SQL information is only preserved for those SQL statements where the query plan and the SQL text match.

 

Related Topics

SQL Classification Tab

Related Documents