Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Need to Improve pefromance for select statement using MSEG table

Former Member
0 Kudos
166

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

1 ACCEPTED SOLUTION

former_member194613
Active Contributor
0 Kudos
85

The benchmark should be the join, and I can not see how any of your solutions can be faster than the join

SELECT .....

INTO TABLE ....

UP TO 1 ROWS

FROM mchb as a

INNER JOIN mseg as b

ON amatnr EQ bmatnr

AND awerks EQ bwerks

AND algort EQ blgort

And acharg EQ bcharg

WHERE a~ ....

The WHERE condition must come from the select on MCHB, the field list from the total results

you want.

If you want to compare, must compare your solutions plus the select to fill I_MCHB.

Siegfried

Edited by: Siegfried Boes on Dec 20, 2007 2:28 PM

6 REPLIES 6

Former Member
0 Kudos
85

hi

You are not at all using any key fields in select statement’s where condition. Key field for MSEG are MBLNR,MJAHR and ZEILE.

Create the secondary index for the fields which are using in where condition(MATNR, WERKS and LGORT).

Hope it will give some better performance

Reward if it is useful.

Thanks

Siva kumar

former_member194613
Active Contributor
0 Kudos
86

The benchmark should be the join, and I can not see how any of your solutions can be faster than the join

SELECT .....

INTO TABLE ....

UP TO 1 ROWS

FROM mchb as a

INNER JOIN mseg as b

ON amatnr EQ bmatnr

AND awerks EQ bwerks

AND algort EQ blgort

And acharg EQ bcharg

WHERE a~ ....

The WHERE condition must come from the select on MCHB, the field list from the total results

you want.

If you want to compare, must compare your solutions plus the select to fill I_MCHB.

Siegfried

Edited by: Siegfried Boes on Dec 20, 2007 2:28 PM

Former Member
0 Kudos
85

Hi,

make sure that the order of the fetching fields and the where clause fields are in the same order in which they appear in the data base table, which will imporves the performance.

ans also search if any index exists for that table, which will be useful for your where clause, if so maintain that order.

Reward if useful.

Thanks,

Sreeram.

Edited by: Sreeram Prasad on Dec 20, 2007 2:31 PM

Former Member
0 Kudos
85

Try using unique material number, plant and storage location combinations in the FOR ALL ENTRIES clause. Take a look at the code below.

DATA i_mchb_tmp LIKE TABLE OF i_mchb.

IF NOT i_mchb[] IS INITIAL.

i_mchb_tmp[] = i_mchb[].

SORT i_mchb_tmp BY matnr werks lgort.

DELETE ADJACENT DUPLICATES FROM i_mchb_tmp

COMPARING matnr werks lgort.

SELECT mblnr

mjahr

zeile

matnr

charg

werks

lgort

FROM mseg

INTO TABLE i_mseg

FOR ALL ENTRIES IN i_mchb_tmp

WHERE matnr EQ i_mchb_tmp-matnr

AND werks EQ i_mchb_tmp-werks

AND lgort EQ i_mchb_tmp-lgort.

ENDIF.

former_member194613
Active Contributor
0 Kudos
85

> make sure that the order of the fetching fields and the where clause fields are in the same order in

> which they appear in the data base table, which will imporves the performance.

normally this has zero impact

Former Member
0 Kudos
85

Hi Deepak,

You are using index M which is as good as it gets. When running your program you can see the status in SM66. You should run this program and monitor it every 30 minutes. I think your performance issue may lie elsewhere in your program. Then we will need to see more of your program to give you performance tips.

Hope this helps.

Filler