topic Re: Performance Issue in Application Development and Automation Discussions

Performance Issue

Former Member — Fri, 25 Aug 2006 05:12:22 GMT

Hi All,

I am facing a big performance issue in a abap program which produce the cash flow details our group of company. This is the logic I used to develop the report. (It is a SAP Script)

- First I am getting the closed customer payment records from the table BSAD ( eg: - Type 'DZ')

- Then I am getting the corresponding invoices from the BSAD using the same BELNR ( eg :- Type <> 'DZ')

- Then checking the GL Entry (BSIS) for the corresponding records which select in the second stage.

- In a Z table I am keeping the account list by grouping separate section ( Eg: - Customer receipts, fixed assets...etc).

I have done the same thing to get the open item details also.

Report is correct and running perfectly, but my issue is it's taking long time to process. Because of this I made this report to run as a background job. But still it is taking such a long time. ( For Eg: - If 1000 records selected from the first stage, it will take about more than one hour to process, which is not good enough to run in a live environment)

Pls advice me how to improve the performance of this.

Here is a code sample how to see how I have make the logic



   SELECT SUM( dmbtr ) AS dmbtr belnr kunnr hkont shkzg xblnr budat
blart bldat
   INTO CORRESPONDING FIELDS OF TABLE it_voucher3
   FROM  bsad
   WHERE   bukrs = p_bukrs AND gjahr = p_gjahr
           AND augdt IN s_bldat
           AND blart = 'DZ'
*            AND blart = 'DZ' and shkzg = 'S'

           GROUP BY kunnr belnr hkont shkzg xblnr budat blart bldat.

   SORT it_voucher3 BY kunnr.

   LOOP AT it_voucher3 INTO wa_voucher3.

* Select the invoices
     SELECT SUM( dmbtr ) AS dmbtr belnr kunnr hkont shkzg xblnr budat
blart bldat
     INTO CORRESPONDING FIELDS OF TABLE it_voucher1
     FROM  bsad
     WHERE   bukrs = p_bukrs AND gjahr = p_gjahr
             AND augdt IN s_bldat
             AND blart <> 'DZ' AND
             augbl = wa_voucher3-belnr AND
             kunnr = wa_voucher3-kunnr

             GROUP BY belnr kunnr hkont shkzg xblnr budat blart bldat.

     LOOP AT it_voucher1 INTO wa_voucher1.

*  Find the corresponding entry in the GL open
      SELECT SINGLE dmbtr FROM bsis INTO w_Rcptamt WHERE
*           hkont = '0010003900' AND
           hkont IN ( select fglacc from zcashflow_matrix WHERE FCODE =
'XX001' ) AND  bukrs = p_bukrs  AND
               gjahr = p_gjahr  AND
               belnr = wa_voucher1-belnr.

       IF sy-subrc <> 0.
* If the corresponding entry not found in GL open, look in the GL Closed
      SELECT SINGLE dmbtr FROM bsas INTO  w_Rcptamt  WHERE
           hkont IN ( select fglacc from zcashflow_matrix WHERE FCODE =
'XX001' ) AND bukrs = p_bukrs  AND
              gjahr = p_gjahr  AND
              belnr = wa_voucher1-belnr.

            IF sy-subrc = 0.
              w_Netamt = w_Netamt + w_Rcptamt.
              continue.
            ENDIF.
       ELSE.
             w_Netamt = w_Netamt + w_Rcptamt.
             continue.
       ENDIF.

************************************************************************
* Interest Income
      SELECT SINGLE dmbtr FROM bsis INTO w_IntIncomeAmt WHERE
           hkont IN ( select fglacc from zcashflow_matrix WHERE FCODE =
'XX002' ) AND  bukrs = p_bukrs  AND
               gjahr = p_gjahr  AND
               belnr = wa_voucher1-belnr.

       IF sy-subrc <> 0.
* If the corresponding entry not found in GL open, look in the GL Closed
      SELECT SINGLE dmbtr FROM bsas INTO  w_IntIncomeAmt  WHERE
           hkont IN ( select fglacc from zcashflow_matrix WHERE FCODE =
'XX002' ) AND bukrs = p_bukrs  AND
              gjahr = p_gjahr  AND
              belnr = wa_voucher1-belnr.

            IF sy-subrc = 0.
              w_IntIncome = w_IntIncome + w_IntIncomeAmt.
              continue.
            ENDIF.
       ELSE.
             w_IntIncome = w_IntIncome + w_IntIncomeAmt.
             continue.
       ENDIF.

************************************************************************

Re: Performance Issue

Former Member — Fri, 25 Aug 2006 05:15:57 GMT

avoid using SELECT statements in the loop

to avoid that , first select all the entries into one internal table and then read this internal table in the loop

and try to avoid INTO CORRESPONDING FIELDS also

Re: Performance Issue

Former Member — Fri, 25 Aug 2006 05:20:08 GMT

Try Use following Performance tools to resolve your problem..

1)SqlTrace(ST05)

2)Tips and trick tool

3)ABAP Runtime Analysis(SE30)

4)Code Inspector

If helpful Reward Points

Regards

Re: Performance Issue

Former Member — Fri, 25 Aug 2006 05:24:42 GMT

Hi Thanura,

Do not use SUM GROUP BY in select statement when your WHERE condition has many fields and you do have primary key fields. Try to get maximum key fields if possible.

In stead use SELECT INTO TABLE AND then do SUM.

Do not use SELECT statement in LOOP.. ENDLOOP of internal table. Sort your itab on fields which will be used in WHERE CONDITION. Use SELECT .. FOR ALL ENTRIES IN ITAB WHERE fld1 = ITAB-fld1 ..

After getting reuqired data in internal table use loop.. endloop and read table with key.. bainary search. For Binary search you need to sort the tables. Read more in ABAP help.

Hope this will give you pointers to improved performance.

Please reward valuable replies.

Regards

Sunil

Re: Performance Issue

former_member480923 — Fri, 25 Aug 2006 05:37:09 GMT

Some issus in the code:

a) avoid select inside loops

b) avoid using group by command and usm command while selecting, this you can do inside the code once you have finished will all the slect statement

b) try to use the full index for the tables

Hope this Helps

Anirban

Re: Performance Issue

Former Member — Fri, 25 Aug 2006 07:22:20 GMT

hi All,

Tks for the immidiate responce.

I have used the select statement in the loop because, i want to get the GL entry for that particular invoice.

Pls let me know a method how to avoid that select statement for the BSIS table from inside the loop.

As u all said is correct, if I use the select statement inside a loop , takes lot of time to execute.

Pls let me know a method to avoid that BSIS selection from the loop

Regards

Thanura

Re: Performance Issue

Former Member — Fri, 25 Aug 2006 09:10:11 GMT

I would suggest you try the below ...

Basically separate your subquery ...so before the loop you can do the following..

select fglacc into ifglacc from zcashflow_matrix WHERE FCODE = 'XX001'.

LOOP AT it_voucher1 INTO wa_voucher1.

Find the corresponding entry in the GL open

SELECT SINGLE hkont dmbtr FROM bsis INTO w_hkont w_Rcptamt WHERE

bukrs = p_bukrs AND

gjahr = p_gjahr AND

belnr = wa_voucher1-belnr.

read table ifglacc with key fglacc = w_hkont.

if sy-subrc = 0.

else.

---your other logic....

endif.

ENDLOOP.

Message was edited by: Anurag Bankley

Re: Performance Issue

Former Member — Fri, 25 Aug 2006 09:26:34 GMT

SELECT SUM( dmbtr ) AS dmbtr belnr kunnr hkont shkzg xblnr budat
blart bldat
   INTO TABLE it_voucher3
   FROM  bsad
   WHERE   bukrs = p_bukrs AND 
           gjahr = p_gjahr AND
           augdt IN s_bldat AND 
            blart = 'DZ'.
if sy-subrc = 0.
  SORT it_voucher3 kunnr belnr 
                   hkont shkzg 
                   xblnr budat 
                    blart bldat .
endif.

for this first SELECT from BSAD, i have few suggestions,

1. do not use INTO CORRESPONDING FIELDS in the SELECT. use the fields the order you define the internal table. else the system has to match each source and target fields if you use MOVE CORRESPONDING.

2. DO not use GROUP BY , instead get all the data to your internal table and then use SORT with the fields, the way how you want.

instead of writing SELECT in the loop to fill it_voucher1 table ,use FOR ALL ENTRIES out side the loop and either use LOOP and ENDLOOP of it_voucher1 or use READ TABLE it_voucher1.

as below

SELECT SUM( dmbtr ) 
 AS dmbtr belnr kunnr hkont shkzg xblnr budat
    blart bldat
     INTO TABLE it_voucher1
     FROM  bsad
     FOR ALL ENTRIES OF <b>it_voucher3</b> 
     WHERE   bukrs = p_bukrs AND gjahr = p_gjahr
             AND augdt IN s_bldat
             AND blart <> 'DZ' AND
             augbl = it_voucher3-belnr AND
             kunnr = it_voucher3-kunnr.
 Then use SORT 
  if sy-subrc = 0. 
   sort it_voucher1 BY belnr kunnr hkont shkzg xblnr budat blart bldat.
 endif.

but remember one thing if you are using FOR ALL ENTRIES, if the table you use with FOR ALL ENTRIES (in your case

it_voucher3) is EMPTY, then SELECT will FETCH ALL THE RECORDS. so first you check this it_voucher3 should not be empty.

<b>

LOOP AT it_voucher3 INTO wa_voucher3.

 LOOP AT it_voucher1 INTO wa_voucher1 where augbl = wa_voucher3-belnr 
                                            kunnr = wa_voucher3-kunnr.

  like this you can avoid SELECT in LOOPs and also you can use READ TABLE instead
 ENDLOOP.

</b>

Regards

srikanth

Message was edited by: Srikanth Kidambi