Performance while read MSEG

[murugeshkumar_ramasamy]

Hi Gurs,

Kindly guide me ....

I developed a report for Age wise stock analysis.. It is taking so much time when running in production server because of so many records in MSEG Table ...

Give the alternate if any ??

This is my code..

SELECT MSEG_{MBLNR MSEG}MJAHR MSEG_{ZEILE MSEG}WERKS MSEG_{MATNR MSEG}BWART MSEG_{MENGE MKPF}BUDAT

INTO TABLE IT_TRANS

FROM MSEG AS MSEG JOIN MKPF AS MKPF

ON MSEG_{MANDT = MKPF}MANDT AND

MSEG_{MBLNR = MKPF}MBLNR AND

MSEG_{MJAHR = MKPF}MJAHR

FOR ALL ENTRIES IN T_AGE_TMP

WHERE MSEG~MATNR EQ T_AGE_TMP-MATNR

AND MSEG~WERKS EQ PR_BWKEY

AND MSEG~LGORT IN SO_LGORT

AND MSEG~BWART IN BWART

AND MSEG~SHKZG EQ 'S'

AND MKPF~BUDAT IN BUDAT.

Thanks & Regards,

Murugesh R

[h_senden2]

MSEG is such a big table, you have to use the indexes (primary or secondary) to read it. If there is no index for your use you can create your own.

regards,

Hans

[Former Member]

Hi

U should check wich indexes are active your system for both tables MSEG and MKPF, probably your selection can't use any index.

Max

[murugeshkumar_ramasamy]

Thanx... For your immediate response...

The MSEG~M index is used in my query.. ...

Thanks and Regards,

Murugesh R

[murugeshkumar_ramasamy]

hi gurus...

Kindly Guide me ... if any alternate is there ?

Thanks & Regards,

Murugesh R

[Former Member]

SELECT MSEG_{MBLNR MSEG}MJAHR MSEG_{ZEILE MSEG}WERKS MSEG_{MATNR MSEG}BWART MSEG_{MENGE MKPF}BUDAT

INTO TABLE IT_TRANS

FROM MSEG AS MSEG JOIN MKPF AS MKPF

ON MSEG_{MANDT = MKPF}MANDT AND

MSEG_{MBLNR = MKPF}MBLNR AND

MSEG_{MJAHR = MKPF}MJAHR

FOR ALL ENTRIES IN T_AGE_TMP

WHERE MSEG~MATNR EQ T_AGE_TMP-MATNR

AND MSEG~WERKS EQ PR_BWKEY

AND MSEG~LGORT IN SO_LGORT

AND MSEG~BWART IN BWART

AND MSEG~SHKZG EQ 'S'

AND MKPF~BUDAT IN BUDAT.

1.Before this above select,

Sort and delete the for all entries table by matnr .

You should pass only unique records to it.

If required,have a help table .

[murugeshkumar_ramasamy]

Thanx Raj....

Before the select statement ... I check the for all entries table is not initial and Sort the table by matnr and delete the adjacent duplicates ...

Performance issues for material type SCIN only it have lot of entries...

What is help table? Kindly Explain...

Thanks & Regards,

Murugesh R

[Former Member]

Dear Muragesh,

U can use READ statement with BINARY SEARCH.

check the internal table shoiuld not be initial.

Best Regards,

Flavya

[murugeshkumar_ramasamy]

Thanx Flavya...

My problem is above mentioned query is taking long time...

Any alternate to get material document No...

Regards,

Murugesh R

[Former Member]

Hi ,

what are the fields u are using in the index MSEG~M ?

regards

Prabhu

[murugeshkumar_ramasamy]

Dear Prabhu,

Index MSEG M ( its delivered by SAP) has below fields,

MANDT Client

MATNR Material Number

WERKS Plant

LGORT Storage location

BWART Movement Type (Inventory Management)

SOBKZ Special Stock Indicator

Regards,

Murugesh R

[Former Member]

With whatever you had mentioned above, I don't think of any other way of improving performance.

At most, you can make sure user enters LGORT on screen, if user agrees, you may make LGORT and BWART as mandatory field.

Also make sure, you index M is active at database level. If it is not active at databse level, you will get warning message displayed while viewing index.

Regards,

Mohaiyuddin

[Former Member]

Hi,

You can reduce DB time by removing FOR ALL ENTRIES clause in select query ,and use Ranges to delete IT_TRANS entries based on MATNR i.e. use a delete statement

[Former Member]

a JOIN is not always the better way to retrieve records...........maybe you should try it with 2 selects

[murugeshkumar_ramasamy]

Thanx for all for your valuable guidelines..

Regards,

Murugesh R