Chatta subito con l'assistenza
Chat con il supporto

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

View SQL scripts of temporary tables

User-Defined Temp Tables > View SQL scripts of temporary tables

After the user-defined temporary tables are created, you can view the SQL scripts used to create and populate the tables.

To view a script

  1. Click image\B_UserDefinedTempTable.gif.

  2. Select the Temp Table List tab.

  3. Select the temporary table from the Temp Table drop down list.

 

Related Topics

Drop Temporary Tables

User-Defined Temp Tables > Drop Temporary Tables

After the User-Defined temporary tables are created, you can drop them.

To drop temporary tables

  1. Click .

  2. Select the Temp Table List tab.

  3. If you want to remove all temp tables, click Drop All Table.

 

Related Topics

Example of Temporary Tables in SQL Scanner

User-Defined Temp Tables > Example of Temporary Tables in SQL Scanner

Example with CREATE TABLE in the source code containing the following SQL statements:

/* SQL1 */

CREATE TABLE #temp1

([EMP_ID] float NOT NULL,

[EMP_NAME] varchar (40) NOT NULL)

 

/* SQL2 */

Insert into #temp1 (EMP_ID, EMP_NAME)

values (1, 'Rene')

 

/* SQL3 */

Insert into #temp1 (EMP_ID, EMP_NAME)

values (2, 'Andrew')

 

/* SQL4 */

Insert into #temp1 (EMP_ID, EMP_NAME)

values (3, 'Claudia')

 

/* SQL5 */

Select * from #temp1

In the above example, when the SQL Scanner finds the first INSERT statement, it executes the CREATE TABLE statement, so that the temporary table #temp1 is created. The query plan for the INSERT statement is obtained and the statement is classified as to how likely it is to be causing a performance problem. In the SQL Information Pane of the SQL Scanner window, the Scanner Temp Table pane displays the SQL statements used to create the temporary table. At then end of scanning process, the temporary table is dropped unless you have set the Override User-Defined Temp Table option.

In the Preferences, an option is provided to include the data in the temporary table. When you select the Include data option, the INSERT, UPDATE and DELETE statements that populate the temporary table are also executed. These statements are displayed along with the CREATE TABLE command in the Information Pane in the SQL Scanner window.

From the example above, the SQL Scanner window shows the following for SQL5 (select * from #temp1) under the Scanner Temp Table button. It shows the SQL statement used to create the temporary table and the data that is inserted into it during the scanning process.

/* with Included data option selected in Preferences */

CREATE TABLE #temp1

([EMP_ID] float NOT NULL,

[EMP_NAME] varchar (40) NOT NULL)

Insert into #temp1 (EMP_ID, EMP_NAME)

values (1,'Rene')

Insert into #temp1 (EMP_ID, EMP_NAME)

values (2,'Andrew')

Insert into #temp1 (EMP_ID, EMP_NAME)

values (3,'Claudia')

Example using SELECT INTO to create the temporary table.

After scanning, the SQL Scanner window shows the following two statements:

/* SQL1 */

select EMP_ID,

EMP_NAME

into #temp1

from EMPLOYEE

 

/* SQL2 */

select *

from #temp1

 

For SQL2, the Scanner Temp Table pane shows that the SELECT INTO SQL statement was used to create the temporary table.

select EMP_ID,

EMP_NAME

into #temp1

from EMPLOYEE

 

Related Topics

Copy SQL with Temporary Tables to SQL Optimizer

User-Defined Temp Tables > Copy SQL with Temporary Tables to SQL Optimizer

When you copy a SQL statement from the SQL Scanner to the SQL Optimizer module, the Send to SQL Optimizerfunction automates this process for you. It copies the SQL statement, opens the SQL Optimizer window and inserts the SQL statement in the SQL Editor pane. When the SQL statement you are copying also includes a temporary table, the Send to SQL Optimizer function also copies the SQL statements for creating and populating the temporary table to the User-Defined Temp Table module.

Two items need to be considered when the SQL statements are automatically copied in the User-Defined Temp Table module. Is there SQL text already in the module? And does the temporary table already exist with the same or different data?

If SQL text is already entered into the User-Defined Temp Table module, you are prompted to save the current SQL to an ASCII file before it is overlaid with the new text. Saving the text enables you to use it again.

If a table with the same name already exists and you would like to recreate it with either a new definition or different data, then you must drop the table before executing the new SQL text. You can drop temporary table from the Temp Table List tab in the User-Defined Temp Table module.

 

Related Topics

Related Documents

The document was helpful.

Seleziona valutazione

I easily found the information I needed.

Seleziona valutazione