Hi Experts
Initially, I had the below query, which took too much of execution time.
SELECT A~VBELN A~POSNR A~MATNR A~KWMENG A~KBMENG
A~ERDAT A~ERZET A~PSTYV D~AUART E~ETTYP
E~EDATU
INTO TABLE INT_COLL_ORD
FROM VBAP AS A
INNER JOIN VBAK AS D ON D~VBELN EQ A~VBELN AND
D~MANDT EQ A~MANDT
INNER JOIN VBEP AS E ON E~VBELN EQ A~VBELN AND
E~POSNR EQ A~POSNR AND
E~MANDT EQ A~MANDT
WHERE A~VBELN IN s_VBELN AND
D~auart in s_auart AND
D~vkorg in s_vkorg AND
D~vbtyp eq 'C' AND
( ( matnr LIKE c_prefix_sp AND zz_msposnr NE 0 AND kbmeng EQ 0 )
OR ( matnr LIKE c_prefix_fp AND kwmeng NE A~kbmeng ) ) AND
A~ABGRU EQ SPACE AND
A~MTVFP IN R_MTVFP AND
A~PRCTR IN R_PRCT AND
E~ETENR EQ '1'.
SORT INT_COLL_ORD BY VBELN POSNR ETTYP.
DELETE ADJACENT DUPLICATES FROM INT_COLL_ORD COMPARING VBELN POSNR.
CHECK NOT INT_COLL_ORD[] IS INITIAL.
SELECT VBELN UVALL CMGST INTO TABLE INT_VBUK
FROM VBUK FOR ALL ENTRIES IN INT_COLL_ORD
WHERE VBELN = INT_COLL_ORD-VBELN AND
UVALL NE 'A'.
SORT INT_VBUK BY VBELN.
DELETE ADJACENT DUPLICATES FROM INT_VBUK COMPARING VBELN.
So, I split this big INNER JOIN of 3 Tables into 2 different INNERJOINs as below:
SELECT A~AUART B~VBELN B~UVALL B~CMGST
INTO TABLE INT_VBUK
FROM VBAK AS A INNER JOIN VBUK AS B
ON A~VBELN EQ B~VBELN
WHERE A~VBELN IN s_VBELN AND
A~auart in s_auart AND
A~vkorg in s_vkorg AND
A~vbtyp eq 'C' AND
B~UVALL NE 'A'.
IF NOT INT_VBUK[] IS INITIAL.
SORT INT_VBUK BY VBELN.
DELETE ADJACENT DUPLICATES FROM INT_VBUK COMPARING VBELN.
SELECT A~VBELN A~POSNR A~MATNR A~KWMENG A~KBMENG A~ERDAT A~ERZET A~PSTYV B~ETTYP B~EDATU
INTO TABLE INT_COLL_ORD
FROM VBAP AS A INNER JOIN VBEP AS B
ON B~VBELN EQ A~VBELN AND B~POSNR EQ A~POSNR AND B~MANDT EQ A~MANDT
FOR ALL ENTRIES IN INT_VBUK
WHERE A~VBELN = INT_VBUK-VBELN AND
( ( matnr LIKE c_prefix_sp AND zz_msposnr NE 0 AND kbmeng EQ 0 )
OR ( matnr LIKE c_prefix_fp AND kwmeng NE A~kbmeng ) ) AND
A~ABGRU EQ SPACE AND
A~MTVFP IN R_MTVFP AND
A~PRCTR IN R_PRCT AND
B~ETENR EQ '1'.
ENDIF.
SORT INT_COLL_ORD BY VBELN POSNR ETTYP.
DELETE ADJACENT DUPLICATES FROM INT_COLL_ORD COMPARING VBELN POSNR.
CHECK NOT INT_COLL_ORD[] IS INITIAL.
loop at int_coll_ord.
read table int_vbuk with key vbeln = int_coll_ord-vbeln BINARY SEARCH.
int_coll_ord-auart = int_vbuk-auart.
modify int_coll_ord.
endloop.
Still, it is noted that the performance is not improved at all. All the more, this query takes more time than the prior one.
Could anybody help me to re-write this query in an effective way?
Regards
BD