09-30-2005 1:27 PM
Hi,
Can anyone re-design the code as this is giving me a performance issue. The earlier code and change code is given. But still, the db hit is around 80%.
<b>Old Code:</b>
**<<<++Start of changes NEX4CRH 08.08.2005 DV2K922632 CR17140
*SELECT
PGMIPRGRP MSEGMATNR MSEGERFMG MKPFVGART MKPF~BUDAT
*MSEGWERKS MSEGSHKZG
MSEGMBLNR MSEGMJAHR MKPF~MJAHR
FROM PGMI INNER JOIN MSEG
ON ( PGMINRMIT = MSEGmatnr ) INNER JOIN MKPF ON
( MSEGMBLNR = MKPFMBLNR AND MSEGMJAHR = MKPFMJAHR ) INTO
*CORRESPONDING FIELDS OF TABLE
INT_ACTUAL WHERE PGMI~PRGRP IN S_PRDGRP AND
( MSEG~BWART = '101' OR
MSEG~BWART = '102' )
AND MKPF~VGART = 'WF'.
<b>New-Code</b>
SELECT PRGRP NRMIT FROM PGMI INTO TABLE INT_PGMI
WHERE PRGRP IN S_PRDGRP.
SELECT MBLNR MATNR WERKS ERFMG MJAHR BWART SHKZG
INTO TABLE INT_MSEG
FROM MSEG FOR ALL ENTRIES IN INT_PGMI
WHERE MATNR = INT_PGMI-NRMIT AND
( MSEG~BWART = '101' OR
MSEG~BWART = '102' ).
SELECT MBLNR MJAHR VGART BUDAT FROM MKPF INTO TABLE INT_MKPF
FOR ALL ENTRIES IN INT_MSEG
WHERE MBLNR = INT_MSEG-MBLNR AND
MJAHR = INT_MSEG-MJAHR AND
MKPF~VGART = 'WF'.
LOOP AT INT_MKPF.
READ TABLE INT_MSEG WITH KEY MBLNR = INT_MKPF-MBLNR
MJAHR = INT_MKPF-MJAHR1.
IF SY-SUBRC = 0.
MOVE INT_MSEG-MBLNR TO INT_ACTUAL-MBLNR.
MOVE INT_MSEG-MATNR TO INT_ACTUAL-MATNR.
MOVE INT_MSEG-WERKS TO INT_ACTUAL-WERKS.
MOVE INT_MSEG-ERFMG TO INT_ACTUAL-ERFMG.
MOVE INT_MSEG-MJAHR TO INT_ACTUAL-MJAHR.
MOVE INT_MSEG-BWART TO INT_ACTUAL-BWART.
MOVE INT_MSEG-SHKZG TO INT_ACTUAL-SHKZG.
MOVE INT_MKPF-VGART TO INT_ACTUAL-VGART.
MOVE INT_MKPF-BUDAT TO INT_ACTUAL-BUDAT.
APPEND INT_ACTUAL.
ENDIF.
ENDLOOP.
CLEAR INT_ACTUAL.
LOOP AT INT_ACTUAL.
READ TABLE INT_PGMI WITH KEY NRMIT = INT_ACTUAL-MATNR.
IF SY-SUBRC = 0.
MOVE INT_PRDGRP-PRGRP TO INT_ACTUAL-PRGRP.
MODIFY INT_ACTUAL.
ENDIF.
ENDLOOP.
Please advise in this regard.
09-30-2005 1:35 PM
MSEG is a big table, and I have struggled with accessing it in the past. You can probably improve a little, but don't expect it to be really fast.
Here are some hints....
SELECT PRGRP NRMIT FROM PGMI INTO TABLE INT_PGMI
WHERE PRGRP IN S_PRDGRP.
<b>
* make sure that this table is sorted by NRMIT
sort int_pgmi ascending by nrmit.</b>
<b>* you will want to check to make sure that
* INT_PGMI is not initial.
check not INT_PGMI[] is initial.</b>
SELECT MBLNR MATNR WERKS ERFMG MJAHR BWART SHKZG
INTO TABLE INT_MSEG
FROM MSEG FOR ALL ENTRIES IN INT_PGMI
WHERE MATNR = INT_PGMI-NRMIT AND
( MSEG~BWART = '101' OR
MSEG~BWART = '102' ).
<b>* make sure that INT_MSEG is sorted.
sort INT_MSEG ascending by mblnr mjahr.
* check to make sure that it is not initial.
check not INT_MSEG[] is initial.</b>
SELECT MBLNR MJAHR VGART BUDAT FROM MKPF INTO TABLE INT_MKPF
FOR ALL ENTRIES IN INT_MSEG
WHERE MBLNR = INT_MSEG-MBLNR AND
MJAHR = INT_MSEG-MJAHR AND
MKPF~VGART = 'WF'.
Regards,
Rich Heilman
09-30-2005 2:23 PM
In addition to what rich said, make also sure that there are no duplicates entries in your int_pgmi and int_mseg otherwise the records would be retrieved several times.
Since int_mseg can become rather large you should not access the table in that way. Although it assumes a fast key search it is nothing else but a sequential search.
Define intMseg type hashed table of ... with table key mblnr mjahr.
and use
READ TABLE INT_MSEG WITH TABLE KEY MBLNR = INT_MKPF....
instead of
READ TABLE INT_MSEG WITH KEY MBLNR = INT_MKPF-MBLNR
MJAHR = INT_MKPF-MJAHR1.
IF SY-SUBRC = 0.
MOVE INT_MSEG-MBLNR TO INT_ACTUAL-MBLNR.
MOVE INT_MSEG-MATNR TO INT_ACTUAL-MATNR.
MOVE INT_MSEG-WERKS TO INT_ACTUAL-WERKS.
MOVE INT_MSEG-ERFMG TO INT_ACTUAL-ERFMG.
MOVE INT_MSEG-MJAHR TO INT_ACTUAL-MJAHR.
MOVE INT_MSEG-BWART TO INT_ACTUAL-BWART.
MOVE INT_MSEG-SHKZG TO INT_ACTUAL-SHKZG.
MOVE INT_MKPF-VGART TO INT_ACTUAL-VGART.
MOVE INT_MKPF-BUDAT TO INT_ACTUAL-BUDAT.
APPEND INT_ACTUAL.
ENDIF.
ENDLOOP.
Christian
09-30-2005 1:48 PM
Hi,
try this one
SELECT PRGRP NRMIT FROM PGMI INTO TABLE INT_PGMI
WHERE PRGRP IN S_PRDGRP.
-
modified
SELECT AMBLNR AVGART ABUDAT AMJAHR
BMATNR BWERKS BERFMG BBWART B~SHKZG
FROM BKPF AS A INNER JOIN
MSEG AS B ON AMBLNR = BMBLNR AND
AMJAHR = BMJAHR
INTO CORRESPONDING FIELD OF TABLE
FOR ALL ENTRIES IN INT_ACTUAL
WHERE A~VGART = 'WF' AND
( B~BWART = '101' OR
B~BWART = '102' ).
*END
LOOP AT INT_ACTUAL.
READ TABLE INT_PGMI WITH KEY NRMIT = INT_ACTUAL-MATNR.
IF SY-SUBRC = 0.
MOVE INT_PRDGRP-PRGRP TO INT_ACTUAL-PRGRP.
MODIFY INT_ACTUAL.
ENDIF.
ENDLOOP.
CHEERS,
SASI