Using Indexes with unused Fields

[Former Member]

Hello ABAP community,

I am actually implementing performance improvements, and I have a problem when using indexes.

Example: I have a SELECT statement which checks following fields into WHERE conditions : BELEGTC, VORGAST, BUKRS and AUSZIFS.

I already have an index which contains AUSZIFS, BELEGTC, and VERTRAN (this last field i am not using it in my select statement).

My question is:

Do I need to use an Index with exactly the same fields as I am using in my select (or whichever DB operation I am trying to improve performance) ?

Does it harm the performance, if I use an Index with fields which I am not using in my select?

And another question: We have a table with approximately 30 Indexes defined. Does it harm the performance of the table in general, in all operations over the table, despite defined indexes are not used in these operations?

For example, I use an index Z01 in a select statement which retrieves data from a table called ZTable. If this ZTable has 30 indexes defined, does it influence the performance of my operation although I am only using index Z01?

Thanks a lot in advance!

Jorge.

[Former Member]

Hi Jorge,

I'll reply from my previous experiences with the same issues

Do I need to use an Index with exactly the same fields as I am using in my select (or whichever DB operation I am trying to improve performance) ?

Not at all. Sure, all fields are better, but if you can use about 80% of the index fields, performances are not so impacted (obviously it depends by the granularity of your index and selection)

And another question: We have a table with approximately 30 Indexes defined. Does it harm the performance of the table in general, in all operations over the table, despite defined indexes are not used in these operations?
For example, I use an index Z01 in a select statement which retrieves data from a table called ZTable. If this ZTable has 30 indexes defined, does it influence the performance of my operation although I am only using index Z01?

Yes, it can be, and there will be an higher probability if the indexes shares many fields.

Or you use exactly the fields in an index or the poor DB go mad trying to identify the correct one.

[SimoneMilesi]

Hi Jorge,

I'll reply from my previous experiences with the same issues

Do I need to use an Index with exactly the same fields as I am using in my select (or whichever DB operation I am trying to improve performance) ?

Not at all. Sure, all fields are better, but if you can use about 80% of the index fields, performances are not so impacted (obviously it depends by the granularity of your index and selection)

And another question: We have a table with approximately 30 Indexes defined. Does it harm the performance of the table in general, in all operations over the table, despite defined indexes are not used in these operations?
For example, I use an index Z01 in a select statement which retrieves data from a table called ZTable. If this ZTable has 30 indexes defined, does it influence the performance of my operation although I am only using index Z01?

Yes, it can be, and there will be an higher probability if the indexes shares many fields.

Or you use exactly the fields in an index or the poor DB go mad trying to identify the correct one.

[Former Member]

Hi,

you dont have to match all fields, but the more fields you match, the better the performance gets.

its important what fields the index starts with. if you can not support the first field, this index is useless. if you can support the first field, but not the second field, you get the performance as is as if you would have an index with just 1 field.

each index a ddic table has, lowers the performance when you do inserts, updates and delets, because each index is "like" a new table sorted the way round. and doing an insert will lead to an "insert" to every index table.

the amount of indexes does not lower the performance of the read-statment! because when u select data from 1 index table, you dont care about all the others.

using internal tables with indexes is the same. only thing is that an index is just "created" when its needed by a read or loop. and only after that statment you got the same overhead.

if im wrong feel free to correct me

regards

Stefan Seeburger

[Former Member]

I've never heard of a table with 30 indexes! It seems excessive, but you should use a performance trace (ST05) before doing anything else to see if you are actually using any index in the SELECT. There are plenty of discussions on ST05 and performance in SCN. You should check those.

In general, it is important when using an index to use the leading fields of the index in the select.

Rob