L3 - Default This query:
processInstances = historyService
.createHistoricProcessInstanceQuery()
.finishedBefore(minDate.getTime())
.processDefinitionKey(processDefinitionKey)
.orderByProcessInstanceEndTime().asc().list();
Leads to this SQL command:
select RES.*
from ACT_HI_PROCINST RES
inner join ACT_RE_PROCDEF DEF on RES.PROC_DEF_ID_ = DEF.ID_
WHERE DEF.KEY_ = 'ADefKey'
and RES.END_TIME_ <= '2014-06-06 12:12:12'
and RES.END_TIME_ is not NULL
order by END_TIME_ asc
LIMIT 2147483647 OFFSET 0;
When a high load is present, this can lead to long waiting times (with over 80,000 entries -> 500-700 seconds).
This can be reduced to <1 second with this index:
create index PROC_DEF_ID_END_TIME ON ACT_HI_PROCINST (PROC_DEF_ID_,END_TIME_);