Solved: slow performance select MSEG MKPF

Former Member · ‎2009 Mar 04

hi all,

i added some checking while doing MIGO, in the checking the i select many times MSEG and MKPF, even i am using the index key, but still need to spend 10-15 minutes as the tables is having millions of record. so i am thinking is there any function module or other way i can use to improve this performance?

your help is much appreciated.

*&---------------------------------------------------------------------*
*&      Form  FM_VALIDATION_315
*&---------------------------------------------------------------------*
*       validation during doing 315 movement type
*----------------------------------------------------------------------*
*  -->  PT_MSEG   structure
*       PT_MKPF   structure
*       PV_VALID  text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM FM_VALIDATION_315 TABLES PT_MSEG
                       USING PT_MKPF TYPE MKPF
                             PV_VALID.

  DATA: LS_MSEG TYPE MSEG,
        LS_MKPF TYPE TY_MKPF,
        LT_REF_MSEG TYPE STANDARD TABLE OF TY_MSEG,
        LS_REF_MSEG TYPE TY_MSEG,
        LT_REPEAT_MKPF TYPE STANDARD TABLE OF TY_MKPF,
        LS_REPEAT_MKPF TYPE TY_MKPF,
        LT_REPEAT_MSEG TYPE STANDARD TABLE OF TY_MSEG,
        LS_REPEAT_MSEG TYPE TY_MSEG,
        LT_CANCEL_MSEG TYPE STANDARD TABLE OF TY_MSEG,
        LS_CANCEL_MSEG TYPE TY_MSEG,
        LV_CANCEL_MBLNR TYPE MBLNR,
        LV_XBLNR TYPE XBLNR1,
        LT_HU_MKPF TYPE STANDARD TABLE OF TY_MKPF,
        LS_HU_MKPF TYPE TY_MKPF,
        LT_HU_MSEG TYPE STANDARD TABLE OF TY_MSEG,
        LS_HU_MSEG TYPE TY_MSEG,
        LV_HU_VALID(1) TYPE C,
        LT_MSEG TYPE STANDARD TABLE OF MSEG.        " INS BY LAUCM 1/8/2008

  PV_VALID = 'X'.

* check if 313 material doc is cancelled
* if cancelled, not allowed to do 315
  IF PV_VALID IS NOT INITIAL.
    SELECT SINGLE MBLNR
      FROM MSEG
      INTO LV_CANCEL_MBLNR
      WHERE SMBLN = GV_REF_MBLNR
        AND SJAHR = GV_REF_MJAHR.
    IF SY-SUBRC = 0.
      PV_VALID = ' '.
      MESSAGE E000(ZM00) WITH GV_REF_MBLNR
                              'Reference material doc is cancelled'(001).
    ENDIF.
  ENDIF.

* check if material and qty is not changed manually
  IF PV_VALID IS NOT INITIAL.
    SELECT MBLNR MJAHR ZEILE LINE_ID BWART MATNR MENGE
      FROM MSEG
      INTO TABLE LT_REF_MSEG
      WHERE MBLNR = GV_REF_MBLNR
        AND MJAHR = GV_REF_MJAHR
        AND XAUTO = SPACE.
    IF SY-SUBRC = 0.
      SORT LT_REF_MSEG BY MBLNR
                          MJAHR
                          LINE_ID.
* del by laucm 1/8/2008
*      LOOP AT PT_MSEG INTO LS_MSEG.
*         READ TABLE LT_REF_MSEG INTO LS_REF_MSEG
*           WITH KEY MBLNR = GV_REF_MBLNR
*                    MJAHR = GV_REF_MJAHR
*                    LINE_ID = LS_MSEG-LINE_ID
*           BINARY SEARCH.
* end del by laucm 1/8/2008
* beg ins by laucm 1/8/2008
      LT_MSEG[] = PT_MSEG[].
      LOOP AT LT_REF_MSEG INTO LS_REF_MSEG.
         READ TABLE LT_MSEG INTO LS_MSEG
           WITH KEY LINE_ID = LS_REF_MSEG-LINE_ID.
* end ins by laucm 1/8/2008
         IF SY-SUBRC = 0.
*          different material
           IF LS_REF_MSEG-MATNR NE LS_MSEG-MATNR.
             PV_VALID = ' '.
             MESSAGE E000(ZM00) WITH LS_REF_MSEG-ZEILE
                                     'Material is different with ref mat doc'(002).
           ENDIF.
*          different qty
           IF LS_REF_MSEG-MENGE NE LS_MSEG-MENGE.
             PV_VALID = ' '.
             MESSAGE E000(ZM00) WITH LS_REF_MSEG-ZEILE
                                     'Item quantity is different with ref mat doc'(003).
           ENDIF.
         ELSE.
*          line item been deleted
           PV_VALID = ' '.
* change by laucm 1/8/2008
*           MESSAGE E000(ZM00) WITH LS_MSEG-ZEILE
*                                   'Line item from ref mat doc is not found'(004).
            MESSAGE E000(ZM00) WITH LS_REF_MSEG-ZEILE
                                    'Line item from ref mat doc is not found'(004).

         ENDIF.
      ENDLOOP.
    ENDIF.
  ENDIF.

* check repeated material slip for movement type 315 material doc
* if it is cancalled then can proceed
  IF PV_VALID IS NOT INITIAL.
    CONCATENATE GV_REF_MBLNR
                '-'
                GV_REF_MJAHR
           INTO LV_XBLNR.

    SELECT MBLNR MJAHR
      FROM MKPF
      INTO TABLE LT_REPEAT_MKPF
*      WHERE BUDAT GT CO_DUMMY_DATE         " del v1.1 laucm 130808
      WHERE BUDAT GE PT_MKPF-BLDAT          " ins v1.1 laucm 130808
        AND XBLNR = LV_XBLNR.
    IF SY-SUBRC = 0.
      SELECT MBLNR MJAHR ZEILE LINE_ID BWART MATNR MENGE
        FROM MSEG
        INTO TABLE LT_REPEAT_MSEG
        FOR ALL ENTRIES IN LT_REPEAT_MKPF
        WHERE MBLNR = LT_REPEAT_MKPF-MBLNR
          AND MJAHR = LT_REPEAT_MKPF-MJAHR
          AND XAUTO = SPACE.

      IF SY-SUBRC = 0.
        SORT LT_REPEAT_MSEG BY MBLNR
                               MJAHR.
        SELECT MBLNR MJAHR ZEILE LINE_ID BWART MATNR MENGE SMBLN SJAHR SMBLP
           FROM MSEG
           INTO TABLE LT_CANCEL_MSEG
           FOR ALL ENTRIES IN LT_REPEAT_MSEG
           WHERE SMBLN = LT_REPEAT_MSEG-MBLNR
             AND SJAHR = LT_REPEAT_MSEG-MJAHR
             AND SMBLP = LT_REPEAT_MSEG-ZEILE.
        IF SY-SUBRC = 0.
          SORT LT_CANCEL_MSEG BY SMBLN
                                 SJAHR
                                 SMBLP.

          LOOP AT LT_REPEAT_MKPF INTO LS_REPEAT_MKPF.
            READ TABLE LT_REPEAT_MSEG INTO LS_REPEAT_MSEG
              WITH KEY MBLNR = LS_REPEAT_MKPF-MBLNR
                       MJAHR = LS_REPEAT_MKPF-MJAHR
              BINARY SEARCH.
            IF SY-SUBRC = 0.
*              IF LS_REPEAT_MSEG-BWART = GV_MVMT_GR.
              IF LS_REPEAT_MSEG-BWART IN RA_MVMT_GR.
*                check if 315 material is cancelled
                READ TABLE LT_CANCEL_MSEG INTO LS_CANCEL_MSEG
                  WITH KEY SMBLN = LS_REPEAT_MSEG-MBLNR
                           SJAHR = LS_REPEAT_MSEG-MJAHR
                           SMBLP = LS_REPEAT_MSEG-ZEILE
                  BINARY SEARCH.
                IF SY-SUBRC <> 0.
*                  if not cancalled, error
                  PV_VALID = ' '.
                  MESSAGE E000(ZM00) WITH GV_REF_MBLNR
                                          'Ref material doc is already processed'(005).
                ENDIF.
              ENDIF.
            ENDIF.
          ENDLOOP.
        ELSE.
*           if not cancalled, error
          PV_VALID = ' '.
          MESSAGE E000(ZM00) WITH GV_REF_MBLNR
                                  'Ref material doc is already processed'(005).
        ENDIF.
      ENDIF.
    ENDIF.
  ENDIF.
ENDFORM.                    " FM_VALIDATION_315

Added Code tags and moved to correct forum

Edited by: Rob Burbank on Mar 3, 2009 9:46 PM

Former Member · ‎2009 Mar 04

Hi Mingo,

When you need to use internal tables that will contain large number of data then it is recommended to use hashed internal table. Tables Mseg & Mkpf contain large number of data.

1) So instead of using standard internal table use hashed internal tabe.

2) Also it is the best to use Field -Symbols instead of work areas.

Do the necessary changes mentioned above to your code and the performance will definitely improve.

Also after doing the changes mentioned above do runtime analysis (SE30) and SQL Trace (ST05). You will get to know where the problem exactly is & why the program requires such a long time to execute.

Hope this helps you.

Vinay

Former Member · ‎2009 Mar 04

You seem to be using indexes effectively. The only problem I see is here:

READ TABLE LT_MSEG INTO LS_MSEG
           WITH KEY LINE_ID = LS_REF_MSEG-LINE_ID.

Since you have not used the BINARY SEARCH addition with this standard internal table, it will slow it down.

Rob

Former Member · ‎2009 Mar 04

Hi,

Apart from binary search you have to include some more fields in sort as these fields are being used in for all entries clause.

SORT LT_REPEAT_MSEG BY MBLNR MJAHR ZEILE.

Sort LT_REPEAT_MKPF by MBLNR MJAHR.

Thanks,

Krishna..

Former Member · ‎2009 Mar 04

Hi Mingo,

When you need to use internal tables that will contain large number of data then it is recommended to use hashed internal table. Tables Mseg & Mkpf contain large number of data.

1) So instead of using standard internal table use hashed internal tabe.

2) Also it is the best to use Field -Symbols instead of work areas.

Do the necessary changes mentioned above to your code and the performance will definitely improve.

Also after doing the changes mentioned above do runtime analysis (SE30) and SQL Trace (ST05). You will get to know where the problem exactly is & why the program requires such a long time to execute.

Hope this helps you.

Vinay

Former Member · ‎2009 Mar 04


 SORT LT_REF_MSEG BY MBLNR
                          MJAHR
                          LINE_ID.
* del by laucm 1/8/2008
*      LOOP AT PT_MSEG INTO LS_MSEG.
*         READ TABLE LT_REF_MSEG INTO LS_REF_MSEG
*           WITH KEY MBLNR = GV_REF_MBLNR
*                    MJAHR = GV_REF_MJAHR
*                    LINE_ID = LS_MSEG-LINE_ID
*           BINARY SEARCH.
* end del by laucm 1/8/2008
* beg ins by laucm 1/8/2008
      LT_MSEG[] = PT_MSEG[].
      LOOP AT LT_REF_MSEG INTO LS_REF_MSEG.
         READ TABLE LT_MSEG INTO LS_MSEG
           WITH KEY LINE_ID = LS_REF_MSEG-LINE_ID.

The issue is related to nonlinear coding, I have several blogs on that topic, with a tool

which would immediately identify the problem.

The innser operation inside a loop must be fast operation, use binary search but even better

use a sorted or hashed table.

=> So the inner table MUST be sorted, to sort the outer table is a waste time!

I would recommend you to check whether you can change the logic into


      LOOP AT lt_mseg INTO ls_mseg.
         READ TABLE lt_ref_mseg INTO ls_ref_mseg WITH TABLE KEY ...

Use the external table as the loop table and don't touch it, mayeb it has some kind of order

which helps outside.

Use a sorted table inside your routine and define a unique table key!

Equivalent but more combersome, use a standard table and sort it and use binary search.

Siegfried

Former Member · ‎2009 Mar 04

Hi Mingo Lau

I don't know issue is solve or not, as per of my understanding u r facing problem with SELECT on mseg.

for that there is basic solution try to find out Material Doc Year (MJHAR) some how, and try to not to use for all entries .

please clarify the problem so we will try to solve it better.

Former Member · ‎2009 Mar 05

very thanks for your advise...

but i am still not yet test it because i have some other issue have to be settled before this..

i will try to follow your advice and test, once i get the result i will update you all again..

thanks a lot

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

slow performance select MSEG MKPF