performance issue => re-build MSEG Index

[Former Member]

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.

[Former Member]

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

[andrea_galluccio2]

Hi,

i'll try to answer to all of your question, point by point in the best way.

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?

>> As i know, MANDT is necessary (always) if the tables has MANDT field. One index is selected than another by the optimizer based on the sequence of the fields in the select statement. So if you're table has MANDT, in the index u have to specify MANDT field otherwise that index will never be used (or it will have low probability to be used).

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

>> Each time u create a table, a standard index called ~0 is created. That index in based on the key fields (with the same oreder) of the table. U should create an index only if u access to a table with another sequence of fields (that is, nou using the key fields in the same sequence as declared in table)

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

>> I think u should execute the statistics ( i don't think is necessary restart the application server).Ask to your sysadmin how to create the statistics for an application server. In that way the new index will be known by the optimizer, that sould start using it.

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

>> Usually many index could be slow down. Because each time u modify data in table (with insert , delete or modify operations), the index must be update also.

So i suggest to check (with the statistics),which indexes are usually used in the select on MSEG and delete the other (an index occupies spaces on db).

Another hint. If a program use MSEG table, check with the ST04, which index is used during the selection. Probably use a wrong index and the performace are slow down.

Hope it helps

Bye

Andrea

Pls reward if it helps

[Former Member]

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

[Former Member]

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.

[Former Member]

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