Skip to main content
Solved

Barely used autogenerated indexes by the SF


Ricky
Superhero
Forum|alt.badge.img+8

Coming from a ticket out of TMS:

 

There are a lot of additional (i.e.search order) indexes generated by the SF that are either barely used or never used at all.

These indexes have to be maintained when updating, adding or deleting a record which eventually cause a performance penalty

We have 2 questions:

  1. Why and under what circumstances are these indexes generated
  2. Are they optional and how can we set this up that they are not generated at all?

If this will cause an idea, please move this post into the right section on this portal.

rgds Ricky

Best answer by Erwin Ekkel

The Software Factory automatically creates non-clustered indexes for foreign keys and sort sequences.
The SQL engine decides if an Index should be used for a query or not. You can force the use of an index but this is not best practice. This can cause some indexes to never be used if for example there are too many columns queried outside the index, or there is a better index for the job available. 

The SO indexes are created when a sort order is configured in subjects. And there is not an index that already has the SO columns in that specific order as the first columns. You can change settings for the SO indexes but removing the index does not work since on generating the definition the index will be recreated. The same goes for foreign keys. When a reference is created an index for the foreign key is added, if there is not another index that already covers it in the model. 

You could add a dynamic model code to remove some indexes, or an after upgrade custom code to remove them from the DB but then they would still exist in the model. 

We will investigate the usage and effectiveness of these indexes and we will come back on this. 
 

Edit: An User story has been created for this on the Metamodel backlog to investigate this.

View original
Did this topic help you find an answer to your question?
This topic has been closed for comments

Forum|alt.badge.img+17

The Software Factory automatically creates non-clustered indexes for foreign keys and sort sequences.
The SQL engine decides if an Index should be used for a query or not. You can force the use of an index but this is not best practice. This can cause some indexes to never be used if for example there are too many columns queried outside the index, or there is a better index for the job available. 

The SO indexes are created when a sort order is configured in subjects. And there is not an index that already has the SO columns in that specific order as the first columns. You can change settings for the SO indexes but removing the index does not work since on generating the definition the index will be recreated. The same goes for foreign keys. When a reference is created an index for the foreign key is added, if there is not another index that already covers it in the model. 

You could add a dynamic model code to remove some indexes, or an after upgrade custom code to remove them from the DB but then they would still exist in the model. 

We will investigate the usage and effectiveness of these indexes and we will come back on this. 
 

Edit: An User story has been created for this on the Metamodel backlog to investigate this.


Harold
Apprentice
Forum|alt.badge.img+1
  • Apprentice
  • January 25, 2023

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings