AnsweredAssumed Answered

Performance Issue / Bug  ? Querying tasks for process variable values for single variable results in lots of joins

Question asked by shailendra1 on May 16, 2014
Latest reply on May 21, 2014 by shailendra1
Hi All,

I am using TaskQuery to find tasks which belong to processes having certain set of possible process variable values for a single variable

For e.g.,

TaskQuery query = taskService.createTaskQuery();
query.processVariableValueEquals("a1", "value1");
query.processVariableValueEquals("a1", "value2");
query.processVariableValueEquals("a1", "value3");
query.processVariableValueEquals("a1", "value4");
long count = query.count();

This call results in following SQL

select count(distinct RES.ID_)
FROM ACT_RU_TASK RES
INNER JOIN ACT_RU_VARIABLE A0 on RES.PROC_INST_ID_ = A0.PROC_INST_ID_
INNER JOIN ACT_RU_VARIABLE A1 on RES.PROC_INST_ID_ = A1.PROC_INST_ID_
INNER JOIN ACT_RU_VARIABLE A2 on RES.PROC_INST_ID_ = A2.PROC_INST_ID_
INNER JOIN ACT_RU_VARIABLE A3 on RES.PROC_INST_ID_ = A3.PROC_INST_ID_
WHERE A0.TASK_ID_ is null and A0.NAME_= 'a1' and A0.TYPE_ = 'string' and A0.TEXT_='value1'
AND
A1.TASK_ID_ is null and A1.NAME_= 'a1' and A1.TYPE_ = 'string' and A1.TEXT_ = 'value1'
AND
A2.TASK_ID_ is null and A2.NAME_= 'a1' and A2.TYPE_ = 'string' and A2.TEXT_ = 'value1'
AND
A3.TASK_ID_ is null and A3.NAME_= 'a1' and A3.TYPE_ = 'string' and A3.TEXT_ = 'value1';



As you can see that as the number of variables values increase, so does the number of joins.
This results in huge time for the query so it won't be practical to use this method for many values.

Is this a bug or there a better way of doing this ?

Outcomes