topic Re: replacing selects with join in Application Development and Automation Discussions

replacing selects with join

Former Member — Wed, 30 Jul 2008 18:46:56 GMT

SELECT vbeln

kunnr

FROM vbpa

INTO TABLE l_i_vbpa

FOR ALL ENTRIES IN i_kna1

WHERE kunnr EQ i_kna1-kunnr.

IF NOT l_i_vbpa[] IS INITIAL.

Fetch the Credit checks.

SELECT vbeln

gbstk

cmgst

FROM vbuk

INTO TABLE l_i_vbuk

FOR ALL ENTRIES IN l_i_vbpa

WHERE vbeln EQ l_i_vbpa-vbeln.

with

SELECT a~vbeln

a~kunnr

b~gbstk

b~cmgst

from vbpa as a inner join vbuk as b

on a_{vbeln eq b}vbeln

INTO TABLE ITAB

FOR ALL ENTRIES IN i_kna1

WHERE kunnr EQ i_kna1-kunnr.

Is this a correct statement.

Pl let me know if any changes req.

Thanks

Kiran

Re: replacing selects with join

Former Member — Wed, 30 Jul 2008 18:50:46 GMT

hi,

Check if the table is empty or not for each select ...It is always better to use for all entries as two different selects as yours than using joins ...

if not i_kna1[] is initial.

SELECT a~vbeln

a~kunnr

b~gbstk

b~cmgst

from vbpa as a inner join vbuk as b

on a_{vbeln eq b}vbeln

INTO TABLE ITAB

FOR ALL ENTRIES IN i_kna1

WHERE kunnr EQ i_kna1-kunnr.

endif.

Re: replacing selects with join

Former Member — Wed, 30 Jul 2008 18:52:55 GMT

SELECT a~vbeln

a~kunnr

b~gbstk

b~cmgst

from vbpa as a inner join vbuk as b

on a_{vbeln eq b}vbeln

INTO TABLE ITAB

FOR ALL ENTRIES IN i_kna1

WHERE a~kunnr EQ i_kna1-kunnr.

Re: replacing selects with join

Former Member — Wed, 30 Jul 2008 18:53:46 GMT

WHERE a~kunnr EQ i_kna1-kunnr.

Re: replacing selects with join

Former Member — Wed, 30 Jul 2008 18:55:44 GMT

Hi Santhosh,

Thanks for that advise.

But am tuning a report that is running for almost 130hrs in background.Hence am replacing lot many selects with joins.

Is it a good way 2 replace selects in view of tuning by replacing those couple of selects which i posted.

Thanks

Kiran

Re: replacing selects with join

Former Member — Wed, 30 Jul 2008 19:00:32 GMT

in some cases Selects on Views will be useful. your select is combination of join and for all entries. so it will not be that performance.

Re: replacing selects with join

Former Member — Wed, 30 Jul 2008 19:01:59 GMT

hi,

Here i would advise you to make use of the entire key in both the separate selects than using joins as joins always affect performance .... when using the individual select you can even sort the table and delete the adjacent duplicate entries comparing fields....

Regards,

Santosh

Re: replacing selects with join

Former Member — Wed, 30 Jul 2008 19:02:05 GMT

No - your problem is not with JOINs. It is because the first SELECT does not use an index.

Rob

Re: replacing selects with join

Former Member — Wed, 30 Jul 2008 19:22:37 GMT

Exactly.

I was worrying about that only.

And I am also thinking, if my i replace them with a join, am i going to worsen the situation?

Thanks

Kiran