TaskQuery Slow with Variables

As we transition to the new Hyland Community, some post links may be temporarily disrupted as we finalize redirects. We apologize for the inconvenience and are working to resolve this as quickly as possible. Details.

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,