It seems that any indexes (unique or otherwise) created in Creatio config are created to ignore case. This is not good from a data perspective, and prevents actual uniqueness checking in cases where the case of the characters matter. We need to be able to configure unique indexes to be case sensitive - I think ideally this would be the default option and you could check a checkbox in the config to ignore case if required, but certainly you should be able to generate normal database unique indexes which are case sensitive.
I have an existing object called account in project that is based on the baseentity object and includes a lookup field to the account object and a lookup field to project. (and some more simple fields) I want to add an unique index to avoid having duplicate schools in the same project. I created an index and added the account field and the project field, and checked the unique checkbox.
WHen I publish, I get an erroe message: Failed to update structure for following schemas: "UsrAccountInProject","UsrAccountInProject","UsrAccountInProject"... (6 times)
I did make sure that I don't before adding the index but I forgot to check for records not filled in. Now removed those records and publish was successful.
Colleagues,
Can anyone explain why for example in table dbo.Activity there are
identical indexes 'I7Nk67FkcZJTOtRCb5pRNRHffI' and 'Iqf5Ij9PskG09b4gVMq7XcSq04g'?
It hardly speeds up the work, but this functionality is directly present
in the basic version of the product! There are about 50 such indexes in
the database, and one is created by the system, the other is in the
database. Therefore, it turns out that only one index works when searching,
but when inserting there will already be two additional processing of
these indexes. And this already significantly affects the speed of the
application during the addition of records, and this is inherent
in objects where are many records (Product, calls, contacts, activities).
How to fix this problem?
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.
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;
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?
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.
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?
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.
