topic Re: Performace issue while fetching data from MSEG table in Application Development and Automation Discussions

Performace issue while fetching data from MSEG table

Former Member — Wed, 17 Nov 2010 13:24:46 GMT

Hi All,

I am supposed to fetch ERFMG values from mseg table corresponding to a particular material.

sum those ERFMG values and display it in a flat file. Below is the query to fetch the data from mseg. zchmatchcode is a table which will contain match code and corresponding material nuumbers.

select mseg~erfmg mseg~mjahr mseg~matnr mseg~werks into*

corresponding fields of table itab_mseg

from mseg

inner join zchmatchcode on mseg_{matnr = zchmatchcode}matnr

where mseg~matnr = wa_mara-matnr

and ( mseg_{mjahr = var3 or mseg}mjahr = var2 or mseg_{mjahr = var1 or mseg}mjahr = var )

and mseg~werks eq 'CH03'

and ( mseg~bwart = '301' or

mseg~bwart = '291' or

mseg~bwart = '261' or

mseg~bwart = '262' or

mseg~bwart = '601' or

mseg~bwart = '602' or

mseg~bwart = '633' or

mseg~bwart = '641' or

mseg~bwart = '643' or

mseg~bwart = '644' or

mseg~bwart = '645' or

mseg~bwart = '921' or

mseg~bwart = '933' ).

and below given is the calcuation for adding erfmg values and placing it in the output field.

loop at itab_mseg.

if itab_mseg-matnr = wa_mara-matnr.

if sy-tabix EQ 1.

v_matnr = itab_mseg-matnr.

v_year = itab_mseg-mjahr.

endif.

if v_matnr = itab_mseg-matnr and v_year = itab_mseg-mjahr.

sum = sum + itab_mseg-erfmg.

if v_year EQ var.

it_final-MGVBR = sum.

elseif v_year EQ var1.

it_final-MGVBR1 = sum.

elseif v_year EQ var2.

it_final-mgvbr2 = sum.

elseif v_year EQ var3.

it_final-mgvbr3 = sum.

endif.

else.

clear sum.

v_matnr = itab_mseg-matnr.

v_year = itab_mseg-mjahr.

sum = sum + itab_mseg-erfmg.

if v_year EQ var.

it_final-MGVBR = sum.

elseif v_year EQ var1.

it_final-MGVBR1 = sum.

elseif v_year EQ var2.

it_final-mgvbr2 = sum.

elseif v_year EQ var3.

it_final-mgvbr3 = sum.

endif.

endloop.

I am getting the output without any issues.. The problem is .. this is taking a lot of time. Can someone suggest me a solution for this problem.. If there is a function module to do the above specified process?

I also checked in MB51 and the associated Standard program(because similar kind of calculation is happening in that transaction) but I found it difficult to figure it out..

Awaiting an early solution for my problem :).

Thanks in Advance.

Sriram

Re: Performace issue while fetching data from MSEG table

Former Member — Wed, 17 Nov 2010 14:20:53 GMT

Hi Sriram

Did you try to check how the SELECT statement works using ST05 transaction ? it could help you. As you specifiy MATNR and WERKS into the Where condition an index of MSEG should be used ...

I don't understand why you use the ZCHMATCHCODE table into your SELECT as you don't seem to use any field from the second table. If you just want to check that material code exists in this second table, you can do it in another way. Maybe the long time is due to the JOIN but I can't help you as it is a specific table.

Into your LOOP...ENDLOOP you could use AT NEW to determine a change of material but be carful with your internal table.

Are you sure you want to add quantity even for 262 or 602 ? These codes reverse 261 and 601 and are usually used to subtract...

Re: Performace issue while fetching data from MSEG table

Former Member — Wed, 17 Nov 2010 14:50:44 GMT

Moderator message - Welcome to SCN Please see before posting in the Performance and Tuning forum And please do not ask for "early" solutions. Everyone's problem is important. I see no problems with the code you have posted. Is it called in a LOOP? Please read [Rules of Engagement|https://wiki.sdn.sap.com/wiki/display/HOME/RulesofEngagement], and [Asking Good Questions in the Forums to get Good Answers|/people/rob.burbank/blog/2010/05/12/asking-good-questions-in-the-forums-to-get-good-answers] before posting again. Rob

Re: Performace issue while fetching data from MSEG table

Former Member — Wed, 17 Nov 2010 15:04:20 GMT

Hi,

sorry for adding "early" in my query.. I apologize for that. That query is not called in a loop. Here it is taking lot of time to get me the output and I am stuck as in how to proceed further

Regards,

Sriram

Re: Performace issue while fetching data from MSEG table

Former Member — Wed, 17 Nov 2010 15:12:50 GMT

Hi,

I would certainly check that transaction. I would be glad if you could suggest me how to index as i am not aware of how to use it.

zmatchcode is a table from where the matnrs are fetched into the program(at the start) and matnr from mseg is a foregin key to zmatchcode matnr and it is not allowing me to get ERFMG values if the values are same. that is the reason i used inner join to get the ERFMG even if i dont use that table anywhere else in my query.

I would try using that AT NEW in my program.

261 and 601 are movement types right and the query should select ERFMG values for all the movement type specified in the query and that is the requirement given to me

I really appreciate your help on this.. thanks a lot

Re: Performace issue while fetching data from MSEG table

Former Member — Wed, 17 Nov 2010 15:32:21 GMT


>  That query is not called in a loop.

Since there is nothing wrong with the code you have posted, you really have to check how it is being called. It may not be directly called within a loop, but is it in a form that is called from within a loop.

Additionally, you have to run a trace (ST05) and/or a runtime analysis (SE30). Please do that and post the results before reponding again to this thread.

Rob

Re: Performace issue while fetching data from MSEG table

ravi_lanjewar — Thu, 18 Nov 2010 05:17:39 GMT

Hi,

1) Don't used the join operation on MSEG table.

2) Read data first from Mara or your zchmatchcode (i.e. you can read data from using join on mara and zchmatchcode table.)

3) data read in point 2 used to fetch data from Mseg using for all entries.

4) Check table is not empty before using for all entries

5) consodidate as per your requirement.

Try above step, It wil help you.

Re: Performace issue while fetching data from MSEG table

Former Member — Fri, 19 Nov 2010 14:58:09 GMT

Hi sriram,

Make sure there is a index base on matnr in table zchmatchcode

and create secondary index for MSEG base on this below field

matnr

mjahr

werks

bwart

and you can change your where condition to


and mseg~mjahr in (var3,var2,var1,var)
and mseg~werks eq 'CH03'
and mseg~bwart in ('291','261'.........

Regards

Fernand

Re: Performace issue while fetching data from MSEG table

Former Member — Thu, 03 Mar 2011 10:35:27 GMT

Hi,

To check the secondary index selected by your query at runtime, you can use ST05/SE30 as suggested by Rob, in addition to this, the query optimizer, at times has problems in selecting a particular index inspite of order of fields in your query, this usually happens when there are too many secondary index created. you can make your query select a particular index, to do this you can code the below code just to check the performance as it is not recomended to use such query with specific index:

select <field names> from mseg

into <internal table>

%_HINTS ORACLE 'INDEX("MSEG" "MSEG~ID3")'.

here MSEG is the table name and ID3 is the secondary index name.

In addition to this, you can also ask your BASIS admin to check the optimizer settings

hope this helps.

Re: Performace issue while fetching data from MSEG table

Former Member — Thu, 03 Mar 2011 10:50:34 GMT

Hi,

please check that MSEG index M is active in your system!

Regards,

Klaus