Query Performance

[Former Member]

Hi

How can I check the performance of an SQL statement in ABAP program using the TCode ST05?

What are the steps to follow on the screen?

Is there any other means to do the same?

[Former Member]

Hi,

Goto the tcode St05.

in this we have a menubar perfromance trace menu. In this

we have trace on and trace off is there

click on trace on and select the sql query there

after that click on trace off. r activate trace and deactivate trace.

if u want to check the performance of ur own select statement edit in enter sql statement. do the process there.

Please reward points, if it is useful.

regards,

satish.

[Former Member]

Hi

I have made Trace ON, Entered SQL Query n made Trace OFF.

After this, hw can I see the results?

[gopi_narendra]

Now you can see the results by pressing the button List Trace

Regards

Gopi

[Former Member]

Hi

Now am able to view the result.

But, sorry to say that it is tough to understand.

Am unable to make out anything from it.

What approach I need to have to analyze the result?

Thx

[Former Member]

hi jeeva,

Follow the below steps..

STEPS:

1.OPEN UR REPORT IN SE38 TRANSACTION.

2.SELECT THE MENU PATH

UTILITIES-->MORE UTILITIES-->RUNTIME ANALYSIS

3.SELECT THE PROGRAM RADIO BUTTON AND GIVE UR REPORT NAME THERE.AND CLICK <b>EXECUTE</b> BUTTON

4.IT WILL TAKE U TO ANOTHER SCREEN THERE U CAN EXECUTE UR REPORT AND COME BACK.

5.CLICK ON ANALYZE BUTTON.(PRESENT IN BELOW OF THE SCREEN).

THEN U WILL GET ONE GRAPH

NOTE:

1.IF IT IS RED UR PROGRAM IS HAVING VERY POOR PERFORMANCE

2.IF IT IS GREEN IT IS OK.

ABAP GRAPH : TELLS UR CODING IN THE REPORT.

DATABASE GRAPH : U R RETREIVE DATA FROM DATABASE IN UR REPORT..

SOME STEPS USED TO IMPROVE UR PERFORMANCE:

1. Avoid using SELECT...ENDSELECT... construct and use SELECT ... INTO TABLE.

2. Use WHERE clause in your SELECT statement to restrict the volume of data retrieved.

3. Design your Query to Use as much index fields as possible from left to right in your WHERE statement

4. Use FOR ALL ENTRIES in your SELECT statement to retrieve the matching records at one shot.

5. Avoid using nested SELECT statement, SELECT within LOOPs.

6. Avoid using INTO CORRESPONDING FIELDS OF TABLE. Instead use INTO TABLE.

7. Avoid using SELECT * and Select only the required fields from the table.

8. Avoid nested loops when working with large internal tables.

9. Use assign instead of into in LOOPs for table types with large work areas

10. When in doubt call transaction SE30 and use the examples and check your code

11. Whenever using READ TABLE use BINARY SEARCH addition to speed up the search. Be sure to sort the internal table before binary search. This is a general thumb rule but typically if you are sure that the data in internal table is less than 200 entries you need not do SORT and use BINARY SEARCH since this is an overhead in performance.

12. Use "CHECK" instead of IF/ENDIF whenever possible.

13. Use "CASE" instead of IF/ENDIF whenever possible.

14. Use "MOVE" with individual variable/field moves instead of "MOVE-

CORRESPONDING", creates more coding but is more effcient.

Award Points if helpful.

kiran kumar.G

Have a Nice Day..

[Former Member]

Hi

I have done the initial steps in ST05 - like Making Trace ON, entering the SQL Query and making the Trace OFF.

Now my query is how can I analyze the result of executing the Query?

How to see and interpret the outcome of executing it?

[Former Member]

In the trace listing, put your cursor on an SQL statement and press the "Explain" button. A screen will come up that will show you the standard time to execute the statement and the index used.

Rob

[Former Member]

Hi Rob

When am trying for checking the result, am getting this message.

Error received from Explain SQL

Message no. 0S033

What would be the reason?

[Former Member]

Hi

SQL Trace transaction ST05: The trace list has many lines that are not related to the SELECT statement in the ABAP program. This is because the execution of any ABAP program requires additional administrative SQL calls. To restrict the list output, use the filter introducing the trace list.

The trace list contains different SQL statements simultaneously related to the one SELECT statement in the ABAP program. This is because the R/3 Database Interface - a sophisticated component of the R/3 Application Server - maps every Open SQL statement to one or a series of physical database calls and brings it to execution. This mapping, crucial to R/3s performance, depends on the particular call and database system. For example, the SELECT-ENDSELECT loop on a particular database table of the ABAP program would be mapped to a sequence PREPARE-OPEN-FETCH of physical calls in an Oracle environment.

The WHERE clause in the trace list's SQL statement is different from the WHERE clause in the ABAP statement. This is because in an R/3 system, a client is a self-contained unit with separate master records and its own set of table data (in commercial, organizational, and technical terms). With ABAP, every Open SQL statement automatically executes within the correct client environment. For this reason, a condition with the actual client code is added to every WHERE clause if a client field is a component of the searched table.

To see a statement's execution plan, just position the cursor on the PREPARE statement and choose Explain SQL. A detailed explanation of the execution plan depends on the database system in use.

Starting the Trace:

To analyze a trace file, do the following:

...

Choose the menu path Test &#61614; Performance Trace in the ABAP Workbench or go to Transaction ST05. The initial screen of the test tool appears. In the lower part of the screen, the status of the Performance Trace is displayed. This provides you with information as to whether any of the Performance Traces are switched on and the users for which they are enabled. It also tells you which user has switched the trace on.

Using the selection buttons provided, set which trace functions you wish to have switched on (SWL trace, enqueue trace, RFC trace, table buffer trace).

If you want to switch on the trace under your user name, choose Trace on. If you want to pass on values for one or several filter criteria, choose Trace with Filter. -> Typical filter criteria are: the name of the user, transaction name, process name, and program name.

Now run the program to be analyzed.

Stopping the Trace:

To deactivate the trace:

...

Choose Test &#61614;Performance Trace in the ABAP Workbench. The initial screen of the test tool appears. It contains a status line displaying the traces that are active, the users for whom they are active, and the user who activated them.

Select the trace functions that you want to switch off.

Choose Deactivate Trace. If you started the trace yourself, you can now switch it off immediately. If the performance trace was started by a different user, a confirmation prompt appears before deactivation-

Analyzing a Sample trace data:

PREPARE: Prepares the OPEN statement for use and determines the access method.

OPEN: Opens the cursor and specifies the selection result by filling the selection fields with concrete values.

FETCH: Moves the cursor through the dataset created by the OPEN operation. The array size displayed beside the fetch data means that the system can transfer a maximum package size of 392 records at one time into the buffered area.

Reward if usefull

[Former Member]

.

[Former Member]

NOTE:

> 1.IF IT IS RED UR PROGRAM IS HAVING VERY POOR PERFORMANCE

> 2.IF IT IS GREEN IT IS OK.

No, red marks only statements which take more than 100 msec. Red statements can be o.k., and green ones

can have bugs!

Go to SQL statement summary !!!!

> 1. Avoid using SELECT...ENDSELECT... construct and use SELECT ... INTO TABLE.

> 2. Use WHERE clause in your SELECT statement to restrict the volume of data retrieved.

> 3. Design your Query to Use as much index fields as possible from left to right in your WHERE statement

> 4. Use FOR ALL ENTRIES in your SELECT statement to retrieve the matching records at one shot.

=> Use joins if possible!!!

> 5. Avoid using nested SELECT statement, SELECT within LOOPs.

> 6. Avoid using INTO CORRESPONDING FIELDS OF TABLE. Instead use INTO TABLE.

into corresponding is not a performance issue, it is useful.

> 7. Avoid using SELECT * and Select only the required fields from the table.

Use field list if you really reduce the number of fields by half.

> 8. Avoid nested loops when working with large internal tables.

Usually impossible, if nested loops are necessary, then you can not simply say avoid them, use

sorted tables for the inner table then everything is fine!

> 9. Use assign instead of into in LOOPs for table types with large work areas

> 10. When in doubt call transaction SE30 and use the examples and check your code

Better always test you coding performance with the SE30

11. Whenever using READ TABLE use BINARY SEARCH addition to speed up the search. Be sure to sort the internal table before binary search. This is a general thumb rule but typically if you are sure that the data in internal table is less than 200 entries you need not do SORT and use BINARY SEARCH since this is an overhead in performance.

The important point, if whether the read is used inside a loop, then binary search should always be used.

If it is not inside a loop,

12. Use "CHECK" instead of IF/ENDIF whenever possible.

13. Use "CASE" instead of IF/ENDIF whenever possible.

14. Use "MOVE" with individual variable/field moves instead of "MOVE-

CORRESPONDING", creates more coding but is more effcient.

Not that important.

Overall

The major performance issue is not addressed here, that is correct usage of indexes!

Check the SQL trace, check the time per record, if the minmal time per record is more than 10.000 microseconds, then it is probable that there is an index issue!

This is the TOP performance problem!

Read

Usage of SQL trace:

/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

Usage of SE30

/people/siegfried.boes/blog/2007/11/13/the-abap-runtime-trace-se30--quick-and-easy

Nested loop and correct usage of internal tables

Measurements on internal tables: Reads and Loops:

/people/siegfried.boes/blog/2007/09/12/runtimes-of-reads-and-loops-on-internal-tables

Siegfried