topic Performance Issue in Application Development and Automation Discussions

Performance Issue

Former Member — Fri, 30 Sep 2005 12:27:43 GMT

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%.

**<<<++Start of changes NEX4CRH 08.08.2005 DV2K922632 CR17140

*SELECT

PGMI_{PRGRP MSEG}MATNR MSEG_{ERFMG MKPF}VGART MKPF~BUDAT

*MSEG_{WERKS MSEG}SHKZG

MSEG_{MBLNR MSEG}MJAHR MKPF~MJAHR
FROM PGMI INNER JOIN MSEG
ON ( PGMI_{NRMIT = MSEG}matnr ) INNER JOIN MKPF ON
( MSEG_{MBLNR = MKPF}MBLNR AND MSEG_{MJAHR = MKPF}MJAHR ) 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'.

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.

Re: Performance Issue

RichHeilman — Fri, 30 Sep 2005 12:35:50 GMT

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

Re: Performance Issue

Former Member — Fri, 30 Sep 2005 12:48:31 GMT

Hi,

try this one

SELECT PRGRP NRMIT FROM PGMI INTO TABLE INT_PGMI

WHERE PRGRP IN S_PRDGRP.

modified

SELECT A_{MBLNR A}VGART A_{BUDAT A}MJAHR

B_{MATNR B}WERKS B_{ERFMG B}BWART B~SHKZG

FROM BKPF AS A INNER JOIN

MSEG AS B ON A_{MBLNR = B}MBLNR AND

A_{MJAHR = B}MJAHR

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

Re: Performance Issue

ChristianFi — Fri, 30 Sep 2005 13:23:39 GMT

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