Report taking a lot of time to get executed

[former_member231951]

hi all,

There is one report which is taking a lot of time to get executed. i have checked the code. There are no select queries inside loop.

The only problem is there is a loop on internal table which is having 3000 line items and after some processing there is one more loop inside it having 30000 line items

Can you suggest me how to optimize this report

thanks in advance

Ayush Sethi

[Former Member]

Hi Ayush,

Is there any common field between both table?

If your answer is yes, you can do it in following way:

Loop on table with 30000 lines(Normally this should be item structure). And read line from table with 3000 lines(normally should be header structure). This will reduce unnecessary looping.

If this doesn't work out, Can you post that part of code?

Thanks,

Hardik

[former_member231951]

thanks for your reply

This is the code which is causing problem.

LOOP AT IT_EKPO.   " header wise report

     READ TABLE IT_EKKO WITH KEY EBELN = IT_EKPO-EBELN BINARY SEARCH.

     READ TABLE IT_EKET WITH KEY EBELN = IT_EKKO-EBELN BINARY SEARCH.

     READ TABLE IT_LFA1 WITH KEY LIFNR = IT_EKKO-LIFNR BINARY SEARCH.

     READ TABLE IT_ZPTERM WITH KEY PTERM = IT_EKKO-ZTERM BINARY SEARCH.

     READ TABLE IT_KONV WITH KEY KNUMV = IT_EKKO-KNUMV BINARY SEARCH.

     READ TABLE IT_MARA WITH KEY MATNR = IT_EKPO-MATNR BINARY SEARCH.

     "delete ADJACENT DUPLICATES FROM it_konv comparing kschl.

     "loop at it_ekpo where ( loekz ne 'L'  ).

     IF RB1 = 'X'.

       IF ( IT_EKPO-LOEKZ NE 'L' AND IT_EKPO-ELIKZ = '' )." or ( it_ekpo-loekz ne '' and it_ekpo-elikz = 'X' ).

         IT_FINAL-EBELN = IT_EKKO-EBELN.

         IT_FINAL-BSART = IT_EKKO-BSART.

         IT_FINAL-BEDAT = IT_EKKO-BEDAT.

         IT_FINAL-SPART = IT_MARA-SPART.

         LOOP AT IT_KONV WHERE ( KSCHL = 'BPXX' OR KSCHL = 'BP00' OR KSCHL = 'DC05' OR KSCHL = 'DC01' OR KSCHL = 'DC02' OR KSCHL = 'DC06' OR KSCHL = 'IHC0' ) AND KNUMV = IT_KONV-KNUMV.

           IF IT_KONV-KSCHL = 'BPXX'.

             BPXX = IT_KONV-KWERT.

           ELSEIF IT_KONV-KSCHL = 'BP00'.

             BP00 = IT_KONV-KWERT.

           ELSEIF IT_KONV-KSCHL = 'DC05'.

             DC05 = IT_KONV-KWERT.

           ELSEIF IT_KONV-KSCHL = 'DC01'.

             DC01 = IT_KONV-KWERT.

           ELSEIF IT_KONV-KSCHL = 'DC02'.

             DC02 = IT_KONV-KWERT.

           ELSEIF IT_KONV-KSCHL = 'DC06'.

             DC06 = IT_KONV-KWERT.

           ELSEIF IT_KONV-KSCHL = 'IHC0' AND IT_KONV-KHERK = 'C'.

             IHC0 = IT_KONV-KWERT.

           ENDIF.

           IT_FINAL-RLWRT = IT_FINAL-RLWRT + BPXX + BP00 + IHC0  + DC05 + DC01 + DC02 + DC06 .

           CLEAR: BPXX,

                  BP00,

                  DC05,

                  DC01,

                  DC02,

                  DC06,

                  IHC0.

         ENDLOOP.

[former_member231951]

There is one more problem. Like if you have checked the code. The inner loop is on table It_konv which is the condition based table. So this loop has been used on various others places when the condition is fulfilled.

[former_member231951]

if you want i will show the whole code!!

[Former Member]

HI Ayush,

There is two suggestions from me for your code:

1.

Can you try to change following condition:

LOOP AT IT_KONV WHERE ( KSCHL = 'BPXX' OR KSCHL = 'BP00' OR KSCHL = 'DC05'OR KSCHL = 'DC01' OR KSCHL = 'DC02' OR KSCHL = 'DC06' OR KSCHL = 'IHC0' ) AND KNUMV = IT_KONV-KNUMV.

to

LOOP AT IT_KONV WHERE ( KSCHL = 'BPXX' OR KSCHL = 'BP00' OR KSCHL = 'DC05'OR KSCHL = 'DC01' OR KSCHL = 'DC02' OR KSCHL = 'DC06' OR KSCHL = 'IHC0' ) AND KNUMV = IT_EKKO-KNUMV.

2.

If you are sure that number of records in IT_KONV will be exactly one. No need of looping as you are already reading it as:

READ TABLE IT_KONV WITH KEY KNUMV = IT_EKKO-KNUMV BINARY SEARCH.

Thanks,

Hardik

[Former Member]

Hi Ayush,

It seems problem is occurring due to condition "AND KNUMV = IT_KONV-KNUMV.". In such cases, It wil process each record which is not required.

Thanks,

Hardik

[former_member231951]

Hi hardik,

there is one more read statement above this code which is

READ TABLE IT_KONV WITH KEY KNUMV = IT_EKKO-KNUMV BINARY SEARCH

from this it is validating.

.

[nikhil_kalawade]

This message was moderated.

[former_member209119]

Hi Ayush,

You can use parallel cursor technique. This will reduce the execution time.

Thanks,

A

[Former Member]

I would switch EKKO and EKPO (loop at headers and read items). I assume that both are sorted by key. I also assume that KONV is sorted by KNUMV. If not, do a SORT STABLE. Fix the logic to use the KNUMV from EKKO and add the EBELP for the proper item reference.

Now you have 1:n relationships between EKKO and EKPO and between EKPO and KONV. For both do the same:

- Read the first record by key with binary search to get SY-TABIX (you can use transporting no fields).

- Then loop at the inner table from SY-TABIX. Check if the key is no longer right and exit if not.

- Take the condition types out of the WHERE for KONV and check them inside the body.

- While you are at it, rewrite the inner logic of KONV to handle one type at a time. No need to add them all like you do. (more for maintenance than performance)

Be aware that a LOOP WHERE has to look at all records, so the exit logic will save you a bundle.

Wolfgang

[former_member231951]

hi,

I totally agreed to your point basically there 28000 entries in table KONV and depending upon each condition i need to fetch data so the main issue is arising at this point also.

LOOP AT IT_KONV WHERE KSCHL = 'ZLCC' AND KNUMV = IT_KONV-KNUMV AND ( KHERK = 'C' ) ." and lifnr = it_ekko-lifnr.

             "SORT IT_KONV BY KWERT .

             CH_VAL = IT_KONV-KWERT.

             IT_FINAL-CH_AMT = IT_FINAL-CH_AMT + CH_VAL.

           ENDLOOP.

           "it_final-cust_duty_dat = it_final-zzdate - 2.

           IT_FINAL-CUST_DUTY_DAT = IT_FINAL-EINDT.

           LOOP AT IT_KONV WHERE ( KSCHL = 'JCDB' OR KSCHL = 'NCVD' OR KSCHL = 'JEDB' OR KSCHL = 'JSDB' OR KSCHL = 'SECN' OR KSCHL = 'NCCD' OR KSCHL = 'ECEN' OR KSCHL = 'CVDN' )

                                  AND KNUMV = IT_KONV-KNUMV." and lifnr = it_ekko-lifnr.

             CUST_VAL = IT_KONV-KWERT.

             IT_FINAL-CUST_DUTY_AMT = IT_FINAL-CUST_DUTY_AMT + CUST_VAL.

           ENDLOOP.

           "it_final-imp_frt_dat = it_final-EINDT + 10.     ""AS it is a header wise report it will pick the first line item delivery date

           IF IT_FINAL-ZZDATE IS NOT INITIAL.

             IT_FINAL-IMP_FRT_DAT = IT_FINAL-ZZDATE + 30.

           ENDIF.

           LOOP AT IT_KONV WHERE ( KSCHL = 'FR02' ) AND KNUMV = IT_KONV-KNUMV AND ( KHERK = 'C' )." and lifnr = it_ekko-lifnr.

             FR02_VAL = IT_KONV-KWERT.

             IT_FINAL-IMP_FRT_AMT = IT_FINAL-IMP_FRT_AMT + FR02_VAL.

           ENDLOOP.

           IF IT_FINAL-ZZDATE IS NOT INITIAL.

             IT_FINAL-LOC_FRT_DAT = IT_FINAL-ZZDATE + 30.

           ENDIF.

           LOOP AT IT_KONV WHERE ( KSCHL = 'FR01' ) AND KNUMV = IT_KONV-KNUMV AND ( KHERK = 'C' )." and lifnr = it_ekko-lifnr.

             FR01_VAL = IT_KONV-KWERT.

             IT_FINAL-LOC_FRT_AMT = IT_FINAL-LOC_FRT_AMT + FR01_VAL.

           ENDLOOP.

thanks

[Jelena]

Ayush, not sure what further response you are expecting at this point... As already suggested, kindly use the performance evaluation tools available in SAP. For internal tables it is crucial to chose the right table type (SORTED or HASHED based on how data is accessed). This is explained in ABAP Help and there are many SCN posts on this as well.

And you can easily experiment by changing your code and checking how different variations perform. You'd learn much more from that than from just waiting for SCN to help.

As a side note - instead of going after the pricing condition records, check with the functional consultant if those items could be instead stored in the "subtotal" fields (EKPO-KZWI1...6). This functionality exists in the pricing procedure configuration in both SD and Purchasing, but it's amazing how few people actually understand it. Very rarely there should be a need to read KONV records. It seems that in this case poor design may have started with the pricing configuration as apparently no one considered the reporting needs there.

[Jelena]

Why do you need to use BINARY SEARCH? Judging by the name,  IT_EKKO contains the data from EKKO table. It has EBELN as primary key, so why is the table not defined as HASHED?

+1 to Wolfgang to start LOOP from the higher level EKKO table. Although I'm not convinced these actually have to be separate tables to begin with, most likely it could be just one big JOIN with all the tables but KONV.

LOOP AT IT_KONV WHERE ... Does other data actually have to be in this table? If not - add WHERE to SELECT. The loop can be done much simpler too because there is only one exception with 'IHC0'. In all other cases you are just adding KWERT. So why do you need all those variables?

LOOP AT ...
IF IT_KONV-KSCHL = 'IHC0' AND IT_KONV-KHERK <> 'C'. 
     CONTINUE.
ELSE.

     ADD IT_KONV-KWERT TO IT_FINAL-RLWRT.
ENDIF.

ENDLOOP.

Again, don't forget to use the right table type (HASHED/SORTED vs. STANDARD).

As a side note - there are many performance evaluation tools in SAP these days (e.g. SAT), make sure to use them before posting questions.

[matt]

Jelena Perfiljeva wrote:

Why do you need to use BINARY SEARCH? Judging by the name,  IT_EKKO contains the data from EKKO table. It has EBELN as primary key, so why is the table not defined as HASHED?

Because so many developers are stuck in the 20th century...