11-20-2007 11:45 AM
hello..
please tell me when shud i use FOR ALL ENTRIES n when shud i use INNER JOINS in select query.. whats the difference betwn them..
Thank u..
11-20-2007 11:48 AM
Hi,
Inner Join: When multiple SAP tables are logically joined, it is always advisable to use inner join to read the data from them. This certainly reduces the load on the network.
Let us take an example of 2 tables, zairln and zflight. The table zairln has the field airln, which is the airline code and the field lnnam, which is the name of the airline. The table zflight has the field airln, the airline code and other fields which hold the details of the flights that an airline operates.
Since these 2 tables a re logically joined by the airln field, it is advisable to use the inner join.
Select aairln alnnam bfligh bcntry into table int_airdet
From zairln as a inner join zflight as b on aairln = bairln.
In order to restrict the data as per the selection criteria, a where clause can be added to the above inner join.
For all entries: Outer 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.
Not Recommended
Loop at int_cntry.
Select single * from zfligh into int_fligh
where cntry = int_cntry-cntry.
Append int_fligh.
Endloop.
Recommended
Select * from zfligh appending table int_fligh
For all entries in int_cntry
Where cntry = int_cntry-cntry.
<b>Reward Points if this helps,</b>
Satish
11-20-2007 11:48 AM
Hi,
Every time u should use for all entries, it is performance wise good dont use joins .
EX.
&----
*& Form SUB_READ_VBRK
&----
text
----
FORM sub_read_vbrk.
SELECT vbeln
rplnr
bukrs
FROM vbrk
INTO TABLE it_vbrk
WHERE vbeln IN s_vbeln
AND rplnr NE ' '.
IF sy-subrc EQ 0.
SORT it_vbrk BY rplnr.
ENDIF.
ENDFORM. " SUB_READ_VBRK
&----
*& Form SUB_READ_FPLTC
&----
text
----
FORM sub_read_fpltc.
IF NOT it_vbrk[] IS INITIAL.
SELECT fplnr
fpltr
ccnum
FROM fpltc
INTO TABLE it_fpltc
FOR ALL ENTRIES IN it_vbrk
WHERE fplnr EQ it_vbrk-rplnr
AND ccins EQ 'GIFC'.
IF sy-subrc EQ 0.
SORT it_fpltc BY fplnr.
ENDIF.
ENDIF.
ENDFORM. " SUB_READ_FPLTC
Reward Points
Reagrds,
Prashant
11-20-2007 11:51 AM
Hi,
if you use for all entries
means you have an internal table with some records
and you will hit another table and you fetch the records for those records in first internal table...
if we have 10 in the first table and for these 10 records there may n number of records in the 2nd table and you will get these
here you need two internal tables
but if you use joins
then it will give some records ( here if we have 10 records in first table and 20 in 2nd table then we will get 20 in our target table....)
here you need only one internal table
i hope you understood... with this
regards,
Venkatesh
11-20-2007 12:54 PM
hi,
Using FOR ALL ENTRIES in your will give better performance.
Dont go for JOINS with out knowing the key fields in the table.
If you know the correct key fields and which conditions should be used as where condition in the select query, then you can go ahead with JOIN.
Otherwise FOR ALL ENTRIES is the best way to use.
Thanks
Vijay
11-20-2007 12:58 PM
I do not agree to statement "FOR ALL ENTRIES ARE BETTER THAN JOINS",
if you design the join properly it will fetch data faster,
if u have more than 3 tables involved better go for for all entries
if the tables are related through key fields , join also will perform well
11-20-2007 1:36 PM
Hi Friends,
I was also confused about this part. Which one is recommended and which one is not. Recently i was breaking this select statment which has implemented "INNER JOIN" and I was trying to use "FOR ALL ENTRIES" to make the code Performance intensive.
select BKPFBELNR BKPFBUKRS BKPFCPUDT VBRKBUKRS VBRKFKART VBRKKUNAG VBRKVBELN VBRKVBTYP VBRKWAERK VBRPARKTX VBRPFKIMG VBRPMATNR VBRPNETWR VBRPPOSNR VBRPPSTYV VBRPVBELN VBRPVRKME VBRPWERKS MARALABOR MARAMATNR MARAMTART MBEWBWKEY MBEWMATNR MBEWSTPRS T001KBUKRS T001KBWKEY T001BUKRS T001WAERS
into (BKPF-BELNR , BKPF-BUKRS , BKPF-CPUDT , VBRK-BUKRS , VBRK-FKART , VBRK-KUNAG , VBRK-VBELN , VBRK-VBTYP , VBRK-WAERK
, VBRP-ARKTX , VBRP-FKIMG , VBRP-MATNR , VBRP-NETWR , VBRP-POSNR , VBRP-PSTYV , VBRP-VBELN , VBRP-VRKME , VBRP-WERKS
, MARA-LABOR , MARA-MATNR , MARA-MTART , MBEW-BWKEY , MBEW-MATNR , MBEW-STPRS , T001K-BUKRS , T001K-BWKEY , T001-BUKRS
, T001-WAERS )
from ( BKPF
inner join VBRK
on VBRKBUKRS = BKPFBUKRS
and VBRKVBELN = BKPFBELNR
inner join VBRP
on VBRPVBELN = VBRKVBELN
inner join MARA
on MARAMATNR = VBRPMATNR
inner join MBEW
on MBEWMATNR = VBRPMATNR
and MBEWBWKEY = VBRPWERKS
inner join T001K
on T001KBWKEY = MBEWBWKEY
inner join T001
on T001BUKRS = T001KBUKRS )
where BKPF~BUKRS in BUKRS
and BKPF~CPUDT in CPUDT
and VBRK~VBTYP in VBTYP
and VBRK~KUNAG in KUNAG
and VBRP~WERKS in WERKS
and VBRP~PSTYV in PSTYV
and MARA~LABOR in LABOR
and MARA~MTART in MTART.
Endselect.
Join is implemented on these many tables.
1> BKPF
2> VBRK
3> VBRP
4> MARA
5> MBEW
6> T001K
7> T001
When i compared the execution time of both the programmes then above query was taking much much less time to display the output as compared the code i had written.
So I think we can say that when we are selecting Data from multiple tables then it is better to implement Joins rather than going in for "FOR ALL Entries".
I would like to straight out this concept.
Inputs are welcome.
Regards,
Mayank
11-20-2007 3:34 PM
Just check this:
<a href="/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better">JOINS vs. FOR ALL ENTRIES - Which Performs Better?</a>
Rob