how to avoid embedded loops?

[Former Member]

Hi All,

I am not an expert and dealing with a performance issue. When a debugged the code I figured that a embedded loop was causing the performance issue and I am looking for analternative way to write that piece of the code.

The code is:

loop at SOURCE_PACKAGE assigning <source_fields>.

loop at i_t_d032 into i_l_d032 where

/bic/zfield1 = <source_fields>-/bic/zfield1 and

/bic/zfield2 = <source_fields>-/bic/zfield2 and

/bic/zfield3 = <source_fields>-/bic/zfield3 and

/bic/zfield4 <= <source_fields>-/bic/zfield4 .

delete SOURCE_PACKAGE.

exit.

endloop.

endloop.

FYI - I_t_d032 has 30 million records.

I apprciate your help.

[former_member213275]

Hi,

First check that i_t_d032 is fetched only once for entire LUW ( per one instance) i.e. the same select query to fill this internal table i_t_d032 shouldnt be repeated for every data package -source package for a particuklar load.

Second Declare i_t_d032 type sorted table with non unique key as /bic/zfield1 /bic/zfield2 /bic/zfield3 /bic/zfield4.

Then instead of loop you use read table with table key. This might improve 30 % of time taken usually without sorted table.

loop at SOURCE_PACKAGE assigning <source_fields>.

Read table i_t_d032 into i_l_d032 with table key /bic/zfield1 = <source_fields>-/bic/zfield1

/bic/zfield2 = <source_fields>-/bic/zfield2

/bic/zfield3 = <source_fields>-/bic/zfield3

/bic/zfield4 <= <source_fields>-/bic/zfield4 .

If sy-subrc eq 0.

delete SOURCE_PACKAGE.

endif.

endloop.

Srikanth.

[Former Member]

Thanks for your suggestion. To answer your question I am including the select statement tht fills he itab. And yes it looks like every package is filling the itab.

IF SOURCE_PACKAGE IS NOT INITIAL.

SELECT /BIC/zfield1

/BIC/zfield2

/BIC/zfield3

/BIC/zfield4

FROM /BIC/AZXX_D03200 INTO CORRESPONDING FIELDS OF

TABLE i_t_d032

FOR ALL ENTRIES IN SOURCE_PACKAGE WHERE

/BIC/Zfield1 = SOURCE_PACKAGE-/BIC/zfield1

AND

/BIC/Zfield2 = SOURCE_PACKAGE-/BIC/Zfield2

AND

/BIC/zfield3 = SOURCE_PACKAGE-/BIC/Zfield3.

my question is if I fill he internal table from the active table at once, wouldn't hit th memory? We will be keeping 30 million records in memeory

[Former Member]

check this way :

sort lt_table1.
sort lt_table2.

loop at lt_table1 into wa_table1.
  read table lt_table2 transporting no fields with key field = wa_table1-field binary search.
  if sy-subrc EQ 0
    l_tabix = sy-tabix.
  end if.

  loop at lt_table2 into wa_table2 from l_tabix.  " loop using index is much faster than using where conditions
    if  wa_table2-field NE wa_table1-field.  "exit condition
      exit. 
    end if.
  endloop.
endloop.

for more information read about parallel cursors...

regards sebastiá

[Former Member]

thank you I will try this too!

I appreciate your time

[ravi_lanjewar]

sebastiu00E1n Wrote:

for more information read about parallel cursors...

I am totally disagree with you. Parallel cursor is old technique. You have to used sorted table insted of parallel cursor.

You can define internal table lt_table2 as sorted table. It will avoid first read statement inside the loop or index handlling overhead.

For more details read Link:[sorted table|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/7247] [original link is broken] [original link is broken] [original link is broken];.

Kind Rgds

Ravi Lanjewar

[former_member194613]

Please make clear, what you actually want!

Where is the SELECT, just above the LOOP? So the SELECT fills everything and then a lot must be deleted.

Make that clear.

What is this DELETE supposed to do?

loop at i_t_d032 into i_l_d032 where
/bic/zfield1 = <source_fields>-/bic/zfield1 and
/bic/zfield2 = <source_fields>-/bic/zfield2 and
/bic/zfield3 = <source_fields>-/bic/zfield3 and
/bic/zfield4 <= <source_fields>-/bic/zfield4 .
delete SOURCE_PACKAGE.
exit.
endloop.

People recommending SORTED tables should be aware that it will not work as there is the '<=' condition.

Overall, I guess that there is solution but not in the way proposed. You must start completely different!

[SuhaSaha]

People recommending SORTED tables should be aware that it will not work as there is the '<=' condition

Hello Siegfried,

As per the SAP documentation:

To enable the logical expression of the WHERE condition to be mapped to a key access, the expression must use (in the case of sorted key accesses) AND comparisons with the operator = (or EQ) to cover an initial part of the key consisting of at least one component. An AND association with further comparisons is possible. Optimization may not be possible if the boolean operator NOT is used. 

Source: [http://help.sap.com/abapdocu_702/en/abenitab_where_optimization.htm]

So if i define the table i_t_d032 as SORTED with key fields as /bic/zfield1, /bic/zfield2, /bic/zfield3, then the LOOP ... WHERE will be optimised even if the there is a '<=' condition?

Please correct me if my understanding is incorrect.

BR,

Suhas

[former_member192616]

Hi Suhas,

at least in releases < 7.02 this was not the case. I haven't follow up to 7.02 yet.

It might have been optimized... .

Kind regards,

Hermann

[SuhaSaha]

Hello Hermann,

SAP documentation from Release 701:

While with standard tables all rows of the internal table are checked for the logical expression of the WHERE- addition, with sorted tables and hash tables (as of Release 7.0) you can achieve optimized access by checking that at least the beginning part of the table key in sorted tables and the entire table key in hash tables is equal in the logical expression through queries linked with AND. Optimization also takes effect if the logical expression contains other queries linked with AND with arbitrary operators. 

I have tried this in one of my programs. I had used all the KEYs and addition to that i had used a couple of non-KEY fields with '<=' & '>=' operators.

The Code Inspector had not given warning regd. sequential reads on SORTED TABLES either!

BR,

Suhas

[former_member192616]

Hi Suhas,

i again checked some docs and found:

A LOOP WHERE on sorted tables is optimized if

- All conditions are linked with an AND

- No parentheses are used

- At least one condition has the form k = v

- At least a leading part of the table key is covered by conditions of the form k1 = v1 ... kn = vn.

- none of the conditions entered is negated (NOT <field> <operator> <value>) (Note that you can use the 'NE' / '<>' operator!)

which is in other words what the docu says.

If i remember right it was not always like this (maybe in 6.40 4.6C then....). Your tests showed

that it was optimized i guess.

So coming back to the topic: The DELETE WHERE should be optimized by the kernal on a sorted table no matter if we have 3 or 4 key fields or the <= condition on a field that is not on the leading part of the key.

Kind regards,

Hermann

Edited by: Hermann Gahm on May 26, 2011 10:42 AM