TaskQuery Slow with Variables

[jasonbradfield]

Hi,

We currently have 100,000 records in out act_ru_variable table, and 2,000 in our act_ru_task table.

It is taking over 5 minutes to execute the below. ie all tasks that a user is a candidate for.
It does not matter how many tasks the user is a candidate for.
If I remove the include variables it is very quick.
Here is the java

List<org.activiti.engine.task.Task> claimable = taskService.createTaskQuery().taskCandidateUser(contactId.toString())
         .includeProcessVariables()
         .includeTaskLocalVariables()
         .list();
‍‍‍‍‍‍

Which executes this

select RES.*,
VAR.ID_ as VAR_ID_, VAR.NAME_ as VAR_NAME_, VAR.TYPE_ as VAR_TYPE_, VAR.REV_ as VAR_REV_,
VAR.PROC_INST_ID_ as VAR_PROC_INST_ID_, VAR.EXECUTION_ID_ as VAR_EXECUTION_ID_, VAR.TASK_ID_ as VAR_TASK_ID_,
VAR.BYTEARRAY_ID_ as VAR_BYTEARRAY_ID_, VAR.DOUBLE_ as VAR_DOUBLE_, 
VAR.TEXT_ as VAR_TEXT_, VAR.TEXT2_ as VAR_TEXT2_, VAR.LONG_ as VAR_LONG_
from ACT_RU_TASK RES
left outer join ACT_RU_VARIABLE VAR ON RES.ID_ = VAR.TASK_ID_ or RES.PROC_INST_ID_ = VAR.EXECUTION_ID_
inner join ACT_RU_IDENTITYLINK I on I.TASK_ID_ = RES.ID_
WHERE  RES.ASSIGNEE_ is null
and I.TYPE_ = 'candidate'
and (I.USER_ID_ = '-226') 
order by RES.ID_ asc 
LIMIT 20000 OFFSET 0
‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Note: If I change the limit to 20 I get the same result

Here is an explain

1   SIMPLE   I   ref   ACT_IDX_IDENT_LNK_USER,ACT_FK_TSKASS_TASK   ACT_IDX_IDENT_LNK_USER   768   const   3877   Using index condition; Using where; Using temporary; Using filesort
1   SIMPLE   RES   eq_ref   PRIMARY   PRIMARY   194   vista.I.TASK_ID_   1   Using where
1   SIMPLE   VAR   ALL   ACT_IDX_VARIABLE_TASK_ID,ACT_FK_VAR_EXE,JB_TASK_EXEC   (null)   (null)   (null)   100428   Range checked for each record (index map: 0x26)
‍‍‍‍‍

Mysql 5.6.16
Activiti: 5.17.0

[trademak]

Executing the query separately for process variables and local task variables should improve the performance a lot as well.
Could you try to do 2 queries, one with includeProcessVariables() and one with only includeTaskLocalVariables?

Best regards,

[jasonbradfield]

Thanks, that improved the performance. merging the 2 results meant I had to cast the Tasks.