Former Member

Dec 20, 2007 at 12:37 PM

Need to Improve pefromance for select statement using MSEG table

79 Views

Hi all,

We are using a select statement using MSEG table

which takes a very long time to run the program which is scheduled in back ground.

Please see the history below.;

1) Previously this program was using SELECT-ENDSELECT statement inside the loop i.e.

LOOP AT I_MCHB.

To get Material Doc. Details

SELECT MBLNR

MJAHR

ZEILE INTO (MSEG-MBLNR,MSEG-MJAHR,MSEG-ZEILE)

UP TO 1 ROWS

FROM MSEG

WHERE CHARG EQ I_MCHB-CHARG

AND MATNR EQ I_MCHB-MATNR

AND WERKS EQ I_MCHB-WERKS

AND LGORT EQ I_MCHB-LGORT.

ENDSELECT.

Endloop.

The program was taking 1 hr for 20 k data

2)The above statement was replaced by ALL ENTRIES to remove the SELECT-ENDSELECT from the loop.

***GET MATERIAL DOC NUMBER AND FINANCIAL YEAR DETAILS FROM MSEG TABLE

SELECT MBLNR

MJAHR

ZEILE

MATNR

CHARG

WERKS

LGORT

INTO TABLE I_MSEG

FROM MSEG

FOR ALL ENTRIES IN I_MCHB

WHERE CHARG EQ I_MCHB-CHARG

AND MATNR EQ I_MCHB-MATNR

AND WERKS EQ I_MCHB-WERKS

AND LGORT EQ I_MCHB-LGORT.

3)After getting the further technical analysis from BASIS team , And with the suggestion to optimize the program by changing the INDEX RANGE SCAN to

MSEG~M.

SELECT MBLNR

MJAHR

ZEILE

MATNR

CHARG

WERKS

LGORT

INTO TABLE I_MSEG

FROM MSEG

FOR ALL ENTRIES IN I_MCHB

WHERE MATNR EQ I_MCHB-MATNR

AND WERKS EQ I_MCHB-WERKS

AND LGORT EQ I_MCHB-LGORT.

At present the program is taking 3 to 4 hrs in back ground .

The table is complete table scan using index

MSEG~M.

Please suggest to improve the performance of this

many many thanks

deepak