topic Re: Alternative for double inner join in Application Development and Automation Discussions

Alternative for double inner join

Former Member — Wed, 26 May 2010 08:43:29 GMT

SELECT SINGLE ALNUM INTO P_ITAB-ALNUM_AL FROM MAEX

JOIN MARC ON MARC_{MATNR = MAEX}MATNR

JOIN T001W ON T001W_{WERKS = MARC}WERKS

WHERE MAEX~MATNR = P_VBPLP-MATNR

AND MARC~WERKS = T_VBDPL-WERKS

AND MAEX_{ALAND = T001W}LAND1

AND MAEX~GEGRU = 'DE'.

Can anyone suggest the best way to modify the above statement to improve the performence.

Thanks,

Khasimsa

Re: Alternative for double inner join

Former Member — Wed, 26 May 2010 09:23:28 GMT

a Join with several tables is no problem by itself, so why do you want to improve it?

The SELECT SINGLE is weird, in a Join you will hardly know whether only one record can fulfill the condition, i.e.

UP TO 1 ROWS would be clearer. But why is only one value needed?

Is the join inside a LOOP?

Re: Alternative for double inner join

Former Member — Wed, 26 May 2010 10:12:02 GMT

yes this join is insde a loop.

Re: Alternative for double inner join

Former Member — Wed, 26 May 2010 10:20:02 GMT


> yes this join is insde a loop.

within loop avoid select statement.

use read statement for this issue ,and within loop you are getting one values so check for a rfc or fm for the same.

loop at lt_maex.

read table lt_t001w where matnr = lt_maex-matnr.

if sy-subrc = 0.

endif.

endloop.

Re: Alternative for double inner join

Former Member — Wed, 26 May 2010 10:27:07 GMT

Hi Shaik,

Dont use the select query within the loop.

Performance wise it is not recommended.

Instead you select the above join query in an internal table i.e.,

SELECT a b c, d INTO P_ITAB FROM MAEX

JOIN MARC ON MARC_{MATNR = MAEX}MATNR

JOIN T001W ON T001W_{WERKS = MARC}WERKS

WHERE MAEX~MATNR = P_VBPLP-MATNR

AND MARC~WERKS = T_VBDPL-WERKS

AND MAEX_{ALAND = T001W}LAND1

AND MAEX~GEGRU = 'DE'.

Then

Loop at itab.

read p_itab with key a

endloop.

This will improve your performance as well.

Regards,

Md Ziauddin

Re: Alternative for double inner join

HermannGahm — Wed, 26 May 2010 11:32:55 GMT

Hi,

T001W is a buffered table. The buffer can not be used with the join. Remove the T001W from the join and read it from the buffer separately.

For the remaining tables in the join take a trace.

Check for index support and identical SELECTS.

Kind regards,

Hermann

Re: Alternative for double inner join

Former Member — Wed, 26 May 2010 13:15:58 GMT

many close misses

If the SEKECT is inside a LOOP you should not use a READ in a LOOP, usually the correct coding is a FOR ALL ENTRIES.

The buffered table ... hmm, as long as we do not see the full coding, the buffered table T001W is not the first in the join, maybe it is even to leave it in the join. However, I have some doubts that it is necessary, the coding lloks weird:

AND MAEX_{ALAND = T001W}LAND1

The country LAND1 is not checked on T001W.

Better copy more coding which shows where the values from WHERE condition come from.

Is this working coding (tested correct coding) or still in development ?

Siegfried

Re: Alternative for double inner join

Former Member — Wed, 26 May 2010 14:56:48 GMT

Besides the good point to avoid selects in loops, this may be a little bit better join order:

SELECT SINGLE ALNUM 
  INTO P_ITAB-ALNUM_AL 
  FROM MARC
  JOIN T001W ON T001W~WERKS = MARC~WERKS
  JOIN MAEX ON MAEX~MATNR = MARC~MATNR
           AND MAEX~ALAND = T001W~LAND1
 WHERE MARC~MATNR = P_VBPLP-MATNR
   AND MARC~WERKS = T_VBDPL-WERKS
   AND MAEX~GEGRU = 'DE'.

Re: Alternative for double inner join

ravi_lanjewar — Mon, 31 May 2010 06:07:00 GMT

Hi,

Don't used table t001w in join, While using buffer table in join operation it will not read data from buffer,

1) Read data from table t001w

2) apply join on table marc and maex and also put where condition with werks because marc table having primary key with fields matnr and werks so put both in where condition.


   select single * from t001w client specified
     where mandt = sy-mandt
          and werks =  T_VBDPL-WERKS.

  SELECT SINGLE ALNUM  INTO P_ITAB-ALNUM_AL 
     FROM MARC  JOIN MAEX ON MAEX~MATNR = MARC~MATNR
     WHERE MARC~MATNR = P_VBPLP-MATNR
         AND MARC~WERKS = T_VBDPL-WERKS
         AND MAEX~ALAND =  T001W~LAND1
         AND MAEX~GEGRU = 'DE'.

Re: Alternative for double inner join

Former Member — Thu, 25 Nov 2010 16:18:38 GMT

This message was moderated.