MB51 Performance problem

[Former Member]

Hello,

I am trying to tune the performance of MB51 transaction. user will run the transactions with different varaints.

Apart from the standard seconday indexes there is only one custom secondary index exist on MSEG which is AUFNR field.

I tried to reduce the output fields in V_MMIM_REP_CUST view and switched of the coloring of quantities V_MMIM_REP_PRINT view.

There is a standard secondary index M ( Material,Plant,Storage Location, Movement Type etcc..) is there but user varaints has missing material nubmer. For ex they may run with entering Plant and Storage Location.

Can I create a secodnary index only with fields Plant and Storage Location? Even if these two fields are already part of index M, can we create a index with partial fields of existing index?

Note : We don't have archiving and there are more than 16million entries exist in MSEG table.

Please help me with the ways to Improve the performance.

Thanks,

MPREDDY

[ravi_lanjewar]

Hi,

You should check the which index is currently using in your SQL in transaction ST05

It is not issue but keep the following thing to create secondary index in mind.

1) Creating secondary index is storage cost effective. Check weather it is reusable or not.

2) While creating index check table in se11 transaction where is list in program, view, function module and object.

Check the where condition in SQL of program or function module which mention above.

3) Then create secondary index which is feasible on where condtion and reusable in many where condition but check weather all ready create secondary index or not. if any of index is not usable the by mean of your analysis in program SQLyou can make the change in the thant index but keep in mind that it should be user creatted index not by SAP provide index.

4) Don't create more than 4 or 5 index on one table and it take index search overhead.

5) Don't used more than 4 to 5 fields in one index.

[Former Member]

Thanks for the suggestions Ravishankar.

I have create an Index with Plant and Storage Location and this index is we can say subset of standard index M. Standard index 'M' has Material, Plant, Storage Location, movement type etc... My qyestion is can we create an index with fields Plant and Storage location only?

The index with fields Plant and storage locations doesn't improve the performance and is used only when give Plant and storage location in selection screen of MB51. If we give ranges on Plant and Storage location fields, the index is not getting used. Do you have any idea?

In MB51 screen, I am giving the posting date also and posting date is field of standard index BUD of MKPF table. Why the BUD index is not used? It's using the primary index '0' of MKPF.

Doign the SQL trace and found MKPF and MSEG join query is taking lot of time to execute.

Thanks,

MPREDDY

[ravi_lanjewar]

Hi,

The index with fields Plant and storage locations doesn't improve the performance and is used only when give Plant and storage location in selection screen of MB51. If we give ranges on Plant and Storage location fields, the index is not getting used. Do you have any idea?

You can try after including fields mandt as first fields in index which you created.

Important thing, MB51 is standard transaction, You should search for Note on service.sap.com

[Former Member]

Ravishankar,

Yes the Index with fields MANDT, Plant and Storage location is picking up only when we give Just Plant and Storage location as paramter values.

If we give as ranges its not picking the index. I have no idea why its not pikcing up the index when we give ranges.

Contaced SAP and they suggested some tips and those are reduce the number of fields from MB51 output and remove the coloring for the Quantity value in the output.

Tried all the way but there is no improvement in the performance. Right now MSEG has more than 16 millions records in it, and due to this performance issues are comign up.

Without archiving the MSEG records, is there any way to improve the performance?

Thanks for your answers.

MPREDDY

[ravi_lanjewar]

It could be many different scnario when we used the select option in porgram, When you exclue value or used range in select option will not used index and it will used sequencial read.

Without archiving the MSEG records, is there any way to improve the performance?

You can do hit and try using the following way.

1) Consider what will be user possible input to report according the where condition you create 3 to 4 index on table. check which index is used in program SQL exection and delete index which you have create which is not in used. i(e. only your index not SAP provide)

2) Post your message in SAP support portal or search for note on service.sap.com.

[eduardo_hinojosa]

Hi,

Check Note 921164 - MB51: Improving the runtime using database hints.

Regards,

Eduardo