topic Optimizing History Queries in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/optimizing-history-queries/m-p/198767#M151897 <HTML><HEAD></HEAD><BODY><SPAN>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’.</SPAN><BR /><BR /><PRE class="language-java line-numbers"><CODE><BR /><BR />List&lt;HistoricProcessInstance&gt; filteredHistoricalProcessInstanceList = new NativeHistoricProcessInstanceQueryImpl(commandExecutor)<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.sql("select * from( "+<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"select ahp.* "+<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" from act_hi_procinst ahp "+<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" inner join ("+<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" select distinct proc_inst_id_,text_ from act_hi_varinst "+<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" where text_ = #{policyCd}) ahv "+<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" on ahp.proc_inst_id_ = ahv.proc_inst_id_ "+<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" where ahp.super_process_instance_id_ is null "+<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" order by ahp.start_time_ desc) "+<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" where rownum&lt;=#{number} ")<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.parameter("policyCd",&nbsp; policy.getPolicyCd())<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.parameter("number", number).list();<BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><BR /><BR /><SPAN>The problem I have is that there are polling processes which collectively make 6000+ hits to the DB within one hour.&nbsp; </SPAN><BR /><SPAN> &nbsp;&nbsp;&nbsp;</SPAN><BR /><SPAN>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.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</SPAN><BR /><BR /><BR /><SPAN>1.)&nbsp;&nbsp;&nbsp;Are there indexes that can be added on ACT_HI_PROCINST and ACT_HI_VARINST&nbsp; that would help for heavy usage of history.</SPAN><BR /><BR /><SPAN>2.)&nbsp;&nbsp;&nbsp;Are there certain Activiti API query facilities (via HistoryService et.) that are more efficient than others for querying historic process instances?</SPAN><BR /><BR /><BR /><SPAN>Thnk you!</SPAN></BODY></HTML> Wed, 26 Nov 2014 19:05:44 GMT ag91375 2014-11-26T19:05:44Z Optimizing History Queries https://connect.hyland.com/t5/alfresco-archive/optimizing-history-queries/m-p/198767#M151897 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&lt;HistoricProcessInstance&gt; filteredHistoricalProcessInstanceList = new NativeHistoricPr Wed, 26 Nov 2014 19:05:44 GMT https://connect.hyland.com/t5/alfresco-archive/optimizing-history-queries/m-p/198767#M151897 ag91375 2014-11-26T19:05:44Z Re: Optimizing History Queries https://connect.hyland.com/t5/alfresco-archive/optimizing-history-queries/m-p/198768#M151898 <HTML><HEAD></HEAD><BODY><SPAN>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. </SPAN></BODY></HTML> Tue, 02 Dec 2014 06:11:03 GMT https://connect.hyland.com/t5/alfresco-archive/optimizing-history-queries/m-p/198768#M151898 ollib 2014-12-02T06:11:03Z