Tchater maintenant avec le support
Tchattez avec un ingénieur du support

SQL Optimizer for SQL Server 10.1.2 - User Guide

Welcome to SQL Optimizer Optimize SQL
Create Optimize SQL Sessions Open Optimize SQL Sessions Rewrite SQL Plan Control Use Temporary Tables
Optimize Indexes Find SQL Scan SQL Manage Plan Guides Configure Options Tutorials About Us

Scan SQL

SQL Scanner extracts SQL statements embedded in database objects, captured from SQL Profiler, or stored in application source code and binary files. Use the Add Scanner Jobs wizard to create scan jobs using statements from any of these sources.

To create a scan job

  1. Select the Scan SQL tab in the main window.
  2. Click Add Scanner Job from the Welcome pane.

    Tip: You can navigate directly to the embedded SQL statement for a particular format by clicking one of the links from the Welcome pane.

  3. Select a connection to use for the scan job. Review the following for additional information:

    Select Connection Description

    Connection

    Click to select a previously created connection.

    Tip: Click to open the Connection Manager to create a new connection. See Connect to SQL Server for more information.

    Select Database and User Description

    Database

    Click to select the database to match your SQL statement.

    Set User

    Click to select your user name.
  4. Select the page for the type of statement you want to add to the scan job. Review the following for additional information:

    Database Objects Page Description

    Database Objects

    Select a database object and click to add the object to scan.

    Tip: Click to browse for database objects to add.

    SQL Profiler Page Description

    Available trace files/table

    Click to add SQL Server Profiler trace files or trace tables to scan.

    Database

    Click to select the database of the SQL to scan.

    Set User

    Click to select your user name.
    Source Code Page Description

    Source code type

    Select Text/Binary files or COBOL programming source code to indicate the source code type.

    Add by file

    Click and browse to the files you want to scan.

    Add by directory

    Click and browse to the directories you want to scan.

    Note: Select the Include Sub-directory checkbox to scan sub-directories.

    Connection for scanning

    Select the database and user name settings.

    Note: SQL Scanner helps you manage scan jobs by organizing them into groups. Use the Group Information page to create a new group or to add the current scan job to an existing group.

  5. Select the Group for the scan job you created in the Scanner node of the Task pane once SQL Scanner finishes scanning.

  6. Select the scan job in the Group node.

  7. Identify problematic SQL statements in the SQL List pane. See View SQL Classification for more information.

  8. Select SQL statements to send to SQL Optimizer for optimization. See Send to Optimize SQL for more information.

  

Related Topics 

Manage Groups

Manage Scan Jobs

 

Filter Database Objects

To filter a database object

  1. Click in the Database Objects page of the Add Scanner Jobs wizard.

  2. Review the following for additional information:

    Database Click and select a database.
    Schema Click and select a schema.
    Object Type Click and select a database object type.
    Filter

    Enter the filtering criteria using the % wildcard and click .

    Important: The filtering criteria is case sensitive so you must match the uppercase or lowercase characters of the database object name.

    Notes:

    • The % is used to represent multiple characters.
    • Select the Exclude filter checkbox to exclude database objects that meet the filtering criteria instead of including them.
  3. Select the database objects to add to your scan job.

 

Related Topic

Manage Scan Jobs

Scan SQL

Scan Source Code with Temp Tables

SQL Scanner looks for CREATE TABLE, SELECT INTO, or other SQL statements that create and modify temporary tables when scanning source code that uses temporary tables. Once SQL Scanner locates these statements, it automatically creates or modifies the temporary tables if you selected the Create Scanner Temp Table checkbox in the Scanner Options page. SQL Scanner automatically drops the temporary tables after scanning the source code.

Review the following for additional information:

Source Code

select EMP_ID,

EMP_NAME       

into #a  

from EMPLOYEE  

 

select *

from #a  

 

Extracted SQL displayed in the SQL Text pane

SQL1:

select EMP_ID,

EMP_NAME       

into #a  

from EMPLOYEE  

SQL2:

select *

from #a  

 

SQL Scanner displays the DDL for creating the temporary tables in the Temp Table tab of the SQL Information pane. This includes the DDL found by SQL Scanner or the DDL used to create the user-defined temporary table.

select EMP_ID,

EMP_NAME       

into #a  

from EMPLOYEE  

 

Related Topics

Scan SQL

About Temporary Tables

Create Temporary Tables

Use Temporary Tables in Scan SQL

Scan SQL Options

 

Use Temporary Tables

Creating temporary tables to extract data from permanent tables is a technique you can use to improve SQL statement performance. When SQL Optimizer rewrites a SQL statement, it may use this technique to generate some of the alternatives. The following is an example of an original SQL statement rewritten to use temporary tables.

Original SQL

SELECT *

FROM A 

WHERE A.KEY IN (SELECT B.KEY  

FROM B)                   

 

SQL Alternative

SELECT DISTINCT COL1 = B.KEY

INTO #TEMP1  

FROM B  

 

SELECT *

FROM A, #TEMP1  

WHERE A.KEY = #TEMP1.COL1 

Notes:

  • Since temporary tables are session related, you need to create temporary tables for each new session.

  • SQL Scanner uses separate SQL Server sessions than SQL Optimizer. Therefore, temporary tables you create in SQL Scanner are not available when you send a statement to SQL Optimizer.

  

Related Topics  

Use Temporary Tables in Scan SQL

Scan Source Code with Temp Tables

Documents connexes

The document was helpful.

Sélectionner une évaluation

I easily found the information I needed.

Sélectionner une évaluation