Performance Issue with this Join

[Former Member]

select distinct vbrk_{vbeln vbrk}fkart vbrk_{vtweg vbrk}knumv vbrk~fkdat

vbrk_{kunrg vbrk}sfakn vbrk~knuma

into table i_vbrk

from VRPMA join vbrk

on vrpma_{vbeln = vbrk}vbeln

where ( vrpma_{fkart = 'ZCB4' or vrpma}fkart = 'S2' or

vrpma~fkart = 'S1')

and vrpma~vtweg = p_vtweg

and vbrk~fkdat ge t_date

and vbrk~fkdat le e_date

and vbrk~vkorg = p_vkorg.

Any ideas on enhancing the above selection as both vbrk vrpma are huge tables!

Thanks in advance

[Former Member]

As you are selecting all the fields from VBRK and even the filters are also present in VBRK table, I really do not understand the use of VRPMA.

Instead if you try the below query

select distinct vbrk_{vbeln vbrk}fkart vbrk_{vtweg vbrk}knumv vbrk~fkdat

vbrk_{kunrg vbrk}sfakn vbrk~knuma

into table i_vbrk

from vbrk

where vbrk~fkart in ('ZCB4','S2','S1')

and vrpma~vtweg = p_vtweg

and vbrk~fkdat ge t_date

and vbrk~fkdat le e_date

and vbrk~vkorg = p_vkorg.

[messier31]

Hi Srikanth,

Try this

select vbrk_{vbeln vbrk}fkart vbrk_{vtweg vbrk}knumv vbrk~fkdat

vbrk_{kunrg vbrk}sfakn vbrk~knuma

into table i_vbrk

<b>from VBRK join VRPMA</b> on vbrk_{vbeln = vrpma}vbeln

where ( vrpma_{fkart = 'ZCB4' or vrpma}fkart = 'S2' or

vrpma~fkart = 'S1')

and vrpma~vtweg = p_vtweg

and vbrk~fkdat ge t_date

and vbrk~fkdat le e_date

and vbrk~vkorg = p_vkorg.

let me know if is efficient and works by selecting appropriate data..

[Former Member]

Pankaj,

Thanks for your reply, your suggestion basically involves interchanging selection tables, so this has no effect on performance metric. any ideas about enhancing/replacing joins as a whole ??

Thanks.

[Former Member]

give a try to this piece of code

select vbrk_{vbeln vbrk}fkart vbrk_{vtweg vbrk}knumv vbrk~fkdat

vbrk_{kunrg vbrk}sfakn vbrk~knuma

into table i_vbrk

from VRPMA inner join vbrk

on vrpma_{mandt = vbrk}mandt

and vrpma_{vbeln = vbrk}vbeln

and vrpma_{vbeln = vbrk}vbeln

and vrpma_{vkorg = vbrk}vkorg

and vrpma_{fkdat = vbrk}fkdat

and vrpma_{vtweg = vbrk}vtweg

and vrpma_{fkart = vbrk}fkart

and vrpma_{kunnr = vbrk}kunrg

and vrpma_{kunag = vbrk}kunag

and vrpma_{vbtyp = vbrk}vbtyp

and vrpma_{ERNAM = vbrk}ERNAM

where ( vrpma_{fkart = 'ZCB4' or vrpma}fkart = 'S2' or

vrpma~fkart = 'S1')

and vrpma~vtweg = p_vtweg

and vbrk~fkdat ge t_date

and vbrk~fkdat le e_date

and vbrk~vkorg = p_vkorg.

after the select query

SORT i_vbrk by fdl1 fld2...

delete adjacent duplicates from i_vbrk comparing fld1 fld2 ...

note: here fld1, fld2 denotes the key fields of your internal table according to which you were initially saying SELECT DISTINCT.

SHANE

[Former Member]

Could you just tell me whether view created based on these 2 tables can be used instead of join. Please suggest me which is more efficient with very large amount of data??

[Former Member]

As you are selecting all the fields from VBRK and even the filters are also present in VBRK table, I really do not understand the use of VRPMA.

Instead if you try the below query

select distinct vbrk_{vbeln vbrk}fkart vbrk_{vtweg vbrk}knumv vbrk~fkdat

vbrk_{kunrg vbrk}sfakn vbrk~knuma

into table i_vbrk

from vbrk

where vbrk~fkart in ('ZCB4','S2','S1')

and vrpma~vtweg = p_vtweg

and vbrk~fkdat ge t_date

and vbrk~fkdat le e_date

and vbrk~vkorg = p_vkorg.