nested loop

[Former Member]

HI Experts,

i have nested loop first loop contails 16 rec and 2nd contain 12 if i check the perfromance database perfromance is 6 and abap is appl'on performace 96 how can i increase the perfromance .

plz help me out .

LOOP AT gtbl_vbrk INTO wa_vbrk.
    LOOP AT gtbl_vbrp INTO wa_vbrp. " WHERE vbeln = wa_vbrk-vbeln.
      READ TABLE gtbl_vbrp INTO wa_vbrp WITH KEY vbeln = wa_vbrk-vbeln BINARY SEARCH. " Begin of modif by fc0027 4/05/2011.
      gv_fkimg = gv_fkimg + wa_vbrp-fkimg.
      gv_vrkme = wa_vbrp-vrkme.
      gv_ntgew = gv_ntgew + wa_vbrp-ntgew.
      gv_gewei = wa_vbrp-gewei.
*      gv_brgew  = gv_brgew  + wa_vbrp-brgew.
      CLEAR wa_vbrp.

    ENDLOOP.

    SELECT SINGLE netwr waerk kunag kurrf INTO (gv_netwr,gv_waerk, gv_kunnr, gv_kurrf)
                                    FROM vbrk WHERE  vbeln = wa_vbrk-vbeln."aubel.
    SELECT SINGLE name1 land1 INTO (gv_name, gv_land1) FROM kna1 WHERE kunnr = gv_kunnr.
    SELECT SINGLE landx INTO wa_final-landx FROM t005t WHERE land1 = gv_land1 AND spras = 'E'.
    SELECT SINGLE vtext INTO wa_final-vtext FROM tvzbt WHERE zterm = wa_vbrk-zterm.
    SELECT SINGLE belnr INTO wa_final-belnr FROM bkpf WHERE xblnr = wa_vbrk-vbeln AND blart = 'RV'.
    SELECT SINGLE prctr INTO wa_final-prctr FROM vbrp WHERE vbeln = wa_vbrk-vbeln.
    SELECT SINGLE exnum INTO wa_final-exnum FROM j_1iexchdr WHERE rdoc = wa_vbrk-vbeln.


    DO 5 TIMES.
      CLEAR t_tline.
      CLEAR p_textid.
      CLEAR a.
      FREE t_tline.
      CASE sy-index.
        WHEN 1.
          p_textid = 'Z004'.
        WHEN 2.
          p_textid = 'Z0D1'.
        WHEN 3.
          p_textid = 'Z029'.
        WHEN 4.
          p_textid = 'Z056'.
        WHEN 5.
          p_textid = 'ZB13'.

      ENDCASE.

      a = sy-index.
      w_test = wa_vbrk-vbeln.

      CALL FUNCTION 'READ_TEXT'
        EXPORTING
          client                  = sy-mandt
          id                      = p_textid
          language                = sy-langu
          name                    = w_test
          object                  = 'VBBK'
        TABLES
          lines                   = t_tline
        EXCEPTIONS
          id                      = 1
          language                = 2
          name                    = 3
          not_found               = 4
          object                  = 5
          reference_check         = 6
          wrong_access_to_archive = 7
          OTHERS                  = 8.

      cnt = 0 .
      LOOP AT t_tline WHERE tdline <> ' '..
        CASE a.
          WHEN 1.
            MOVE t_tline-tdline TO wa_final-port.
          WHEN 2.
            MOVE t_tline-tdline TO wa_final-contnr_no.
          WHEN 3.
            MOVE t_tline-tdline TO wa_final-desp_dat.
          WHEN 4.
            MOVE t_tline-tdline TO wa_final-contnr_type.
          WHEN 5.
            MOVE t_tline-tdline TO wa_final-brgew.
        ENDCASE.
      ENDLOOP.
    ENDDO.

    wa_final-vbeln = wa_vbrk-vbeln.
    wa_final-spart = wa_vbrk-spart.
    wa_final-fkdat = wa_vbrk-fkdat.
    wa_final-inco1 = wa_vbrk-inco1.
    wa_final-cust_name = gv_name.
    wa_final-fkimg = gv_fkimg.
    wa_final-vrkme = gv_vrkme.
    wa_final-netwr = gv_netwr.
    wa_final-waerk = gv_waerk.
    wa_final-srno  = gv_srno.
    wa_final-ntgew = gv_ntgew.
*    wa_final-brgew = gv_brgew.
    wa_final-fkart = wa_vbrk-fkart.
    wa_final-gewei = gv_gewei.
    wa_final-aminr = gv_netwr * gv_kurrf.

*Get the distribution channel
    wa_final-vtweg = wa_vbrk-vtweg.

*Calculate net wt. in Tonnes
    DATA: lv_tonne  TYPE ehswae_eaintam,
          lv_ntgew  TYPE ehswae_eaintam,
          lv_gewei  TYPE ehswae_eaintamu.
    CLEAR: lv_tonne, lv_ntgew.

    IF gv_vrkme NE 'EA'.
      lv_ntgew = wa_final-fkimg.
      lv_gewei = wa_final-vrkme.
    ELSE.
      lv_ntgew = wa_final-ntgew.
      lv_gewei = wa_final-gewei.
    ENDIF.

    CASE lv_gewei.
      WHEN 'G'.
        lv_tonne = lv_ntgew / 1000000.
      WHEN 'KG'.
        lv_tonne = lv_ntgew / 1000.
      WHEN 'LB'.
        lv_tonne = lv_ntgew / 2204.
      WHEN 'TO'.
        lv_tonne = lv_ntgew.
    ENDCASE.

    wa_final-tonne = lv_tonne. "Net Weight in Tonnes

    APPEND wa_final TO gtbl_final.
    CLEAR: wa_final,wa_vbrk,gv_fkimg,gv_vrkme,gv_netwr,gv_waerk, gv_name,
           gv_land1, gv_ntgew, gv_gewei.
*     CLEAR: gv_brgew.
    gv_srno = gv_srno + 1.
  ENDLOOP.

Edited by: Matt on May 9, 2011 9:05 AM - added tags

[kesavadas_thekkillath]

Please read the threads & blogs by Siegfred Boes regarding Sorted Internal tables. In SCN you will find many.

[Former Member]

My first question would be regarding the following bit.

LOOP AT gtbl_vbrp INTO wa_vbrp. " WHERE vbeln = wa_vbrk-vbeln.

READ TABLE gtbl_vbrp INTO wa_vbrp WITH KEY vbeln = wa_vbrk-vbeln BINARY SEARCH. " Begin of modif by fc0027 4/05/2011.

It looks to me like you're trying to use a read statement to replace the original loop statement. The read statement will not necessarily give the same result as the loop.

Regarding performance, have you done an SQL trace on it yet? I suspect that one of the delays is with

SELECT SINGLE belnr INTO wa_final-belnr FROM bkpf WHERE xblnr = wa_vbrk-vbeln AND blart = 'RV'.

Here you are searching a potentially massive table with a non-indexed field. A possible solution would be to collect extract data into an internal table of XBLNR, BELNR using a FOR ALL ENTRIES select statement before the initial loop and then read this table within the loop. With the number of records you have stated this would reduce the DB calls from 16 to 1 for this slow query.

The other probable delay is the time taken for the READ TEXT function. There's not a lot you can do about this.

Hope this helps.

John

[Former Member]

1. Use sorted tables. If not, the take your read statement out of your loop and place it before the second loop (second table has to be sorted based on read condition). Now, loop through second table based on sy-tabix of first table. If not this, then try summarizing your data in second table before the first table loop is called so that you can make a read on a key (Control commands).

2. Try eliminating the select in loop. See if you can use Innerjoins/for all entries at once.

3. There are many places where more optimization can be done as well. Use While condition as well instead of do or see your do statement may not end in an infinite loop for any condition.

[Former Member]

Hi,

You can modify your code as mentioned below.

SORT gtbl_vbrp ASCENDING BY vbeln.

SELECT from tables T005T,TVZBT,BKPF,VBRP using FOR ALL ENTRIES of VBRK.
LOOP AT gtbl_vbrk INTO wa_vbrk.
READ TABLE gtbl_vbrp WITH KEY vbeln = wa_vbrk-vbeln
BINARY SEARCH TRANSPORTING NO FIELDS.
 IF sy-subrc = 0.
   LOOP AT gtbl_vbrp INTO wa_vbrp FROM sy-tabix.
    IF wa_vbrp-vbeln <> wa_vbrk-vbeln.
       EXIT.
    ENDIF.

gv_fkimg = gv_fkimg + wa_vbrp-fkimg.
gv_vrkme = wa_vbrp-vrkme.
gv_ntgew = gv_ntgew + wa_vbrp-ntgew.
gv_gewei = wa_vbrp-gewei.
* gv_brgew = gv_brgew + wa_vbrp-brgew.
CLEAR wa_vbrp.

   ENDLOOP.
 ENDIF.

Read each table( T005T,TVZBT,BKPF,VBRP) with the corresponding fields of VBRK.

Rest of the code is fine for performnace.

[ravi_lanjewar]

Hi,

Seems to be soution of your problem.

1) Declare internal table gtbl_vbrp as sorted table with non-unique key vbeln.

2) Don't used parallel cusor logic while reading data in nested Internal table( inner internal table ).

    loop at vbrk (outer loop)
      "Avoid Parallel cursor logic here
      read table vbrp with key vbeln  
      loop at vbrk (inner loop) 
        if value check condition to exit from loop 
         exit
        endif.
      endloop.
    endloop.
   

3) While declaring the internal table type used the required fields only, Don't delare like data: it_vbrk type table of vbrk .

It is taking more time to transfer data from internal table to work area depend on size of structure.

4) Used fields symbols for internal table of vbrk and vbrp

5) while reading data inside the loop used buffering login to avoid the identical read. ( In you program identical read problem is persist.) Avoid it using following logic. You can do using 2 different logic.

You read data from relavent table using the for all entries before the loop and used read statement inside loop.

OR

Buffering logic- Declare internal table as hased table with key fields which you want to put the where condition to read data.

Inside loop read data from interrnal table first if it is not exit in internal table then read from database table and insert into internal table and assign your value. At time of next read similar entry exit in intenal table then is does not go to database and read first from intenal table using this you can avoid the identical read.

 clear: wa_but000. 
 read table it_but000 into wa_but000 with table key partner = vbrk-kunnr.
  if sy-subrc ne 0.
   select (required fields) into wa_but000 
     where partner = vbrk-kunnr.
    if sy-subrc eq 0.
      insert into it_but000 from wa_but000.
    endif.
  endif.
" write you assignment here.

Note: we can further optimize this logic. I am giving just idea.

[Former Member]

Hi,

What I have seen in your code block you have used a select statement in side loop means in each iteration of loop data base hit will happen which can simply avoided using for allentries.

There after you have used a nested loop which also detoriate performance you can bring out the inner loop out side the main loop and put your condition there.

In this way you can achive better performance.

Check key fields are used in sequence in select query. Check the internal table is empty or not before using for all entries.

Thanks,

Ranadev

[Former Member]

Hi,

Avoid SELECT statement inside the loop, Insted use SELECT with FOR ALL ENTRIES before the loop and read the required information from the internal table.

[Former Member]

>Avoid SELECT statement inside the loop, Insted use SELECT with FOR ALL ENTRIES

• This is not generally true! There are also buffered tables inside the loop and for them the SELECT is perfect (you should neither preselect into an internal table nor use buffered tables in joins!*

This is a general remark, but actually your code is different

  SELECT SINGLE name1 land1 INTO (gv_name, gv_land1) 
                  FROM kna1 WHERE kunnr = gv_kunnr.
    SELECT SINGLE landx INTO wa_final-landx FROM t005t 
                  WHERE land1 = gv_land1 AND spras = 'E'.

   SELECT SINGLE netwr waerk kunag kurrf INTO (gv_netwr,gv_waerk, gv_kunnr, gv_kurrf)
                 FROM vbrk WHERE  vbeln = wa_vbrk-vbeln."aubel.
    SELECT SINGLE vtext INTO wa_final-vtext FROM tvzbt 
                  WHERE zterm = wa_vbrk-zterm.
    SELECT SINGLE belnr INTO wa_final-belnr FROM bkpf 
                  WHERE xblnr = wa_vbrk-vbeln AND blart = 'RV'.
    SELECT SINGLE prctr INTO wa_final-prctr FROM vbrp 
                  WHERE vbeln = wa_vbrk-vbeln.
    SELECT SINGLE exnum INTO wa_final-exnum 
                  FROM j_1iexchdr WHERE rdoc = wa_vbrk-vbeln.
 

This first 2 SELECTs are fixed and do not change inside the LOOP therefore they should be executed only once outside.

The others can be combned into one JOIN!

The somewhat weird code with the do-loop should also be checked.

    LOOP AT gtbl_vbrp INTO wa_vbrp. " WHERE vbeln = wa_vbrk-vbeln.
      READ TABLE gtbl_vbrp INTO wa_vbrp WITH KEY vbeln = wa_vbrk-vbeln BINARY SEARCH. " Begin of modif by fc0027 4/05/2011.
      gv_fkimg = gv_fkimg + wa_vbrp-fkimg.
      gv_vrkme = wa_vbrp-vrkme.
      gv_ntgew = gv_ntgew + wa_vbrp-ntgew.
      gv_gewei = wa_vbrp-gewei.
*      gv_brgew  = gv_brgew  + wa_vbrp-brgew.
      CLEAR wa_vbrp.

This is very poor coding, the LOOP can not be repleace by a READ only and here it is not even replaced but double.

And I wonder whether you really need global variables in a LOOP.