Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Performance Issue

Former Member
0 Kudos

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.

3 REPLIES 3

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos

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

0 Kudos

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

Former Member
0 Kudos

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