topic Re: performance issue in Application Development and Automation Discussions

performance issue

Former Member — Thu, 21 Nov 2013 06:00:27 GMT

good morning experts,

i have developed a report by copying ( tcode:: msrv6 ) into zmsrv6......below is the code i have write to fecth the field as per our reqiurement..

            IF XML_ESLL[] IS NOT INITIAL .
     SELECT packno                    **********performance issue************
             introw
             numkn
             zekkn
             FROM ESKL
             INTO TABLE IT_ESKL
             FOR ALL ENTRIES IN XML_ESLL
             WHERE PACKNO = XML_ESLL-PACKNO
             AND INTROW = XML_ESLL-INTROW.
       IF SY-SUBRC EQ 0.
         SORT IT_ESKL BY PACKNO INTROW.
       ENDIF.
   ENDIF.
   IF IT_ESKL[] IS NOT INITIAL.
----------->> SELECT packno                     ************performance issue**********************
                                zekkn
                              ps_psp_pnr                      *******************this the field i want to display in output***********************************
                             FROM ESKN
            INTO TABLE IT_ESKN
            FOR ALL ENTRIES IN IT_ESKL
            WHERE PACKNO IN EBELN
            AND ZEKKN = IT_ESKL-ZEKKN.
       IF SY-SUBRC EQ 0.
         SORT IT_ESKN BY PACKNO ZEKKN.
       ENDIF.
   ENDIF.

so experts output coming ok in production server.. but taking a lot of time to fetch the data from first and second select query from eskl and eskn tables ..

and i created a secondry index also..can any one help me out of this to over come the performance issue.......

regards,

uday...

Re: performance issue

Former Member — Thu, 21 Nov 2013 06:06:34 GMT

Dear bhaskar us the packno as well. it will improve the execution.

if possible create a secondary index for teh zekkn. n transport it on the down time.

->> SELECT packno ************performance issue**********************

zekkn

ps_psp_pnr *******************this the field i want to display in output***********************************

FROM ESKN

INTO TABLE IT_ESKN

FOR ALL ENTRIES IN IT_ESKL

WHERE PACKNO IN EBELN

AND ZEKKN = IT_ESKL-ZEKKN

and packno = it_eskl-packno.

regards,

Re: performance issue

Former Member — Thu, 21 Nov 2013 06:17:33 GMT

Hai Uday,

SELECT packno ************performance issue**********************

zekkn

ps_psp_pnr *******************this the field i want to display in output***********************************

FROM ESKN

INTO TABLE IT_ESKN

FOR ALL ENTRIES IN IT_ESKL

WHERE PACKNO IN EBELN

AND ZEKKN = IT_ESKL-ZEKKN.

in the above select query. Replace the where condition with "

PACKNO IN EBELN

AND ZEKKN = IT_ESKL-numkn.

Re: performance issue

arivazhagan_sivasamy — Thu, 21 Nov 2013 06:18:41 GMT

Hi Uday,

Can you possible to write below.

            FOR ALL ENTRIES IN IT_ESKL
            WHERE PACKNO = IT_ESKL-PACKNO
            AND ZEKKN = IT_ESKL-ZEKKN.

Arivazhagan S

Re: performance issue

Former Member — Thu, 21 Nov 2013 07:03:21 GMT

Hi,

Since PACKNO and ZEKKN is the primary key , i dont think u need to create any secondary index for this.

You may try like this :

SELECT packno

zekkn

ps_psp_pnr

FROM ESKN

INTO TABLE IT_ESKN

FOR ALL ENTRIES IN IT_ESKL

WHERE PACKNO EQ IT_ESKL-PACKNO.

AND ZEKKN EQ IT_ESKL-ZEKKN.

IF SY-SUBRC EQ 0.

delete it_eskn where packno not in ebeln.

SORT IT_ESKN BY PACKNO ZEKKN.

ENDIF.

Regards,

Priyaranjan

Re: performance issue

Former Member — Thu, 21 Nov 2013 07:24:19 GMT

Dear Uday,

create an Empty range table for the key field packno of the table ESKN

data : ra_packno type range of packno.

in your second select query

in where condition PACKNO IN RA_PACKNO.

This will improve the performance of your query.

Note : For any table to fetch the data the key fields should be supplied , If you don't have key fields in runtime

create a dummy range and supply it which increases the performance.

Secondary Index needs to be created only for NON-Key fields.

I hope this will give a better understanding.

check this link also

http://www.sapdev.co.uk/tips/tips_range.htm

Thanks.

With Regards,

Sudhir S

Re: performance issue

kethanuppalapati — Thu, 21 Nov 2013 07:25:12 GMT

Hi uday,

For large amount of data using 'for all entries' sometimes becomes performance issue. I have a similar situation once. Try applying inner join on the two tables.

Select a~packno a~ introw a ~ numkn a ~zekkn b~ ps_psp_pnr

into IT_ESKL

FROM ESKL as a innerjoin ESKN as b on ( a~zekkn = b~zekkn and a~packno = b~packno)

Where packno = XML_ESLL-PACKNO and

b~INTROW = XML_ESLL-INTROW.

Regards,

Kethan.

Re: performance issue

former_member219762 — Thu, 21 Nov 2013 10:24:49 GMT

Hi,

Split XML_ESLL and use parallel processing.

Regards,

Sreenivas.

Re: performance issue

Former Member — Thu, 21 Nov 2013 14:18:00 GMT

Hey Uday,

It depends on the size of data which you are trying to fetch from the database.

For all entries will give effective performance tuing results when it is used with thousands of records only.

1) if your data expected is more than thousands its better to use the inner joins.

2) make sure that the fields you mentioned in the SELECT clause and WHERE clause are in the same order as they exists in the DDIC table.

3) delete the duplicates in the internal tables which you are using in SELECT statements.

4) sort the data in the internal tbales according to the databse table key before sending it to the select query.

try these, may be helpful to you.

Regards,

Bhaskar

Re: performance issue

Former Member — Thu, 21 Nov 2013 15:06:21 GMT

Hi Uday,

The problem might be with the data coming in XML_ESLL table which may be huge.

First check the number of entries into XML_ESLL table and try to reduce them as FOR ALL ENTRIES will match each entry of this table with the other table i.e. with IT_ESKL.

Please go through the below link in which it is clearly written that FOR ALL ENTRIES should be avoided in case of large amount of data.

http://wiki.scn.sap.com/wiki/display/Community/ABAP+Performance+tips

If possible pass all primary keys while fetching the data as this will reduce the number of entries going to be fetched from that table and will make your next Select faster.

So, Pass NUMKN if possible while fetching data from ESKL which will reduce the number of records in IT_ESKL table.

Try doing it by giving some hard coded values for NUMKN whether it is improving its performance or not and also try to use INNER JOIN between first two tables (XML_ESLL and IT_ESKL) and FOR ALL ENTRIES for the last table IT_ESKN.

Regards,

Sonal Garg

Re: performance issue

Former Member — Mon, 25 Nov 2013 10:09:25 GMT

Hi Uday,

Data can be filtered at staring level itself with ebeln.

No need to create secondary index.

Try this..

* filter essl data by ebeln from selection..

xml_esll_temp[] = xml_esll[].

DELETE xml_esll_temp WHERE packno NOT IN ebeln.

SORT xml_esll_temp by packno introw.

* delete duplicate entries

DELETE ADJACENT DUPLICATES FROM xml_esll_temp COMPARING packno introw.

            IF XML_ESLL_TEMP[] IS NOT INITIAL .
     SELECT packno                    **********performance issue************
             introw
             numkn
             zekkn
             FROM ESKL
             INTO TABLE IT_ESKL
             FOR ALL ENTRIES IN XML_ESLL_TEMP
             WHERE PACKNO = XML_ESLL_TEMP-PACKNO
             AND INTROW = XML_ESLL_TEMP-INTROW.
       IF SY-SUBRC EQ 0.
         SORT IT_ESKL BY PACKNO ZEKKN.

IT_ESKL_TEMP[] = IT_ESKL[].

* Delete duplicate entries

         DELETE ADJACENT DUPLICATES FROM it_eskl_temp COMPARING packno zekkn.
       ENDIF.
   ENDIF.
   IF IT_ESKL_TEMP[] IS NOT INITIAL.
----------->> SELECT packno                     ************performance issue**********************
                                zekkn
                              ps_psp_pnr                      *******************this the field i want to display in output***********************************
                             FROM ESKN
            INTO TABLE IT_ESKN
            FOR ALL ENTRIES IN IT_ESKL_TEMP
            WHERE PACKNO IN IT_ESKL_TEMP-PACKNO
            AND ZEKKN = IT_ESKL_TEMP-ZEKKN.
       IF SY-SUBRC EQ 0.
         SORT IT_ESKN BY PACKNO ZEKKN.
       ENDIF.
   ENDIF.

Re: performance issue

Former Member — Tue, 26 Nov 2013 04:53:57 GMT

Dear All,

Thank you For The Valuable Suggestions..

i have Fixed The Problem..the Value Is Passing Same Like Xml_esll-ebeln = eskn-ebeln..both value are same so..Performance Issue Has Been Fixed..

Regards,

uday..