Timeout in select mseg

[Former Member]

Hi,

i try this short report and get allways a runtime-error. Has anyone an idea where i can

in MSEG faster?

REPORT ZTEST MESSAGE-ID ZZ.
*
TABLES: LFA1,   "Lieferantenstamm (allgemeiner Teil)
        LFM1,   "Lieferantenstamm Einkaufssicht
        MSEG.
*
PARAMETERS:       P_MJAHR LIKE MKPF-MJAHR.
SELECT-OPTIONS:   S_MBLNR FOR MSEG-LIFNR.
SELECT-OPTIONS:   S_LIFNR FOR LFA1-LIFNR.
*
DATA: DMBTR     LIKE          MSEG-DMBTR.
DATA: DMBTR_SUM LIKE          MSEG-DMBTR.
*
START-OF-SELECTION.
*
  SELECT * FROM LFA1 WHERE LIFNR IN S_LIFNR.
*
    SELECT SINGLE * FROM LFM1 WHERE LIFNR = LFA1-LIFNR
                                AND EKORG = '1000'.
*
    CLEAR: DMBTR_SUM.
    IF SY-SUBRC = 0.
* Nur wenn auch Einkaufssicht vorhanden
      SELECT DMBTR FROM MSEG INTO DMBTR
                         WHERE MBLNR IN S_MBLNR
                           AND MJAHR = P_MJAHR
                           AND LIFNR = LFA1-LIFNR
                           AND BWART = '101' "Wareneingang
                           AND KZBEW = 'B'.  "Bestellung.
*
        ADD DMBTR TO DMBTR_SUM.
*
      ENDSELECT.
*
    ENDIF.
*
    WRITE: LFA1-LIFNR, DMBTR_SUM.
*
  ENDSELECT.
*
END-OF-SELECTION.
************************************************************************

Thanks.

regards, Dieter

Moderator message - Moved to the correct forum

Edited by: Rob Burbank on May 12, 2009 9:30 AM

[Former Member]

Hi,

i try this short report and get allways a runtime-error. Has anyone an idea where i can

in MSEG faster?

REPORT ZTEST MESSAGE-ID ZZ.
*
TABLES: LFA1,   "Lieferantenstamm (allgemeiner Teil)
        LFM1,   "Lieferantenstamm Einkaufssicht
        MSEG.
*
PARAMETERS:       P_MJAHR LIKE MKPF-MJAHR.
SELECT-OPTIONS:   S_MBLNR FOR MSEG-LIFNR.
SELECT-OPTIONS:   S_LIFNR FOR LFA1-LIFNR.
*
DATA: DMBTR     LIKE          MSEG-DMBTR.
DATA: DMBTR_SUM LIKE          MSEG-DMBTR.
*
START-OF-SELECTION.
*
  SELECT * FROM LFA1 WHERE LIFNR IN S_LIFNR.
*
    SELECT SINGLE * FROM LFM1 WHERE LIFNR = LFA1-LIFNR
                                AND EKORG = '1000'.
*
    CLEAR: DMBTR_SUM.
    IF SY-SUBRC = 0.
* Nur wenn auch Einkaufssicht vorhanden
      SELECT DMBTR FROM MSEG INTO DMBTR
                         WHERE MBLNR IN S_MBLNR
                           AND MJAHR = P_MJAHR
                           AND LIFNR = LFA1-LIFNR
                           AND BWART = '101' "Wareneingang
                           AND KZBEW = 'B'.  "Bestellung.
*
        ADD DMBTR TO DMBTR_SUM.
*
      ENDSELECT.
*
    ENDIF.
*
    WRITE: LFA1-LIFNR, DMBTR_SUM.
*
  ENDSELECT.
*
END-OF-SELECTION.
************************************************************************

Thanks.

regards, Dieter

Moderator message - Moved to the correct forum

Edited by: Rob Burbank on May 12, 2009 9:30 AM

[Former Member]

Hi Deiter,

This might occur because the data in the table must be very large or you are not using the Primary Index.

Please use Seconadary Index and check the ordering of fields.

If then too the problem persists, use ranges.It will definately solve the problem.

Regards,

Sharath

[Former Member]

Hi Dieter,

Always Specify all Primary keys in Where Clause, it helps to get the faster result.

[Sm1tje]

First of all, get rid of all the SELECT ENDSELECT...Performance nightmare!!!

BTW: You might want to consider reading the Performance forum posts first.

Edited by: Micky Oestreich on May 12, 2009 3:02 PM

[Former Member]

In addition to having a triply nested SELECT statement, the SELECT against MSEG is within the SELECT on LFM1, when it should be within the SELECT on LFA1.

Rob

[Former Member]

Use this

*&---------------------------------------------------------------------*
*& Report  ZSKC_SDN
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT ZTEST MESSAGE-ID ZZ.
*
TABLES: LFA1,   "Lieferantenstamm (allgemeiner Teil)
        LFM1,   "Lieferantenstamm Einkaufssicht
        MSEG.
*-----------------------------------------------------------------------*
*                              D A T A
*-----------------------------------------------------------------------*
TYPES : BEGIN OF TY_LIFNR,
          LIFNR TYPE LIFNR,
          SUM   TYPE WERTV9,
        END   OF TY_LIFNR,

        BEGIN OF TY_MSEG,
          MBLNR TYPE MBLNR,
          MJAHR TYPE MJAHR,
          ZEILE TYPE MBLPO,
          LIFNR TYPE ELIFN,
          DMBTR TYPE DMBTR,
        END   OF TY_MSEG.


DATA :  T_LIFNR TYPE SORTED TABLE OF TY_LIFNR WITH UNIQUE KEY LIFNR,
        T_MSEG  TYPE STANDARD TABLE OF TY_MSEG.

DATA :  IS_MSEG TYPE TY_MSEG.
FIELD-SYMBOLS : <FS_LIFNR> TYPE TY_LIFNR.
*-----------------------------------------------------------------------*
*                    S E L E C T I O N   S C R E E N
*-----------------------------------------------------------------------*
PARAMETERS:       P_MJAHR LIKE MKPF-MJAHR.
SELECT-OPTIONS:   S_MBLNR FOR MSEG-LIFNR.
SELECT-OPTIONS:   S_LIFNR FOR LFA1-LIFNR.

*-----------------------------------------------------------------------*
*                  S T A R T   O F   S E L E C T I O N
*-----------------------------------------------------------------------*
START-OF-SELECTION.

* Get the list of vendors.
  SELECT LFA1~LIFNR
  FROM   LFA1 INNER JOIN LFM1
         ON LFA1~LIFNR = LFM1~LIFNR
  INTO   TABLE T_LIFNR
  WHERE  LFA1~LIFNR IN S_LIFNR
  AND    LFM1~EKORG EQ '1000'.

  CHECK SY-SUBRC EQ 0.

* Get Document Segment: Material.
  SELECT MBLNR MJAHR ZEILE LIFNR DMBTR
  INTO   TABLE T_MSEG
  FROM   MSEG
  WHERE  MBLNR IN S_MBLNR
  AND    MJAHR EQ P_MJAHR
  AND    BWART EQ '101'
  AND    KZBEW EQ 'B'.

  CHECK SY-SUBRC EQ 0.

  SORT T_MSEG BY LIFNR.

* Calculate and Add.
  LOOP AT T_MSEG INTO IS_MSEG.
    READ TABLE T_LIFNR ASSIGNING <FS_LIFNR> WITH KEY LIFNR = IS_MSEG-LIFNR
                                                             BINARY SEARCH.
    CHECK SY-SUBRC EQ 0.
    <FS_LIFNR>-SUM = <FS_LIFNR>-SUM + IS_MSEG-DMBTR.
  ENDLOOP.

* Print output.
  LOOP AT T_LIFNR ASSIGNING <FS_LIFNR>.
    WRITE :/ <FS_LIFNR>-LIFNR, <FS_LIFNR>-SUM.
  ENDLOOP.

Use this..This is the tuned version keeping the functionality intact. I assume u will be passing both the vendor number and the Material doc # in the selection screen.

[Former Member]

Hi SKC,

thanks for your answer. I tried it and it's really fast.

I had some problem to read your thread and find the right ASSIGN statement. But now

it works.

thanks again.

regards, Dieter