topic Re: Need help on optimizing code... in Application Development and Automation Discussions

Need help on optimizing code...

aris_hidalgo — Wed, 25 Apr 2007 05:27:35 GMT

Hello Experts,

We are having a hard time optimizaing a certain report wherein it gives

us time limit error in our prod server. The part of the report where it

really slows down is when it gets records from tables VBAK and VBAP using

inner join then looping it and inside the loop it gets a single record

from table VBFA where VBELV = VBELN of the current loop. the internal table

that holds the records from VBAK and VBAP(lt_vbap) holds almost a million records

and going through VBFA table every loop is very time consuming since it is also

a very big table. What I did before is to remove the select single from vbfa statement

inside the loop and used FOR ALL ENTRIES statement instead but instead of time limit error,

it gives lack of memory error. Now, I don't know what else to do so I really need help here.

Anyway, below is the code:


LOOP AT lt_vbap ASSIGNING <wa_vbap>.
    IF <wa_vbap>-auart EQ 'ZUCI' OR
       <wa_vbap>-auart EQ 'ZURV' OR
       <wa_vbap>-auart EQ 'ZUPR'.
*   Check ifa delivery document has been created
      CLEAR wa_vbill.
      SELECT SINGLE vbeln
        FROM vbfa
        INTO wa_vbill
       WHERE vbelv = <wa_vbap>-vbeln
         AND ( ( vbtyp_n EQ 'J' AND vbtyp_v = 'C' )  "Delivery/Order
          OR ( vbtyp_n EQ 'T' AND vbtyp_v = 'H' ) ). "Returns delivery for order/Returns

      IF sy-subrc <> 0.
        <wa_vbap>-del_ind = 'X'.
        CONTINUE.
      ELSE.
*       Check if within selection parameters
        CLEAR wa_likp.
        READ TABLE lt_likp INTO wa_likp WITH KEY vbeln = wa_vbill.
        IF sy-subrc <> 0.
          SELECT SINGLE * FROM likp INTO wa_likp WHERE vbeln = wa_vbill.
          APPEND wa_likp TO lt_likp.
        ENDIF.

        IF NOT wa_likp-wadat_ist IN s_erdat. "Actual goods movement date
          <wa_vbap>-del_ind = 'X'.
          CONTINUE.
        ENDIF.
      ENDIF.
    ELSE.
*   Check if this has already been billed
      CLEAR wa_vbill.
      SELECT SINGLE vbeln
        FROM vbfa
        INTO wa_vbill
       WHERE vbelv = <wa_vbap>-vbeln
         AND ( ( vbtyp_n EQ 'M' AND vbtyp_v = 'C' )   "Invoice/Order
          OR ( vbtyp_n EQ 'O' AND vbtyp_v = 'H' ) ).  "Credit memo/Returns

      IF sy-subrc <> 0.
        <wa_vbap>-del_ind = 'X'.
        CONTINUE.
      ELSE.
*       Check if within selection parameters
        CLEAR wa_vbrk.
        READ TABLE lt_vbrk INTO wa_vbrk WITH KEY vbeln = wa_vbill.
        IF sy-subrc <> 0.
          SELECT SINGLE vbeln fkdat
          FROM vbrk
          INTO wa_vbrk
          WHERE vbeln = wa_vbill.
          APPEND wa_vbrk TO lt_vbrk.
        ENDIF.

        IF NOT wa_vbrk-fkdat IN s_erdat.
          <wa_vbap>-del_ind = 'X'.
          CONTINUE.
        ENDIF.
      ENDIF.
    ENDIF.
  ..............
     ....................

Re: Need help on optimizing code...

Former Member — Wed, 25 Apr 2007 05:34:52 GMT

dont use select stmt in b/w loop-endloop.

<b>before doing any changes in this report , first get the DB cost of each and every table from ST05.

2.Find out no.of DB accesses.

3.No. of Duplicate records.</b>

Message was edited by:

Prabhu Peram

Re: Need help on optimizing code...

Former Member — Wed, 25 Apr 2007 05:51:02 GMT

Write the Select statements for both VBFA and VBRK outside of the loop using for all entries of VBAK and VBAP records into internal tables and read those internal tables in this loop...endloop.

reward if useful

regards,

ANJI

Re: Need help on optimizing code...

aris_hidalgo — Wed, 25 Apr 2007 05:58:19 GMT

Thank you guys for your replies. I tried using FOR ALL ENTRIES statement but it gives us lack of memory space in prod server. Any suggestions? Thanks!

Re: Need help on optimizing code...

Former Member — Wed, 25 Apr 2007 06:02:40 GMT

hi,

dont use select within the loop. for every record in the loop, it checks the where condition from the entire vbfa table which inturn affects performance. instead select into the internal tables before entering the loop and then use the read statement.

Re: Need help on optimizing code...

Former Member — Wed, 25 Apr 2007 06:02:44 GMT

Hi Viraylab,

Special care should be taken while using the VBFA table . It is

better to query the VBFA table using Function Modules .

Please check in VBFA for FMs Read*VBFA

I think we have got a FM read_vbfa*vbeln

Re: Need help on optimizing code...

Former Member — Wed, 25 Apr 2007 06:18:41 GMT

Hi,

Try using these FM's and let us know if it works for you.

FM to read from VBFA table.

SD_VBFA_ARRAY_READ_VBELV

SD_VBFA_READ_WITH_VBELV

SD_VBFA_SELECT

SD_VBFA_SINGLE_READ

SD_VBFA_TRANSFER_TO_BUFFER

Thanks and Regards,

Varun.