Skip to Content
author's profile photo Former Member
Former Member

performance issue => re-build MSEG Index

Hi all,

The performance of all program is very low if the table

MSEG is involved in program.

For example, MB51.

I have checked that MSEG has built-in 3 default index.

then previous colleague made extra 5 index.

they are:

Name Fields

-


-


Y01 MANDT + AUFNR + BWART

Y02 MANDT + LGORT + AUFNR

Y03 MANDT + WERKS + LGORT + MATNR CHARG

Y04 MANDT + LGORT + CHARG

Z05 MATNR + WERKS + BWART + CHARG + AUFNR

Now the questions are:

1. is the MANDT field is necessary for index? if not, then does it affect the index effectiveness? for example , Y01 change to AUFNR + BWART will be better?

2. do i need to concern the sorting of field? eg. does it different for Y02 in MANDT + AUFNR + LGORT compared to orginial one.

3. Do i need to restart the server to take effect if i change the index?

4. does too many indexs created in MSEG will slow down the program performance? how many index no. is suitable in MSEG normally?

SOrry for many question.

Thanks in advance for any positive feedback.

I will reward the points for any good ideas.

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 13, 2006 at 01:35 PM

    It's a good question.

    I don't know if having too many indexes will actually slow down a select, but it may prevent the optimizer from choosing the best index to use for a select. So if it picks the wrong one, there may be a performance hit.

    Multiple indexes will slow down the update of MSEG, because the database has to keep each index up to date when the table is updated.

    You should see if all of the indexes are actually needed and get rid of any that aren't.

    It might also help to get your DBAs to do a database re-org.

    Rob

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Andrea Galluccio

      Andrea - I'm not sure if MANDT is really needed in an index. Many SAP supplied indexes do not have MANDT. For example look at index S for MSEG.

      However, if you want to use MANDT in the SELECT, then it won't be able to use an index that is missing the field.

      Rob

  • author's profile photo Former Member
    Former Member
    Posted on Oct 14, 2006 at 02:51 PM

    You have not said which DataBase you are using, this can make a difference to how a select behaves, (DB2 has some querks).

    2. Do I need to concern the sorting of field? eg. does it make a difference for Y02 with MANDT + AUFNR + LGORT compared to MANDT + LGORT + AUFNR.

    Yes it does make a difference. Think about a telephone directory, would you rather through 10000 Boriss to find yourself or 100 Yips.

    The general rules for creating indexes are; Fields that are availabe (within the calling program(s)) should be to the start. Less likely to have a blank value in the where condition. After that you want the fields with the greatest variation at the start.

    MattG.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 16, 2006 at 11:10 AM

    My analysis:

    <i>1. is the MANDT field is necessary for index? if not, then does it affect the index effectiveness? for example , Y01 change to AUFNR + BWART will be better?</i>

    Many of SAP standard table I could not find the MANDT as a mandarory field.

    <i>2. do i need to concern the sorting of field? eg. does it different for Y02 in MANDT + AUFNR + LGORT compared to orginial one.</i>

    Yes. Even when you select the fields... the order in which they are mentioned matters the performance... Best to follow the orginal order.

    <i>3. Do i need to restart the server to take effect if i change the index?</i>

    I feel not needed

    <i>4. does too many indexs created in MSEG will slow down the program performance? how many index no. is suitable in MSEG normally?</i>

    We create Indexes as we needed... And I do not feel too many indexes will effect a database selection from the program.

    ~thomas

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.