topic Re: Better alternative for the following code... in Application Development and Automation Discussions

Better alternative for the following code...

aris_hidalgo — Fri, 01 Jun 2007 02:24:39 GMT

Hello Experts,

I am currently debugging a report which is in PRD server and users complain that

it always result in time limit error. I am currently checking the report and I found some

code that I think can be optimized. Below is one of the code:


DATA: lr_vbeln             LIKE RANGE OF wa_vbfa-vbeln,
        la_vbeln             LIKE LINE OF lr_vbeln.

* consolidate all document number for selection criteria
* billing documents
  LOOP AT it_vbrk INTO wa_vbrk.
    la_vbeln-sign   = 'I'.
    la_vbeln-option = 'EQ'.
    la_vbeln-low    = wa_vbrk-vbeln.
    la_vbeln-high   = space.

    COLLECT la_vbeln INTO lr_vbeln.

  ENDLOOP.

* delivery documents
  LOOP AT it_likp INTO wa_likp.
    la_vbeln-sign   = 'I'.
    la_vbeln-option = 'EQ'.
    la_vbeln-low    = wa_likp-vbeln.
    la_vbeln-high   = space.

    COLLECT la_vbeln INTO lr_vbeln.

  ENDLOOP.

  IF NOT lr_vbeln[] IS INITIAL.
*   get on the document flow for the corresponding billing and
*   delivery document to get corresponding sales orders
    SELECT vbelv posnv vbeln posnn vbtyp_n vbtyp_v
      INTO CORRESPONDING FIELDS OF TABLE it_vbfa
      FROM vbfa
     FOR ALL ENTRIES IN lr_vbeln
     WHERE vbeln EQ lr_vbeln-low
*     WHERE vbeln IN lr_vbeln-low
       AND vbtyp_n IN ('M', 'J')                "billing/delivery
       AND vbtyp_v EQ 'C'.                      "order

    SELECT vbelv posnv vbeln posnn vbtyp_n vbtyp_v
      APPENDING CORRESPONDING FIELDS OF TABLE it_vbfa
      FROM vbfa
     FOR ALL ENTRIES IN lr_vbeln
     WHERE vbeln EQ lr_vbeln-low
*     WHERE vbeln IN lr_vbeln-low
       AND vbtyp_n IN ('T', 'O')                "returns billing/delivery
       AND vbtyp_v EQ 'H'.                      "returns

Please help me optimize the code above. Thank you guys and take care!

Re: Better alternative for the following code...

Former Member — Fri, 01 Jun 2007 02:31:10 GMT

if it retrieves too much data, i suggest you put in a batch process..or put COMMIT WORK in every select statement..to free the memory and make the retrieval faster

Message was edited by:

leonard chomi

Re: Better alternative for the following code...

Former Member — Fri, 01 Jun 2007 02:40:54 GMT

I beleive no need to use collect for the range tables as there are no amount fields

change to this

DATA: lr_vbeln LIKE RANGE OF wa_vbfa-vbeln,

la_vbeln LIKE LINE OF lr_vbeln.

consolidate all document number for selection criteria
billing documents

LOOP AT it_vbrk INTO wa_vbrk.

la_vbeln-sign = 'I'.

la_vbeln-option = 'EQ'.

la_vbeln-low = wa_vbrk-vbeln.

la_vbeln-high = space.

<b> APPEND la_vbeln INTO lr_vbeln.</b>

ENDLOOP.

delivery documents

LOOP AT it_likp INTO wa_likp.

la_vbeln-sign = 'I'.

la_vbeln-option = 'EQ'.

la_vbeln-low = wa_likp-vbeln.

la_vbeln-high = space.

<b>APPEND la_vbeln INTO lr_vbeln.</b>

ENDLOOP.

IF NOT lr_vbeln[] IS INITIAL.

get on the document flow for the corresponding billing and
delivery document to get corresponding sales orders

SELECT vbelv posnv vbeln posnn vbtyp_n vbtyp_v

INTO CORRESPONDING FIELDS OF TABLE it_vbfa

FROM vbfa

FOR ALL ENTRIES IN lr_vbeln

WHERE vbeln EQ lr_vbeln-low

WHERE vbeln IN lr_vbeln-low

AND vbtyp_n IN ('M', 'J') "billing/delivery

AND vbtyp_v EQ 'C'. "order

SELECT vbelv posnv vbeln posnn vbtyp_n vbtyp_v

APPENDING CORRESPONDING FIELDS OF TABLE it_vbfa

FROM vbfa

FOR ALL ENTRIES IN lr_vbeln

WHERE vbeln EQ lr_vbeln-low

WHERE vbeln IN lr_vbeln-low

AND vbtyp_n IN ('T', 'O') "returns billing/delivery

AND vbtyp_v EQ 'H'. "returns

ENDIF.

--> Better remove INTO CORRESPONDING FIELDS OF

--> when you are equating vbeln EQ lr_vbeln-low , then there is no need to build a range table , instead just declare one internal table with field vbeln , and append the values to it , and then use in FOR ALL ENTRIES

Re: Better alternative for the following code...

Former Member — Fri, 01 Jun 2007 02:51:00 GMT

Just check the code:

DATA: LT_VBRK LIKE TABLE OF IT_VBRK,

LT_LIKP LIKE TABLE OF IT_LIKP.

consolidate all document number for selection criteria
billing documents

LT_VBRK[] = IT_VBRK[].

Sort lt_vbrk by vbeln.

delete adjacent duplicates from lt_vbrk comparing vbeln.

LT_LIKP[] = IT_LIKP[].

Sort lt_likp by vbeln.

delete adjacent duplicates from lt_likp comparing vbeln.

get on the document flow for the corresponding billing and
delivery document to get corresponding sales orders

SELECT vbelv posnv vbeln posnn vbtyp_n vbtyp_v

INTO CORRESPONDING FIELDS OF TABLE it_vbfa

FROM vbfa

FOR ALL ENTRIES IN LT_VBRK

WHERE vbeln EQ LT_VBRK-VBELN

AND vbtyp_n IN ('M', 'J') "billing/delivery

AND vbtyp_v EQ 'C'. "order

***Call FM SAPGUI_PROGRESS_INDICATOR, This will reset the time and will not give time limit error.

SELECT vbelv posnv vbeln posnn vbtyp_n vbtyp_v

APPENDING CORRESPONDING FIELDS OF TABLE it_vbfa

FROM vbfa

FOR ALL ENTRIES IN LT_LIKP

WHERE vbeln EQ LT_LIKP-VBELN

AND vbtyp_n IN ('T', 'O') "returns billing/delivery

AND vbtyp_v EQ 'H'. "returns

1) Looping takes considerable amount of time and this will help in optimizing to large extent.

2) If possible declare it_vbfa with fields vbelv posnv vbeln posnn vbtyp_n vbtyp_v in the same order and remove corresponding fields of.

Regards

Navneet

Message was edited by:

Navneet Saraogi

Re: Better alternative for the following code...

Former Member — Fri, 01 Jun 2007 02:57:02 GMT

Hi Viraylab ,

Here are few of my observations

1. Do not use into corresponding feilds of table , design your IT such that feilds are in the order you select from the database.

2. Combine the two SQL into 1 statement

    SELECT vbelv posnv vbeln posnn vbtyp_n vbtyp_v
      into TABLE it_vbfa
      FROM vbfa
     FOR ALL ENTRIES IN lr_vbeln
     WHERE vbeln IN lr_vbeln-low  " Check Change
       AND vbtyp_n IN ('T', 'O' , 'M', 'J' )                "returns billing/delivery
       AND vbtyp_v in ( 'H' , 'C' 0.

Hope this helps.

Regards

Arun