Skip to Content
author's profile photo
Former Member

Performance Issue

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.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Sep 30, 2005 at 12: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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo
    Former Member
    Sep 30, 2005 at 12: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

    Add comment
    10|10000 characters needed characters exceeded