AnsweredAssumed Answered

Optimizing History Queries

Question asked by ag91375 on Nov 26, 2014
Latest reply on Dec 2, 2014 by ollib
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!