Please help me about joins

[Former Member]

Can anyone please tell me in real environment, generally people use 'joins' or 'for all entries' in select stmt ..... Eventhough 'for all entries' is used for performance in which case 'joins' will be used? Pleaseeee

give me the info..

Regards

Yuktha

[Former Member]

hii,

joins are used to join the fields of more than 1 table ..... in the select statements the joins are written but with for all entries we can use aliases for the joining condition.

thanks....

[valter_oliveira]

Hello!

Both Statements JOIN and FOR ALL ENTRIES are used for performance.

You can use JOIN to get data from two or more database tables only in one select.

SELECT a_{field1 b}field3

INTO (wa-field1, wa-field2)

FROM dbt1 as a INNER JOIN dbt2

ON a_{key = b}key ...

You can use FOR ALL ENTRIES to avoid using:

LOOP AT itab.

SELECT ... FROM dbt... WHERE field1 = itab-field1.

ENDLOOP.

You use SELECT ... FROM dbt FOR ALL ENTRIES IN itab.

Diferent statements to improve performance.

Best regards.

Valter Oliveira.

[christine_evans]

Use joins when selecting data from two or more database tables - they can be very efficient when written properly.

Use FOR ALL ENTRIES when you need to do a 'join' between a database table and an ABAP internal table; again using this can be fine, though I have been told that sometimes it doesn't work too well on DB2 databases.

Using RANGES is another option. Though there is a limit to the number of entries you can include in your RANGES tables and if you exceed this you'll get a dump.

All the above can be efficient or not depending on such factors as how well they are written, the amount of data etc.

[Former Member]

Hi yuktha,

joins as well as 'FOR ALL ENTERIES " are used.

for all enteries are used to retrieve small data from large table

another reason for getting data from pool/cluster table.

for example i am sending you code:

SELECT mapl_{matnr mapl}werks mapl_{plnnr makt}maktx plfl~datuv FROM mapl

• "plfl_{losvn "plfl}losbs "plpo~meinh

INNER JOIN mara ON ( mapl_{matnr = mara}matnr )

INNER JOIN makt ON ( mara_{matnr = makt}matnr AND spras EQ 'EN' )

INNER JOIN plfl ON ( mapl_{plnnr = plfl}plnnr )

INNER JOIN plpo ON ( plpo_{plnnr = mapl}plnnr )

INTO TABLE i_mapl

WHERE mapl_{matnr IN s_matnr AND mtart IN s_mtart AND mara}matkl IN s_matkl

AND mapl_{werks IN s_werks AND plfl}datuv IN s_datuv AND mapl~plnty EQ 'Q'.

SORT i_mapl BY matnr werks plnnr datuv.

DELETE ADJACENT DUPLICATES FROM i_mapl COMPARING matnr werks plnnr datuv.

IF i_mapl[] IS NOT INITIAL.

SELECT plnnr merknr pmethode kurztext stellen masseinhsw

stichprver verwmerkm auswmenge1 toleranzob toleranzun

FROM plmk INTO TABLE i_plmk1 FOR ALL ENTRIES IN i_mapl WHERE plmk~plnnr = i_mapl-plnnr

AND stichprver IN stpr.

ENDIF.

reward marks if it is useful.

[Former Member]

Hi Yuktha,

Here is the information that could give you some idea in regard of FOR ALL ENTRIES VS JOINS:

3 major drawbacks of the "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.

First of let us see the use of for all entries clause as amean to join an ABAP code ::

Say for example you have the following abap code:

Select * from mara

For all entries in itab

Where matnr = itab-matnr.

If the actual source of the material list (represented here by itab) is actually another database table, like:

select matnr from mseg

into corresponding fields of table itab

where ?.

Then you could have used one sql statement that joins both tables.

Select t1.*

From mara t1, mseg t2

Where t1.matnr = t2.matnr

And T2?..

So what are the drawbacks of using the "for all entires" instead of a join ?

At run time , in order to fulfill the "for all entries " request, the abap engine will generate several sql statements (for detailed information ). Regardless of which method the engine uses (union all, "or" or "in" predicates) . If the itab is bigger then a few records, the abap engine will break the itab into parts, and rerun an sql statement several times in a loop. This rerun of the same sql statement , each time with different host values, is a source of resource waste because it may lead to re-reading of data pages.

In other words , when you use the "for all entries " clause instead of coding a join , you are depriving the database of important information needed to select the best access path for your application. Moreover, you are depriving your DBA of the same vital information. When the DBA monitors & tunes the system, he (or she) is less likely to recognize this kind of resource waste. The DBA will see a simple statement that uses an index , he is less likely to realize that this statement is executed in a loop unnecessarily.

In conclusion I suggest to "think twice" before using the "for all entries" clause and to evaluate the use of database views as a means to:

a. simplify sql

b. simplify abap code

c. get around open sql limitations.

Here in this issues we can approach JOINS as an alternate option.

Hope this onfirmation is useful to you.

Reward points if helpful,

Thanks & Regards,

Madhavi.M