JOIN VS FOR ALL ENTRIES??

[Former Member]

HI,

what is the difference between FOR ALL ENTRIES and JOINS?

Bent.

[Former Member]

hi

Join can be created using this addition to the where clause in a select statement. It speeds up the performance tremendously, but the cons of using this variation are listed below

Duplicates are automatically removed from the resulting data set. Hence care should be taken that the unique key of the detail line items should be given in the select statement.

If the table on which the For All Entries IN clause is based is empty, all rows are selected into the destination table. Hence it is advisable to check before-hand that the first table is not empty.

If the table on which the For All Entries IN clause is based is very large, the performance will go down instead of improving. Hence attempt should be made to keep the table size to a moderate level.

Join can cause serious performance problems in <b>some situations</b>. it is preferred to fetch data from one table(itab), then use FOR ALL ENTRIES of itab to fetch the data from another table.but dont forget to delete the duplicate entries from itab.

theres a counter arguement too.

chk this blog:

http://blogs.ittoolbox.com/sap/db2/archives/for-all-entries-vs-db2-join-8912

**reward if helpful

regards,

madhu

[Former Member]

For all entries

The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the length of the WHERE clause.

The plus

Large amount of data

Mixing processing and reading of data

Fast internal reprocessing of data

Fast

The Minus

Difficult to program/understand

Memory could be critical (use FREE or PACKAGE size)

Some steps that might make FOR ALL ENTRIES more efficient:

Removing duplicates from the the driver table

Sorting the driver table

If possible, convert the data in the driver table to ranges so a BETWEEN statement is used instead of and OR statement:

FOR ALL ENTRIES IN i_tab

WHERE mykey >= i_tab-low and

mykey <= i_tab-high.

Select using JOINS

The plus

Very large amount of data

Similar to Nested selects - when the accesses are planned by the programmer

In some cases the fastest

Not so memory critical

The minus

Very difficult to program/understand

Mixing processing and reading of data not possible

For further reference regarding optimization you can look into this link....

http://www.sapbrain.com/ARTICLES/TECHNICAL/optimization/optimization.html#Optimizing%20the%20load%20...

Thanks & regards,

Mahendar Patha..

[Former Member]

hi,

For all entries clause:

1. duplicate rows are automatically removed

2. if the itab used in the clause is empty , all the rows in the source table will be selected .

3. performance degradation when using the clause on big tables.

these three disadvantages are not in JOIN

Regards,

Richa

[Former Member]

Hi,

When using FOR ALL ENTRIES the number of matching records is restricted to the number of records in the internal table. If the number of records in the database tables is too large then join would cause overheads in performance. Additionally a JOIN bypasses the table buffering.

regards,

keerthi