Inner Join issue in Select statement..

[Former Member]

Hi Experts,

i wrote select statement which is having InnerJoin with 5 tables, but it is taking lot of time for execution.

can anyone give me the best way. my peace of code is below :

SELECT a~vbeln

a~vkorg

a~vkbur

p~vbeln

p~prctr

p~matnr

p~werks

p~netpr

p~kpein

k~provg

e~bmeng

e~edatu

b~omeng

INTO CORRESPONDING FIELDS OF TABLE it_so

FROM ( ( ( ( vbak AS a INNER JOIN vbap AS p

ON a_{vbeln = p}vbeln )

INNER JOIN mvke AS k ON k_{matnr = p}matnr )

INNER JOIN vbep AS e ON e_{vbeln = a}vbeln )

INNER JOIN vbbe AS b ON b_{vbeln = p}vbeln )

WHERE k~provg IN s_provg

AND p~prctr IN s_prctr

AND p~matnr IN s_matnr.

Thanks in advance,

sudha.

[Former Member]

hi

you can try for all entries

select.. from vbak into lt_vabk

if sy-subrc = 0.

select... vbap for all entries in lt_vbak where vbeln = lt_vbak.

end if.

[SuhaSaha]

1. You should do away with the INTO CORRESPONDING FIELDS OF TABLE & replace by INTO TABLE. (I know many people will disagree with this but try in SE30 & check if you have any improved timeframes for the latter)

2. Out of the 3 selection criteria s_provg, s_prctr & s_matnr which ones are mandatory? Also none of these are key fields for the tables in question so definitely it is going to impact the performance.

BR,

Suhas

[jayesh_gupta]

Hi,

First of all, its never recommended to have a select with five tables. As soon as three tables are used with join for a select statement, the performance gets affected remarkably. This is because the JOIN bypasses the buffer and everytime SELECT hits the databse.

I would suggest you to first select the records from the main table into an internal table based upon the selection parameters. Also, try to maximize the use of key fields of the tables.

Regards,

Jayesh

[jayesh_gupta]

First write a select with tables vbap and mvke and get the corresponding fields into an internal table ITAB.

Later you may use SELECT ..........FOR ALL ENTRIES IN ITAB.....