select join statement bad performance

[Former Member]

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

[Former Member]

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

[SwadhinGhatuary]

This message was moderated.

[Former Member]

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

[Former Member]

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

[PeterJonker]

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' ).

[Former Member]

I will try, thanks for answering

[RaymondGiuseppi]

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

[Former Member]

I will try, thanks for answering

[Former Member]

This message was moderated.