performance issue

[Former Member]

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...

[Former Member]

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,

[Former Member]

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.

[arivazhagan_sivasamy]

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

[Former Member]

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

[Former Member]

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

[kethanuppalapati]

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.

[former_member219762]

Hi,

Split XML_ESLL and use parallel processing.

Regards,

Sreenivas.

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

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..