Hello all,
I noticed that by default Creatio adds a Non-Unique/Non-Clustered index on all foreign key columns (Look up references). Want to understand this better.
Indexes can be detrimental when there is a lot of Writes into that table as compared to Reads. This effect gets multiplied when a Table has 5 or more look up reference columns.
1. Are these indexes Mandatory for the functioning of Creatio?
2. Will there be any side effects (Apart from performance implications) to removing these indexes on a case by case basis - After evaluation of whether table has more Read vs Write frequency.
Like
Hello,
Foreign key indexes are not mandatory but if you disable or remove that index, it may result in a slower search. We do not recommend removing OOTB indexes. Instead, you can safely disable indexes. E.g. for MS SQL Server use:
ALTER INDEX [YOUR_INDEX_NAME] ON dbo.[YOUR_TABLE_NAME] DISABLE;
Best regards,
Bogdan S.
Bogdan Spasibov,
Thank you Bogdan. If we disable it at the DB level, wouldn't a subsequent package install re-enable that index?
From my understanding, the Index is controlled by the 'Indexed' attribute against that column in the Object designer. Is it better to remove it from here or disable it at the DB level directly?
M Shrikanth,
If you are not going to use that you can remove it from the Object Designer and disable it on the DB level.
But let me ask you what is the business task you are trying to achieve?
Best regards,
Bogdan S.
Bogdan Spasibov,
Hi Bogdan. Thank you. Indexes by default do not add a performance benefit. They aid read performance but slow down writes as the indexes are to be updated as well. Creatio seems to add by default an index to all foreign key columns irrespective of whether that table is read heavy or write heavy. In cases where performance is a big deal, disabling these indexes might give a performance edge.
This is to better our understanding of Creatio and not for a specific business task.