topic Re: Performance tuning (runtime limit) in Application Development and Automation Discussions

Performance tuning (runtime limit)

Former Member — Thu, 15 Nov 2012 10:08:37 GMT

Hello Gurus,

I'm trying to improve the performance of a report. I have the runtime limit fixed to 1800 seconds but I have to access to huge table like Rseg, Ekpo ,or Ekbe most and most of the time the access is with a partial key.

Which of these 2 accesses is more efficient:

select ebeln ebelp zeile belnr gjhar from ekbe into corresponding fields of table it_ekke

where ebeln = it_ekko-ebeln

and ebelp = it_ekko-ebelp.

or it's better doing

select ebeln ebelp zeile belnr gjhar from ekbe appending corresponding fields of table it_ekke package size 10000

where ebeln = it_ekko-ebeln

and ebelp = it_ekko-ebelp.

knowing that it_ekpo has 600000 records.

Thanks.

Re: Performance tuning (runtime limit)

Former Member — Thu, 15 Nov 2012 11:03:42 GMT

Hi,

First of all can you not drop INTO CORRESPONDING FIELDS OF?? INTO CORRSEPONING will serch for the field names in the internal table and will then insert resulting in poor performance.

If this is an existing program and you just cannot change the entire select logic then its better to avoid appending corresponding. But trust me, you are asking to choose between worse and worst.

Thanks,

Anubhav

Re: Performance tuning (runtime limit)

Former Member — Thu, 15 Nov 2012 11:46:46 GMT

Hi,

If you want to avoid the TIMEOUT dump you can split your SELECT to several selects, for example using PACKAGE SIZE. And you need to add progress indicator which resets time counter.

Re: Performance tuning (runtime limit)

gouravkumar64 — Thu, 15 Nov 2012 12:26:30 GMT

Hi,

Try like this

select ebeln ebelp zeile belnr gjhar from ekbe into table it_ekke

where ebeln = it_ekko-ebeln

and ebelp = it_ekko-ebelp.

or it's better doing

select ebeln ebelp zeile belnr gjhar from ekbe appending table it_ekke package size 10000

where ebeln = it_ekko-ebeln

and ebelp = it_ekko-ebelp.

whenever use more than 5 joins better to use -for all entries.

for progress INDIACTOR USE LIKE THIS,

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'
     EXPORTING
       percentage = pv_percentage
       text       = 'In Progress'.

Thanks

Gourav.

Re: Performance tuning (runtime limit)

Clemenss — Thu, 15 Nov 2012 13:16:37 GMT

Sorry Anubhav Duttagupta,

it is complete nonsense that INTO CORRESPONDING FIELDS OF will affect performance because it is evaluated at compile time in this case.

Do not believe everything that my have been true 20 years ago.

Regards,

Clemens

Re: Performance tuning (runtime limit)

Former Member — Thu, 15 Nov 2012 15:40:10 GMT

Boss, this forum is not for targeting others posts.

You disagree you put your own point with evidence. Simple as that.

Advice you to be more focussed when you reply.

Cheers

Anubhav

Re: Performance tuning (runtime limit)

ThomasZloch — Thu, 15 Nov 2012 15:42:38 GMT

Before starting to remove INTO CORRESPONDING, splitting joins or calling progress indicators (the first two will not help; the last one is curing the symptom, but not the cause), you should find the real bottlenecks. Did you run a runtime analysis with transactions SAT or ST12 (please search for additional related information)?

Can this report simply run in a background process? Is this a report that users look at or is it rather some interface or automatic process? Can you force users to enter narrow selection criteria?

Thomas

Re: Performance tuning (runtime limit)

ThomasZloch — Thu, 15 Nov 2012 15:48:26 GMT

Well, Clemens was quite direct, but he is right in saying that removing INTO CORRESPONDING has almost no effect (less than 1% gain, if at all, look for previous discussions of this topic in this space, some with measurements). On the contrary, this construct allows for some neat programming logic that I would not want to miss.

You are posting in a public space, anybody is invited to challenge statements made by others, as long as our specific rules of engagement and professionalism in general are observed.

Thomas

Re: Performance tuning (runtime limit)

Former Member — Thu, 15 Nov 2012 15:50:48 GMT

Absolutely agree. Optimizing the report is of paramount importance.

Re: Performance tuning (runtime limit)

Clemenss — Thu, 15 Nov 2012 16:29:24 GMT

Hi,

starting with the alternatives you gave originally, please note what SAP help says:

"If the addition PACKAGE SIZE is specified together with FOR ALL ENTRIES, all rows selected are first imported to an internal system table. The packages are only formed during the transfer from the system table to the actual target table. In this way, the FOR ALL ENTRIES addition overrides the effect of PACKAGE SIZE to prevent memory overflows."

Both statements mentioned can work with FOR ALL ENTRIES only except if the select statement is inside a loop at it_ekko - which I do not believe.

Using FOR ALL ENTRIES the data are transported in packages of 5 rows from the database by default. Here you may have better results using database hints in the select statement, i.e.

SELECT ...

INTO

FROM

FOR ALL ENTRIES IN ...

WHERE ...

%_HINTS ORACLE '&max_in_blocking_factor 100& &max_blocking_factor 100&'.

Regards

Clemens

Re: Performance tuning (runtime limit)

Former Member — Mon, 19 Nov 2012 09:06:05 GMT

thanx to you all, all the replies were really helpul, i have optimized the code using the for all entries and most of all optimized the accesses to the table before this point.

There was still a run time limit but i overtook it with

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

percentage = pv_percentage

text = 'In Progress'.