topic Re: select join statement bad performance in Application Development and Automation Discussions

select join statement bad performance

Former Member — Wed, 02 Dec 2015 11:56:09 GMT

Hi all,

I have a select statement below;

select * into table itab from ekpo as a

inner join ekbe as b

on a~ebeln = b~ebeln and a~ebelp = b~ebelp

inner join rbkp as c

on b~belnr = c~belnr and b~gjahr = c~gjahr

where ( a~matnr = 'XXXXX' or a~zzmatnr = 'XXXXX' )

and b~belnr like '510%'.

This statement is very slow because of ( a~matnr = 'XXXXX' or a~zzmatnr = 'XXXXX' ) part of where statement.

When I change where statement

where a~matnr = 'XXXXX'

and b~belnr like '510%'.

like this, it is so fast. But I must select the union of lines from ekpo where matnr = 'XXXXX' also zzmatnr(custom field in ekpo) = 'XXXXX'.

I created a secondary index to ekpo for zzmatnr field but nothing changed. Again so bad performance.

Do you have any idea about this?

Thanks.

Gözde Candan Soysal

Re: select join statement bad performance

SwadhinGhatuary — Wed, 02 Dec 2015 13:03:08 GMT

This message was moderated.

Re: select join statement bad performance

Former Member — Wed, 02 Dec 2015 13:04:07 GMT

Hello,

Have you tried to decouple the join into 2 selects

the first one which does the select from ekpo with ( a~matnr = 'XXXXX' or a~zzmatnr = 'XXXXX' )

and a second one with for all entries over ekbe and rbkp?

Best regrads,

Nikolaus

Re: select join statement bad performance

PeterJonker — Wed, 02 Dec 2015 13:20:01 GMT

Hi Gozde,

I think it is the OR statement (for the matnr) that delays your query.

Did you try:

select * into table itab from ekpo as a

inner join ekbe as b

on a~ebeln = b~ebeln and a~ebelp = b~ebelp

inner join rbkp as c

on b~belnr = c~belnr and b~gjahr = c~gjahr

where b~belnr like '510%'.

delete itab where not ( matnr = 'XXXXX'

or matnr = 'YYYYY' ).

Re: select join statement bad performance

RaymondGiuseppi — Wed, 02 Dec 2015 13:24:51 GMT

Try to avoid performance problems that can generate ... FOR ALL ENTRIES

The SQL optimizer doesn't seem able to manage the "OR" option in the SELECT, so break it:


SELECT * INTO TABLE itab
  FROM ekpo AS a
  INNER JOIN ekbe AS b
    ON a~ebeln = b~ebeln AND a~ebelp = b~ebelp
  INNER JOIN rbkp AS c
    ON b~belnr = c~belnr AND b~gjahr = c~gjahr
  WHERE a~matnr = 'XXXXX'
    AND b~vgabe IN ('2', '3', 'P')
    AND b~belnr  LIKE '510%'. " where does this come from?
SELECT * APPENDING TABLE itab
  FROM ekpo AS a
  INNER JOIN ekbe AS b
    ON a~ebeln = b~ebeln AND a~ebelp = b~ebelp
  INNER JOIN rbkp AS c
    ON b~belnr = c~belnr AND b~gjahr = c~gjahr
  WHERE a~zzmatnr = 'XXXXX'
    AND b~vgabe IN ('2', '3', 'P')
    AND b~belnr  LIKE '510%'.
DELETE ADJACENT DUPLICATES FROM itab. " sort table if required

Hint: Of course you need an index on ZZMATNR else you get a full table scan...

Regards,

Raymond

Re: select join statement bad performance

Former Member — Wed, 02 Dec 2015 14:34:34 GMT

Yes I tried, but the problem is the OR statement. So it didn't solve my problem. Thanks for answer

Re: select join statement bad performance

Former Member — Wed, 02 Dec 2015 14:36:14 GMT

I will try, thanks for answering

Re: select join statement bad performance

Former Member — Wed, 02 Dec 2015 14:36:28 GMT

I will try, thanks for answering

Re: select join statement bad performance

Former Member — Thu, 03 Dec 2015 18:41:39 GMT

This message was moderated.