topic Re: Adding index on ACT_HI_PROCINST table in Alfresco Forum

Adding index on ACT_HI_PROCINST table

andreea_bogdan — Mon, 10 Apr 2017 10:18:56 GMT

We are currently experiencing a major issue regarding the ACT_HI_PROCINST historization table from Activiti.The table has ~15 mio rows. We are querying the table in one of our activiti processes and since the table is huge, the time to query is really long.We will be cleaning up the table to get rid

Re: Adding index on ACT_HI_PROCINST table

gdharley — Mon, 10 Apr 2017 19:47:31 GMT

Hi Andreaa,

Go ahead and add the index, it will cause no problems at all.

In fact, for environments that retain a large history (such as yours), it is typical that you would add this index.

As to why it isn't there by default, generally you would not want to maintain millions of rows of history data. More often than not you would move it into an archive store.

Regards,

Greg

‌

Re: Adding index on ACT_HI_PROCINST table

cjose — Tue, 11 Apr 2017 13:26:52 GMT

Agree with what Greg mentioned above. If historical information is required for other applications/processes, it is best to be stored externally in a business data store OR internally in the same database but custom tables (See Activiti User Guide for more details on this option) which are optimized for your queries.

Though this post is a bit old, it is still very relevant when building/designing business processes - http://airquill.io/2014/06/29/Storing-data-in-processes.html http://airquill.io/2014/06/29/Storing-data-in-processes.html

Regards,

Ciju