performance issue with MSEG table

[Former Member]

hi all,

when I am fetching data from MSEG table it is taking lot of time. I created a secondary index with fields

MANDT Client

MBLNR Number of Material Document

MATNR Material Number

CHARG Batch Number

AUFNR Order Number

my program is like this

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

*SELECT-OPTIONS : s_aufnr like afko-aufnr.

SELECT-OPTIONS: s_date FOR afko-gltrp. "finish date

SELECTION-SCREEN END OF BLOCK b1.

DATA : BEGIN OF i_prod OCCURS 0,

aufnr LIKE afko-aufnr,

gstrp LIKE afko-gstrp,

gltrp LIKE afko-gltrp,

END OF i_prod.

DATA : BEGIN OF i_mseg OCCURS 0,

mblnr LIKE mseg-mblnr, "mat doc.no

mjahr LIKE mseg-mjahr,

zeile LIKE mseg-zeile,

bwart LIKE mseg-bwart, "movement type

matnr LIKE mseg-matnr,

werks LIKE mseg-werks,

lgort LIKE mseg-lgort,

charg LIKE mseg-charg,

menge LIKE mseg-menge,

meins LIKE mseg-meins,

aufnr LIKE mseg-aufnr,

END OF i_mseg.

DATA : BEGIN OF i_final OCCURS 0,

aufnr LIKE afko-aufnr,

gstrp LIKE afko-gstrp,

gltrp LIKE afko-gltrp,

mblnr LIKE mseg-mblnr, "mat doc.no

bwart LIKE mseg-bwart, "movement type

matnr LIKE mseg-matnr,

werks LIKE mseg-werks,

lgort LIKE mseg-lgort,

charg LIKE mseg-charg,

menge LIKE mseg-menge,

meins LIKE mseg-meins,

END OF i_final.

START-OF-SELECTION.

SELECT aufnr gstrp gltrp FROM afko INTO TABLE i_prod WHERE gltrp IN s_date.

IF sy-subrc EQ 0.

SELECT mblnr

mjahr

zeile

bwart

matnr

werks

lgort

charg

menge

meins

aufnr FROM mseg INTO TABLE i_mseg

FOR ALL ENTRIES IN i_prod WHERE aufnr = i_prod-aufnr.

• %_HINTS ORACLE 'INDEX("MSEG" "ZPD")'.

.

ELSE.

WRITE : / 'No production orders found within the date'.

ENDIF.

*move data to i_final internal table

LOOP AT i_prod.

i_final-aufnr = i_prod-aufnr.

i_final-gstrp = i_prod-gstrp.

i_final-gltrp = i_prod-gltrp.

READ TABLE i_mseg WITH KEY aufnr = i_prod-aufnr.

IF sy-subrc EQ 0.

i_final-mblnr = i_mseg-mblnr.

i_final-bwart = i_mseg-bwart.

i_final-matnr = i_mseg-matnr.

i_final-werks = i_mseg-werks.

i_final-lgort = i_mseg-lgort.

i_final-charg = i_mseg-charg.

i_final-menge = i_mseg-menge.

i_final-meins = i_mseg-meins.

ENDIF.

APPEND i_final.

CLEAR i_final.

ENDLOOP.

then I am displaying i_final with FM ALV.

but how to increase the performance & reduce the fetching time from mseg.

watching

[Former Member]

hi all,

when I am fetching data from MSEG table it is taking lot of time. I created a secondary index with fields

MANDT Client

MBLNR Number of Material Document

MATNR Material Number

CHARG Batch Number

AUFNR Order Number

my program is like this

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

*SELECT-OPTIONS : s_aufnr like afko-aufnr.

SELECT-OPTIONS: s_date FOR afko-gltrp. "finish date

SELECTION-SCREEN END OF BLOCK b1.

DATA : BEGIN OF i_prod OCCURS 0,

aufnr LIKE afko-aufnr,

gstrp LIKE afko-gstrp,

gltrp LIKE afko-gltrp,

END OF i_prod.

DATA : BEGIN OF i_mseg OCCURS 0,

mblnr LIKE mseg-mblnr, "mat doc.no

mjahr LIKE mseg-mjahr,

zeile LIKE mseg-zeile,

bwart LIKE mseg-bwart, "movement type

matnr LIKE mseg-matnr,

werks LIKE mseg-werks,

lgort LIKE mseg-lgort,

charg LIKE mseg-charg,

menge LIKE mseg-menge,

meins LIKE mseg-meins,

aufnr LIKE mseg-aufnr,

END OF i_mseg.

DATA : BEGIN OF i_final OCCURS 0,

aufnr LIKE afko-aufnr,

gstrp LIKE afko-gstrp,

gltrp LIKE afko-gltrp,

mblnr LIKE mseg-mblnr, "mat doc.no

bwart LIKE mseg-bwart, "movement type

matnr LIKE mseg-matnr,

werks LIKE mseg-werks,

lgort LIKE mseg-lgort,

charg LIKE mseg-charg,

menge LIKE mseg-menge,

meins LIKE mseg-meins,

END OF i_final.

START-OF-SELECTION.

SELECT aufnr gstrp gltrp FROM afko INTO TABLE i_prod WHERE gltrp IN s_date.

IF sy-subrc EQ 0.

SELECT mblnr

mjahr

zeile

bwart

matnr

werks

lgort

charg

menge

meins

aufnr FROM mseg INTO TABLE i_mseg

FOR ALL ENTRIES IN i_prod WHERE aufnr = i_prod-aufnr.

• %_HINTS ORACLE 'INDEX("MSEG" "ZPD")'.

.

ELSE.

WRITE : / 'No production orders found within the date'.

ENDIF.

*move data to i_final internal table

LOOP AT i_prod.

i_final-aufnr = i_prod-aufnr.

i_final-gstrp = i_prod-gstrp.

i_final-gltrp = i_prod-gltrp.

READ TABLE i_mseg WITH KEY aufnr = i_prod-aufnr.

IF sy-subrc EQ 0.

i_final-mblnr = i_mseg-mblnr.

i_final-bwart = i_mseg-bwart.

i_final-matnr = i_mseg-matnr.

i_final-werks = i_mseg-werks.

i_final-lgort = i_mseg-lgort.

i_final-charg = i_mseg-charg.

i_final-menge = i_mseg-menge.

i_final-meins = i_mseg-meins.

ENDIF.

APPEND i_final.

CLEAR i_final.

ENDLOOP.

then I am displaying i_final with FM ALV.

but how to increase the performance & reduce the fetching time from mseg.

watching

[Former Member]

hi,

Use the secondary index created only with MANDAT and AUFNR field ... there shouldn't be any other fields apart from these ... to increase the performance ...

Regards,

Santosh

[Former Member]

hi uday,

There could be various reasons for performance issue with MSEG.

1) database statistics of tables and indexes are not upto date.

because of this wrong index is choosen during the execution.

2) Improper indexes, because there is no indexes with the fields mentioned in the WHERE clause of the statement. Because of this reason, CBO would have choosen wrong index and did a range scan.

3) Optimizer bug in oracle.

4) Size of table is very huge, archive.

Better switch on ST05 trace before you run this statements, so it will give more detailed information, where exactly time being spent during the execution.

reward if usful,

Regards,

Amit

[Former Member]

hi

den do one thing don't use any condition

and den use loop tp fetch data.

SELECT mblnr

mjahr

zeile

bwart

matnr

werks

lgort

charg

menge

meins

aufnr FROM mseg INTO TABLE i_mseg.

loop at ra.

loop at i_mseg ....

endloop.

endloop.

[Former Member]

if we are not using where condition in select statement will it increases the performance.

[Former Member]

means in the secondary index , there should be only fields which we are using in the where clause.

here , the index should contain only MANDT & AUFNR..

am i correct?

[former_member435013]

Hi,

I assume basics like hardware DB and statistics are ok.

i)

Create an non-unique index with only two key fields mandt and aufnr. I think there is even a sap note in that way.

ii)

Think about archiving, perhaps with PBS. That is a third party product and allows short residence time.

regards

Walter Habich