AnsweredAssumed Answered

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 there a better way of doing this ?

Outcomes