topic Re: Help with runtime analysis in Application Development and Automation Discussions

Help with runtime analysis

Former Member — Wed, 02 May 2007 12:32:56 GMT

Hi,

I have a report with pretty bad performence and found the SE30 - ABAP runtime analysis to test it with.

In the hit list I can find an Open Cursor S615 that use 36% of the runtime and a Fetch S615 that uses 13% of the runtime.

What is open cursor and fetch? As I understand it is a different way to write a select statement but the program are using an ordinary select.

regards,

Glenn

Re: Help with runtime analysis

Former Member — Wed, 02 May 2007 12:35:58 GMT

Hi,

In the normal SELECT statement, the data from the selection is always read directly into the target area specified in the INTO clause during the SELECT statement. When you use a cursor to read data, you decouple the process from the SELECT statement. To do this, you must open a cursor for a SELECT statement. Afterwards, you can place the lines from the selection into a flat target area.

Opening and Closing Cursors

To open a cursor for a SELECT statement, use the following:

OPEN CURSOR [WITH HOLD] <c> FOR SELECT <result>

FROM <source>

[WHERE <condition>]

[GROUP BY <fields>]

[HAVING <cond>]

[ORDER BY <fields>].

You must first have declared the cursor <c> using the DATA statement and the special data type CURSOR. You can use all clauses of the SELECT statement apart from the INTO clause. Furthermore, you can only formulate the SELECT clause so that the selection consists of more than one line. This means that you may not use the SINGLE addition, and that the column selection may not contain only aggregate expressions.

An open cursor points to an internal handler, similarly to a reference variable pointing to an object. You can reassign cursors so that more than one points to the same handler. In a MOVE statement, the target cursor adopts all of the attributes of the source cursor, namely its position, and all of the clauses in the OPEN CURSOR statement.

You can also open more than one cursor in parallel for a single database table. If a cursor is already open, you cannot reopen it. To close a cursor explicitly, use the following statement:

CLOSE CURSOR <c>.

You should use this statement to close all cursors that you no longer require, since only a limited number of cursors may be open simultaneously. With one exception, a database LUW is concluded when you close a cursor either explicitly or implicitly. The WITH HOLD addition in the OPEN CURSOR statement allows you to prevent a cursor from being closed when a database commit occurs in Native SQL.

Reading Data

An open cursor is linked to a multiple-line selection in the database table. To read the data into a target area in the ABAP program, use the following:

FETCH NEXT CURSOR <c> INTO <target>.

This writes one line of the selection into the target area <target>, and the cursor moves one line further in the selection set. The fetch statement decouples the INTO clause from the other clauses in the SELECT statement. All the INTO clauses of the SELECT statement can be used. The statement reads the lines that are needed to fill the target area of the INTO clause and moves the cursor to the next line to be read.

SY-SUBRC is set to 0 until all the lines of the selection have been read; otherwise it is 4. After a FETCH statement, system field SY-DBCNT contains the number of all the lines read so far for the corresponding cursor.

Look at the Demo Programs

demo_select_cursor_1

demo_select_cursor_2

demo_select_cursor_3

Regards

Sudheer

Re: Help with runtime analysis

Former Member — Wed, 02 May 2007 12:37:37 GMT

Hi!

Just double click on the relevant keyword (OPEN CURSOR / FETCH), and it will navigate you to the source code.

You'll see, what you can do to improve the performance.

Regards

Tamá

Re: Help with runtime analysis

Former Member — Wed, 02 May 2007 12:44:44 GMT

The open cursor and fetch-statements are pointing to the same code. It is a very messy select with three joins and a lot of sum-calculations.

One of the select-options are for material and contain 70 items to search for. If the user don't fill this select-option the report are much faster. Are there any limits saying that you should not use more than say 20 items in a select -option to avoid a full table scan or something?

regards,

Glenn

Re: Help with runtime analysis

alison_lloyd — Wed, 02 May 2007 18:19:07 GMT

Try replacing jopin with differnt internal tables and select for all entries (delete adjacent duplicates if you have them i.e create auxiliary for all entries tables). You can also vary the order of the 3 new select statements based on which selection criteria are filled. Also put an information message on selection screen if 'important' selection criteria are not filled or for e.g. if materials are not filled in can you restict to material type and/ or group?