topic Re: Performance while read MSEG in Application Development and Automation Discussions

Performance while read MSEG

murugeshkumar_ramasamy — Fri, 28 Nov 2008 10:37:55 GMT

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

Re: Performance while read MSEG

h_senden2 — Fri, 28 Nov 2008 10:44:08 GMT

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

Re: Performance while read MSEG

Former Member — Fri, 28 Nov 2008 10:45:10 GMT

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

Max

Re: Performance while read MSEG

murugeshkumar_ramasamy — Fri, 28 Nov 2008 10:50:14 GMT

Thanx... For your immediate response...

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

Thanks and Regards,

Murugesh R

Re: Performance while read MSEG

murugeshkumar_ramasamy — Fri, 28 Nov 2008 10:54:19 GMT

hi gurus...

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

Thanks & Regards,

Murugesh R

Re: Performance while read MSEG

Former Member — Fri, 28 Nov 2008 14:28:29 GMT

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 .

Re: Performance while read MSEG

murugeshkumar_ramasamy — Sat, 29 Nov 2008 04:27:19 GMT

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

Re: Performance while read MSEG

Former Member — Mon, 01 Dec 2008 13:10:50 GMT

Dear Muragesh,

U can use READ statement with BINARY SEARCH.

check the internal table shoiuld not be initial.

Best Regards,

Flavya

Re: Performance while read MSEG

murugeshkumar_ramasamy — Tue, 02 Dec 2008 04:11:00 GMT

Thanx Flavya...

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

Any alternate to get material document No...

Regards,

Murugesh R

Re: Performance while read MSEG

Former Member — Tue, 02 Dec 2008 07:40:12 GMT

Hi ,

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

regards

Prabhu

Re: Performance while read MSEG

murugeshkumar_ramasamy — Tue, 02 Dec 2008 08:09:40 GMT

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

Re: Performance while read MSEG

Former Member — Tue, 02 Dec 2008 08:19:54 GMT

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

Re: Performance while read MSEG

Former Member — Tue, 02 Dec 2008 10:28:36 GMT

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

Re: Performance while read MSEG

Former Member — Tue, 02 Dec 2008 15:56:12 GMT

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

Re: Performance while read MSEG

murugeshkumar_ramasamy — Tue, 23 Dec 2008 09:45:19 GMT

Thanx for all for your valuable guidelines..

Regards,

Murugesh R