Performance issue

[Former Member]

I have some performance issue. I wanted to remove below loops.

Actually WA_DELIVERY_MASTER is my output table. i do want to append values into it.

• Merging all the delivery related data

LOOP AT li_delivery INTO lwa_delivery.

wa_delivery_master-vbeln = lwa_delivery-vbeln.

wa_delivery_master-ernam = lwa_delivery-ernam.

wa_delivery_master-erdat = lwa_delivery-erdat.

wa_delivery_master-lifsk = lwa_delivery-lifsk.

wa_delivery_master-lfdat = lwa_delivery-lfdat.

wa_delivery_master-lddat = lwa_delivery-lddat.

wa_delivery_master-wadat = lwa_delivery-wadat.

wa_delivery_master-tddat = lwa_delivery-tddat.

wa_delivery_master-route = lwa_delivery-route.

wa_delivery_master-wadat_ist = lwa_delivery-wadat_ist.

wa_delivery_master-vgbel = lwa_delivery-vgbel.

wa_delivery_master-vgpos = lwa_delivery-vgpos.

wa_delivery_master-posnr = lwa_delivery-posnr.

wa_delivery_master-lfimg = lwa_delivery-lfimg.

wa_delivery_master-mbdat = lwa_delivery-mbdat.

wa_delivery_master-kostk = lwa_delivery-kostk.

wa_delivery_master-wbstk = lwa_delivery-wbstk.

wa_delivery_master-pkstk = lwa_delivery-pkstk.

lv_count = 0.

• Merging Shipment data

LOOP AT li_shipment INTO lwa_shipment

WHERE vbeln = lwa_delivery-vbeln.

wa_delivery_master-tknum = lwa_shipment-tknum.

wa_delivery_master-ernam_vttk = lwa_shipment-ernam.

wa_delivery_master-sttrg = lwa_shipment-sttrg.

wa_delivery_master-vsart = lwa_shipment-vsart.

wa_delivery_master-vsart_desc = lwa_shipment-vsart_desc.

wa_delivery_master-tplst = lwa_shipment-tplst.

wa_delivery_master-tdlnr = lwa_shipment-tdlnr.

wa_delivery_master-dalbg = lwa_shipment-dalbg.

wa_delivery_master-dalen = lwa_shipment-dalen.

wa_delivery_master-datbg = lwa_shipment-datbg.

wa_delivery_master-stlad = lwa_shipment-stlad.

wa_delivery_master-tpnum = lwa_shipment-tpnum.

APPEND wa_delivery_master TO gi_delivery_master.

lv_count = 1.

ENDLOOP.

IF lv_count NE 1.

APPEND wa_delivery_master TO gi_delivery_master.

ENDIF.

CLEAR wa_delivery_master.

ENDLOOP.

I do not want to use 2 loops. It is affecting my performance.

[Former Member]

Hi Rohit,

Instead of inner Loop you can use the Read statement.

READ TABLE li_shipment into lwa_shipment WITH TABLE KEY vbeln = lwa_delivery-vbeln.

Just replace that.

Reply back if you need more help.

Regards

Karthik D

[Former Member]

Karthik - READ won't help at all unless the BINARY SEARCH addition is used. And in this case, it will not return all the results.

Rob

[Former Member]

No we can not use read statement as my each delivery there are more shipment number.

[Former Member]

It will not work in my case as there is a query like

SELECT a_{vbeln a}ernam a_{erdat a}lifsk a~lfdat

a_{lddat a}wadat a_{tddat a}route a~wadat_ist

b_{vgbel b}vgpos b_{vbeln b}posnr b~lfimg

b_{mbdat c}vbeln c_{kostk c}wbstk c~pkstk

INTO TABLE SELECT a_{vbeln a}ernam a_{erdat a}lifsk a~lfdat

a_{lddat a}wadat a_{tddat a}route a~wadat_ist

b_{vgbel b}vgpos b_{vbeln b}posnr b~lfimg

b_{mbdat c}vbeln c_{kostk c}wbstk c~pkstk

INTO TABLE li_delivery

FROM ( ( likp AS a

LEFT OUTER JOIN lips AS b

ON a_{vbeln = b}vbeln )

LEFT OUTER JOIN vbuk AS c

ON a_{vbeln = c}vbeln )

WHERE a~vbeln IN s_vbelnd

AND a~ernam IN s_ernamd

AND a~erdat IN s_erdatd.

FROM ( ( likp AS a

LEFT OUTER JOIN lips AS b

ON a_{vbeln = b}vbeln )

LEFT OUTER JOIN vbuk AS c

ON a_{vbeln = c}vbeln )

WHERE a~vbeln IN s_vbelnd

AND a~ernam IN s_ernamd

AND a~erdat IN s_erdatd.

so each vbeln with this item so there are many rows for each VBELN in my internal table there are more than one rows.

in this case to feth data from

LOOP AT li_shipment INTO lwa_shipment

WHERE vbeln = lwa_delivery-vbeln.

wa_delivery_master-tknum = lwa_shipment-tknum.

wa_delivery_master-ernam_vttk = lwa_shipment-ernam.

wa_delivery_master-sttrg = lwa_shipment-sttrg.

wa_delivery_master-vsart = lwa_shipment-vsart.

endloop.

it will not work.

[ThomasZloch]

Hi,

you can use loop inside loop if you make sure that the WHERE-conditions, especially of the inner loop, are supported by the table keys of sorted internal tables (defined as ... TYPE SORTED TABLE OF ... WITH (NON-)UNIQUE KEY ...

Thomas

P.S. Did not see Siegfried's post before sending mine, he beat me again...

[former_member194613]

just define the second table as a sorted table and your performance is dramatically improved.

Best use always sorted tables if possible.

If it must be a standard table then use binary search ... of coursre togehter with a loop see here

Section on loops.

Without that you run into problems described here

Try move-corresponding, I don't think that is costs much, and if something changes in your structures, it will save you a lot of trouble.

Siegfried

[Former Member]

Hi,

In addition to above suggestions, I would like to add few points. When using nested loops we need to consider the volume of data in each internal tables.

Say ITAB1 has 1 million records and ITAB2 has 1.5 million records, in this case if you may have to use nested loops then loop on ITAB1 (outer loop) and inside the loop, perform loop on ITAB2 with the addition of WHERE clause. Which means in case of nested loops always outer loop should contain less itterations. One more point here I don't see any reason why you are assigning each work area's field values seperately to another work area and append it to internal table. I would use the syntax MOVE-CORRESPONDING WA1 TO WA2 and append WA2 TO ITAB2.

Regards,

Venkat

[Former Member]

>

> Say ITAB1 has 1 million records and ITAB2 has 1.5 million records, in this case if you may have to use nested loops then loop on ITAB1 (outer loop) and inside the loop, perform loop on ITAB2 with the addition of WHERE clause. Which means in case of nested loops always outer loop should contain less itterations. One more point here I don't see any reason why you are assigning each work area's field values seperately to another work area and append it to internal table. I would use the syntax MOVE-CORRESPONDING WA1 TO WA2 and append WA2 TO ITAB2.
> 
> Regards,
> Venkat

Venkat - unfortunately, that's basically wrong on both counts:

LOOP AT...WHERE will still have to read all entries in both tables in order to evaluate the WHERE condition..

MOVE-CORRESPONDING will add some overhead, but not enough to worry about.

Run some tests to see if this is correct.

Rob

[Former Member]

Hi Rob,

I have a question for you, say I have an internal table with records something like this:

ITAB1-FIELD1 ITAB1-FIELD2

1001 XX

1001 YY

1001 ZZ

ITAB2-FIELD1 ITAB2-FIELD2

1001 XYZ

What way of looping would you suggest for this?

1) LOOP AT ITAB1.

LOOP AT ITAB2 WHERE FIELD1 = ITAB1-FIELD1.

• * * * *

ENDLOOP.

ENDLOOP.

2) LOOP AT ITAB2.

LOOP AT ITAB1 WHERE FIELD1 = ITAB2-FIELD2.

• * * * *

ENDLOOP.

ENDLOOP.

In example 1 the no. of itterations including outer and inner loops are 6.

In example 2 the no. of itterations including outer and inner loops are 4.

Regards,

Venkat

[Former Member]

Neither - see:

[Performance of Nested Loops|]

Rob

[P561888]

Venkat:

if you want to use Loop inside a Loop.

I think it is the better way,

Loop at internal table1 into wa_itab1.

v_tabix = sy-tabix.

Loop at internal table2 into wa_itab2 from v_tabix.

Code here.

endloop.itab2

endloop."Itab 1

[Former Member]

>

>  if you want to use Loop inside a Loop.
> I think it is the better way,

Nested loops are a performance killer. See my earlier post.

Rob

[P561888]

Hi Rob,

I will agree that loop inside a loop is the performance killer but the by using the INDEX method we can get better performance .

[Former Member]

>

> Hi Rob,
> I will agree that loop inside a loop is the performance killer but the by using the INDEX method we can get better performance .

Yes, but you have to have an exit when the field you are watching changes.

Rob