LOOP and Inner LOOP

[Former Member]

Is one of these faster than the other?

data: Outer_tab type table of some_type.

data: Inner_tab type table of some_other_type.

LOOP at Outer_Tab into some_struct.

SORT Inner_tab by some_cond some_cond2.

LOOP at Inner_Tab WHERE some_cond = Outer_TAB-some_cond

AND some_comd2 = Outer_Tab-some_cond2.

• some logic here

ENDLOOP. " Inner

ENDLOOP. "Outer

OR

data: Outer_tab type table of some_type.

data: Inner_tab type SORTED table of some_other_type.

LOOP at Outer_Tab into some_struct.

LOOP at Inner_Tab WHERE some_cond = Outer_TAB-some_cond

AND some_comd2 = Outer_Tab-some_cond2.

• some logic here

ENDLOOP. " Inner

ENDLOOP. "Outer

I am curious if the SORTED table prevents a full table access to the INNER_Tab - and if the STANDARD table does not prevent it and it compares ALL records.

Moderator message - Moved to the correct forum

Edited by: Rob Burbank on Sep 25, 2009 2:10 PM

[Former Member]

Hi

If you use the SORTED table you'll improve the performance, but this table has to have the fields used in WHERE condition as fields for sort criteria

Max

[Former Member]

Hi,

Using sorted table i,e the second method is better compared to the first one. By using SORTED TABLE, when ever records are added into the internal table, immediately they will be sorted. This will increase the performance particularly when you use where condition in the loop of the sorted internal table.

Regards,

Vikranth

[Former Member]

Hi,

Using SORT table however increases the performance. When considering with LOOp and Endloop, seems to have the nested loop. Never use loop inside another loop.

loop at outer_table.

Use Read Table inner_table with key field = outer_table-field.

if sy-subrc = 0.

few... required.. steps

endif.

endloop.

Thanks,

Prasad

[Former Member]

Prasad,

Your statement would be correct if I needed one and only one record from the INNER table.

But in my case, there are many records that match the WHERE condition.

Your statement is a good policy for unique record situations.

[Former Member]

Both examples are bad.

You can get all records using only READs.

Additionally, there are other options for the LOOP statement that you can use to increase performance if you don't want sorted or hashed tables.

Rob

Edited by: Rob Burbank on Sep 25, 2009 2:11 PM

[Former Member]

"You can get all records using only READs."

How would I get multiple occurrences where the conditions are the same - meaning 3 record in INNER that have the same key as shown in the WHERE clause?

[Former Member]

Hello Rob,

If there exists a many:many relationship between the two internal tables records, dont you think nested loops are unavoidable? Is there anyway to avoid nested loops in such scenarios since read table will only pick one value. How is it possible with a loop and a read statement alone?

Regards,

Vikranth

[Former Member]

Vik,

Exactly my question.... nicely stated by you. Good job.

[Former Member]

>

> How would I get multiple occurrences where the conditions are the same - meaning 3 record in INNER that have the same key as shown in the WHERE clause?

You read the first occurence using a binary search and then get the next records by increasing the index and getting the next record(s).

See [The Performance of Nested Loops|/people/rob.burbank/blog/2006/02/07/performance-of-nested-loops]. It's kind of dated, but it works.

Rob

[Former Member]

Good one Rob..

We can use the index read instead of nested loop. However i guess it is more useful in case we have expectation of large volume of records.

thanks

Amit.

[Rui_Dantas]

Hi John. In situations like this, instead of being curious you can just read the Help. Contrary to popular belief, it can be quite helpful:

When you use the WHERE condition with a STANDARD or HASHED table, a full table scan is always required. 

If you are working with a SORTED TABLE, the partial sequential processing can be optimized so that only the entries that actually satisfy the WHERE condition are processed. 
This optimization requires that each sub-condition is linked with AND; that no parentheses are used; that at least one sub-condition is in the form k = v; and that the conditions in the form k1 = v1 ... kn = vn cover at least the first part of the table key.

So, your 1st example (standard table) will make a full table scan. Your 2nd example (sorted table) would be optimized (so no nead to replace it by READ), provided you define the key correctly.

If you do use READ then you can use a standard table, because you can force BINARY SEARCH explicitly. However, you must not, of course, make the SORT inside the LOOP as you have in your code:

LOOP at Outer_Tab into some_struct.

  SORT Inner_tab by some_cond some_cond2.         "<< this should go before the LOOP  
      LOOP at Inner_Tab WHERE some_cond = Outer_TAB-some_cond    
                                  AND some_comd2 = Outer_Tab-some_cond2.

[Former Member]

there is a solution with the standard table and the LOOP WHERE which is based on READ BINARY SEARCH LOOP FROM INDEX,

see here

Measurements on internal tables: Reads and Loops:

/people/siegfried.boes/blog/2007/09/12/runtimes-of-reads-and-loops-on-internal-tables

The same thing what is done here manually is done by the SORTED TABLE automatically, but be aware not in all old releases!

*The LOOP with READ is as bad as the Nested Loop if no BINARY SEARCH is used !!! * This is not so hard too understand, but recommended so often.

Of course the SORT MUST be done once, i.e. not inside the LOOP.

In rare cases where the LOOP has condition other than '=', only the manual optimization works, the SORTED table is then not optimized!

Siegfried

[Former Member]

HI,

if your to internal table satisfy the bellow requirements

To use this following should be true

1) Both tables should be sorted with the same key ( in the above case field1 and field2 )

2) The mapping between primary to secondary tables should be

a. one to many ( for one record in primary there are multiple records in secondary )

b. one to one (for one record in primary there is only one in secondary )

Please note that other than the above 2 other 2 scenarios (many to many & many to one) will not work with the above logic and make sure you donu2019t use in such case.

3) The secondary table should not contain a record which does not map to the primary table ( this will not happen if the secondary is fetched using for all entries from primary )

4) Please make sure you do not delete any record from primary before using the logic which will make the point 3 false

5) If you were to use a 'AND' in where statement of the inner loop you should use or in the inner loop if condition in the above logic.

then you can use the

Loop AT i_primary into wa_primary.

Loop AT i_secondary into wa_secondary from lv_index.
If ( wa_primary-field1 NE wa_secondary-field1 ) or
( wa_primary-field2 NE wa_secondary-field2 ).
Lv_index = sy-tabix.
Endif.
u201C your code comes here u201C
Endloop.
Endloop.

which is very fast i have check it in most of the reports gave performance issues

you can check the link for detail

[http://nafran.blogspot.com/2009/09/when-and-where-we-can-use-parallel.html|http://nafran.blogspot.com/2009/09/when-and-where-we-can-use-parallel.html]

[ http://nafran.blogspot.com/2009/05/best-way-to-code-nested-loops-sap.html|http://nafran.blogspot.com...;

[Former Member]

hi

don't use sort command within the loop.

use it before making loop within loop...

its better u use read table command within loop.

thanks and regards

dhruv