Query to get processinstance id from variable value takes around 5 minutes

1

The query to get process instance details from variable value and process definition key is taking around 5 minutes to complete from both history and runtime service.
Is there any way to get process instance id/ or process instance details from variable name, it’s value and process definition key?

2

I think it will help if you show what code you are executing and give some background of what variables you are saving in your process.

3

Hi,
PFB the code snippet which is taking time:

List processInstanceList = runtimeService.createProcessInstanceQuery()
.processDefinitionKey(processDefinitionKey)
.variableValueEquals(“mobile”, variableValue).list();

corresponding query being hit on DB:

select distinct RES.* ,
P.KEY_ as ProcessDefinitionKey,
P.ID_ as ProcessDefinitionId,
P.NAME_ as ProcessDefinitionName,
P.VERSION_ as ProcessDefinitionVersion,
P.DEPLOYMENT_ID_ as DeploymentId
from ACT_RU_EXECUTION RES
inner join ACT_RE_PROCDEF P
on RES.PROC_DEF_ID_ = P.ID_
inner join ACT_RU_VARIABLE A0
on RES.PROC_INST_ID_ = A0.PROC_INST_ID_
WHERE RES.PARENT_ID_ is null
and P.KEY_ = ?
and A0.EXECUTION_ID_ = A0.PROC_INST_ID_
and A0.NAME_= ?
and A0.TYPE_ = ?
and A0.TEXT_ = ?
order by RES.ID_ asc

The reason for the query to take time is:
count of rows in ACT_RU_VARIABLE table is around 9.7 million and the count of rows in ACT_RU_EXECUTION table is around 1.7 million.

I’m looking for an alternative way to get running process instance list (just process instance id works too) which takes lesser time than the above query.

4

What database server are you using?

Other things that you should check is:
Why there is so many rows in the RU Variable table?
The RU table keeps only open execution variables and closed ones are moved to the HI table (if you have auditing enabled.) Should you have so many open executions?
You can try to create partial indexes for this table (I know postgresql can do this) that satisfy this where clause. (Partial indexes would be preferred to full indexes, but it depends on your data)
I dont think there is another way to build the query, you would need to either reduce the data, partition the table, or create indexes.