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 0

Like

4 comments

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. 

Show all comments

Hi Community,

I'm trying to understand the behavior of Creatio with Indexed / Non-Indexed databases. Does Creatio handle database indexing by default? If yes, which are the default fields that are indexed?

 

Also, can a Non-Indexed database affect Creatio's performance considering a production environment? Or does indexing only affect direct DB scripts configured in the packages?

 

Thanks in advance.

Like 2

Like

1 comments

Hello Halludeen,

 

Hope you're doing well.

 

By default, the Creatio application can handle the database indexing. For cloud solutions, we arrange the defragmentation of indexes every day to provide stable site productivity. For on-site deployments, indexing should be configured by the client's/partner's DBA. In the second case, index defragmenting should be done for all available indexes (you can skip this operation for clustered indexes, as it doesn't make a big difference before).

 

About the non-indexed database, the application also can work with a kind of DB, but we can not assure that the application performance will be as fast as with indexed DB. That's why we recommend to perform index operation for DB records at least once per month.

 

Thank you for your questions.

 

Best regards,

Roman

Show all comments