Skip to Content
0
Former Member
Mar 04, 2009 at 01:58 AM

slow performance select MSEG MKPF

489 Views

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