topic Re: SELECT query in Application Development and Automation Discussions

SELECT query

Former Member — Tue, 20 Nov 2007 11:45:37 GMT

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..

Re: SELECT query

Former Member — Tue, 20 Nov 2007 11:48:21 GMT

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 a_{airln a}lnnam b_{fligh b}cntry into table int_airdet

From zairln as a inner join zflight as b on a_{airln = b}airln.

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

Re: SELECT query

former_member386202 — Tue, 20 Nov 2007 11:48:47 GMT

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.

ENDFORM. " SUB_READ_FPLTC

Reward Points

Reagrds,

Prashant

Re: SELECT query

Former Member — Tue, 20 Nov 2007 11:51:26 GMT

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

Re: SELECT query

Former Member — Tue, 20 Nov 2007 12:54:54 GMT

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

Re: SELECT query

Former Member — Tue, 20 Nov 2007 12:58:25 GMT

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

Re: SELECT query

Former Member — Tue, 20 Nov 2007 13:36:43 GMT

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 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

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 VBRK_{BUKRS = BKPF}BUKRS

and VBRK_{VBELN = BKPF}BELNR

inner join VBRP

on VBRP_{VBELN = VBRK}VBELN

inner join MARA

on MARA_{MATNR = VBRP}MATNR

inner join MBEW

on MBEW_{MATNR = VBRP}MATNR

and MBEW_{BWKEY = VBRP}WERKS

inner join T001K

on T001K_{BWKEY = MBEW}BWKEY

inner join T001

on T001_{BUKRS = T001K}BUKRS )

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

Re: SELECT query

Former Member — Tue, 20 Nov 2007 15:34:06 GMT

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