Camunda server performance is very poor because of these below queries, please let us know what we need to do on this (we are using 7.17 camunda spring boot):
select distinct PROC_DEF_ID_ from ACT_RU_EXECUTION; – returns 23 unique values…table run time has 3M+ records
RES.PROC_DEF_ID_ = P.ID_ condition in query performs bad and its doing ACT_RU_EXECUTION full table scan
data on column RES.PROC_DEF_ID_ has very less cardinality
Below is the full query:
select distinct RES.*
from ACT_RU_EXECUTION RES
inner join ACT_RE_PROCDEF P on RES.PROC_DEF_ID_ = P.ID_
WHERE RES.SUSPENSION_STATE_ = 1
and EXISTS (
select
ID_
from
ACT_RU_VARIABLE
WHERE
NAME_= ‘businessKey’
and TASK_ID_ is null and RES.PROC_INST_ID_ = PROC_INST_ID_
and
(
( TYPE_ is not null and TYPE_ = ‘string’
and TEXT_ is not null and
TEXT_
=
‘POID_32652571_FOID_84882147’ )
)
)
and exists (select ID_ from ACT_RU_EVENT_SUBSCR EVT where
EVT.EXECUTION_ID_ = RES.ID_ and
EVT.EVENT_TYPE_ = ‘message’
and EVT.EVENT_NAME_ = ‘UpdateTimer’
)
order by RES.ID_ asc
LIMIT 2147483647 OFFSET 0;
Could you please help me on how to use business key for correlation.
I am currently using correlation command like this:
List results = execution.getProcessEngine().getRuntimeService().createMessageCorrelation(“UpdateTimer”).processInstanceVariableEquals(“businessKey”, businessKey).setVariables(map).correlateAllWithResult();
if (((List<?>) results).isEmpty()) {
LOGGER.info("Unable to correlate with "+ businessKey);
}
Then you can do the correlation as follow: execution.getProcessEngineServices().getRuntimeService().createMessageCorrelation(<MESSAGE_NAME>).processInstanceBusinessKey(<BUSINESS_KEY_VALUE>).correlate()