Skip to Content
author's profile photo Former Member
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 a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on 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 a 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
    Former Member
    Posted on 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 a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.