Skip to main content

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

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.

I would like to bring to attention my Idea: https://community.thinkwisesoftware.com/ideas/reference-without-index-3648

Reply


Terms & ConditionsCookie settings