Optimizing History Queries

[ag91375]

We have the following SQL in the NativeHistoricProcessInstanceQueryImpl making a query for the 10 most recent top level root process instances that have a certain variable associated, which is ‘policyCd’.

List<HistoricProcessInstance> filteredHistoricalProcessInstanceList = new NativeHistoricProcessInstanceQueryImpl(commandExecutor)
      .sql("select * from( "+
            "select ahp.* "+
            " from act_hi_procinst ahp "+
            " inner join ("+
            " select distinct proc_inst_id_,text_ from act_hi_varinst "+
            " where text_ = #{policyCd}) ahv "+
            " on ahp.proc_inst_id_ = ahv.proc_inst_id_ "+
            " where ahp.super_process_instance_id_ is null "+
            " order by ahp.start_time_ desc) "+
            " where rownum<=#{number} ")
      .parameter("policyCd",  policy.getPolicyCd())
      .parameter("number", number).list();
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The problem I have is that there are polling processes which collectively make 6000+ hits to the DB within one hour. 
   
I have been searching the web for the answers to the following questions I have posted but have not been able to find any answers.      


1.)   Are there indexes that can be added on ACT_HI_PROCINST and ACT_HI_VARINST  that would help for heavy usage of history.

2.)   Are there certain Activiti API query facilities (via HistoryService et.) that are more efficient than others for querying historic process instances?


Thnk you!

[ollib]

You could store your process variables in an separate table with the JPA option, see chapter 10 of the user guide. The process instance id should be a column in this table. You can then join this table and the activiti act_hi_procinst table and query with your filter requirements. Even better is to create a database view on both tables.