topic Re: Inner Join in Application Development and Automation Discussions

Inner Join

Former Member — Thu, 25 Oct 2007 08:02:30 GMT

Hi,

Can we improve performance by using simple queries instead of inner joins?

I have one query

select l_{vbeln l}vkorg l_{kunnr l}zukrl l~werks into table i_vbeln

from ( likp as l inner join vbpa as v

on l_{mandt = v}mandt

and l_{vbeln = v}vbeln )

where l~mandt = sy-mandt

and l~vstel in so_vstel

and l~vkorg in so_vkorg

and l~wadat in so_wadat

and l~route in so_route

and l~kunnr in so_wempf

and l~wadat_ist in so_udate

and l~werks in so_werks

and v~parvw in so_parvw

and v~kunnr in so_parnr

and v~land1 in so_land1.

Can we modify it to improve th efficiency?

Re: Inner Join

Former Member — Thu, 25 Oct 2007 08:08:33 GMT

hi mahendra,

no its not possible to use simple select statement as we have to use inner joins to improve its effieciency as when u r working on DEVELOPMENT SERVER it will work fine(simple select statements) as dere is less less amount of data but when we want to execute on PRODUCTION SERVER OR MAIN SERVER ,its efficiency will lead go down as dere will be more amount of data.

so ,for dis reason i suggest u to use inner joins insted of simple select statements.

on more thing ,try to use maximum of 3 inner joins as in 1 select statement.

if u find it useful.plzzzzz do give points toooo.

regards,

ric.s

Re: Inner Join

Former Member — Thu, 25 Oct 2007 08:19:14 GMT

Hi Mahendra,

if you want to improve the performance, first you retrive data from likp and then retrive data from vbpa using for all entries in likp.

<b>Reward points for helpful answers</b>

Satish

Re: Inner Join

Former Member — Thu, 25 Oct 2007 08:22:04 GMT

select l_{vbeln l}vkorg l_{kunnr l}zukrl l~werks into table i_vbeln

from ( likp as l inner join vbpa as v

on l_{vbeln = v}vbeln )

where l~vstel in so_vstel

and l~vkorg in so_vkorg

and l~wadat in so_wadat

and l~route in so_route

and l~kunnr in so_wempf

and l~wadat_ist in so_udate

and l~werks in so_werks

and v~parvw in so_parvw

and v~kunnr in so_parnr

and v~land1 in so_land1.

select vbeln vkorg kunnr zukrl werks into table i_likp

from likp

where vstel in so_vstel

and vkorg in so_vkorg

and wadat in so_wadat

and route in so_route

and kunnr in so_wempf

and wadat_ist in so_udate

and werks in so_werks.

if not i_likp[] is initial.

select Vbeln kunnr parvw into i_vbpa from vbpa

for all entries in i_likp

where

vbeln = i_likp-vbeln and

and parvw in so_parvw

and kunnr in so_parnr

and land1 in so_land1.

endif.

Regards

Anji

Re: Inner Join

former_member386202 — Thu, 25 Oct 2007 08:24:24 GMT

Hi,

To improve the performance first retrive the data from lips table and

then retrive data from vbpa table using for all entries.

regards,

Prashant

Re: Inner Join

Former Member — Thu, 25 Oct 2007 08:43:27 GMT

Hi Mahendra,

Yes, we can improve the performance by using FOR ALL ENTRIES instead of INNER JOINS.

Modify form of your query for the improve efficiency is as follow:

SELECT VBELN MANDT

FROM VBAP

INTO CORRESPONDING FIELDS OF TABLE ITAB1

WHERE MANDT = SY-MANDT

AND parvw in so_parvw

and kunnr in so_parnr

and land1 in so_land1.

SELECT VBELN

VKORG

KUNNR

ZUKRL

WERKS

FROM LIKP

INTO CORRESPONDING FIELDS OF TABLE ITAB2

FOR ALL ENTRIES IN ITAB1

WHERE VBELN = ITAB1-VBELN

AND MANDT = ITAB1-MANDT

AND vstel in so_vstel

and vkorg in so_vkorg

and wadat in so_wadat

and route in so_route

and kunnr in so_wempf

and wadat_ist in so_udate

and werks in so_werks.

Please Reward if useful.

Regards,

Shilpi