Select Join Performance

[Former Member]

Hi,

I have a performance problem with this select statement: Takes 30 to 50 secs.

    SELECT k~ebeln  k~lifnr  k~ekgrp  k~ekorg  k~bukrs  k~bsart
           k~bstyp  k~bedat  k~reswk  k~spras  k~adrnr
           k~waers p~ebelp  p~pstyp  p~werks  p~matnr  p~matkl  p~txz01
           p~idnlf  p~labnr
           p~elikz  p~erekz  p~knttp  p~vrtkz  p~meins  p~mfrpn
            p~effwr p~netpr p~peinh p~bprme
           t~etenr  t~eindt  t~menge  t~wemng  t~slfdt  t~mahnz
           INTO CORRESPONDING FIELDS OF TABLE g_i_tab1
           FROM  ekko    AS k
           JOIN  ekpo    AS p
           ON    p~ebeln =  k~ebeln
           JOIN  eket    AS t
           ON    t~ebeln =  k~ebeln  AND
                 t~ebelp =  p~ebelp
           WHERE k~lifnr IN s_lifnr  AND
                 k~ekorg IN s_ekorg  AND
                 k~ekgrp IN s_ekgrp  AND
                 k~bedat IN s_bedat  AND
                 k~ebeln IN s_ebeln  AND
                 k~bstyp IN s_bstyp  AND
                 k~bsart IN s_bsart  AND
                 k~loekz =  space    AND
                 p~matnr IN s_matnr  AND
                 p~werks IN s_werks  AND
                 p~bstyp IN s_bstyp  AND
                 p~loekz =  space    AND
                 p~elikz IN r_elikz  AND
                 p~matkl IN s_matkl  AND
                 p~pstyp IN s_pstyp  AND
                 p~knttp IN s_knttp  AND
                 p~kanba <> 'Y'      AND
                 t~eindt IN s_eindt  AND
                 t~menge >  0        AND
                 t~menge >  t~wemng.

The SQL-Trace (ST05) shows two nested loops, the selection starts always with EKKO

I wonder whether I can force the System to start the selection with Table EKPO, using a certain Index?

(When I count the number of entries with SE16N it takes only 1 sec and I should have the Unique Index to read the other two tables.)

What can I do?

Best regards

Jens

[volker_borowski2]

Hi,

summary agreed, Herman

I am not so familliar with these MM and SD tables (and have no access to a system to look it up now).

Is MENGE a real number on DB Level?

SAP has quite a lot num-values that are stored as chars, so it might be neccessary to put in some

num-conversion stuff in addition to build a working index. And it must have of course the join fields in addition.

Volker

[former_member186741]

I don't know if anyone has already suggested something like this but it might be worth trying to split the select

like the following to get the key fields.

SELECT ebeln ebelp

INTO CORRESPONDING FIELDS OF TABLE g_ekpo_keys "sorted on ebeln ebelp

FROM ekpo

WHERE matnr IN s_matnr AND

werks IN s_werks AND

bstyp IN s_bstyp AND

loekz = space AND

elikz IN r_elikz AND

matkl IN s_matkl AND

pstyp IN s_pstyp AND

knttp IN s_knttp AND

kanba 'Y' .

if g_ekpo_keys is not initial.

SELECT k_{ebeln k}lifnr k_{ekgrp k}ekorg k_{bukrs k}bsart

k_{bstyp k}bedat k_{reswk k}spras k~adrnr

k_{waers p}ebelp p_{pstyp p}werks p_{matnr p}matkl p~txz01

p_{idnlf p}labnr

p_{elikz p}erekz p_{knttp p}vrtkz p_{meins p}mfrpn

p_{effwr p}netpr p_{peinh p}bprme

t_{etenr t}eindt t_{menge t}wemng t_{slfdt t}mahnz

INTO CORRESPONDING FIELDS OF TABLE g_i_tab1

FROM ekpo AS p

JOIN ekko AS k

ON k_{ebeln = p}ebeln

JOIN eket AS t

ON t_{ebeln = p}ebeln AND

t_{ebelp = p}ebelp

for all entries in g_ekpo_keys

WHERE p~ebeln = g_ekpo_keys-ebeln and

p_{ebelp = g_ekpo_keys}ebelp and

k~lifnr IN s_lifnr AND

k~ekorg IN s_ekorg AND

k~ekgrp IN s_ekgrp AND

k~bedat IN s_bedat AND

k~ebeln IN s_ebeln AND

k~bstyp IN s_bstyp AND

k~bsart IN s_bsart AND

k~loekz = space AND

t~eindt IN s_eindt AND

t~menge > 0 AND

t_{menge > t}wemng.

endif.

[Former Member]

Hi,

thanks for your ideas and your commitment.

The most promising path to a real solution seems to be the KEEP pool as Volker suggested.

The alternative seems to be, to select schedulling agreements and purchase orders sepeparately as Kris suggested.

Best regards

Jens

[Former Member]

pls use this function ME_READ_HISTORY for po history.