AnsweredAssumed Answered

performance issue with candidate tasks

Question asked by miroslav.slobodnik on Apr 3, 2012
Latest reply on May 3, 2012 by ronald.van.kuijk
Hello,
I am testing Activiti framework from performance perspective. Actually I found problem with candidate tasks query performance if I have about 300 000 process instance in database. Problem is with this lines of code where I want to find out CANDIDATE tasks for concrete user:


org.activiti.engine.TaskService taskService = processEngine.getTaskService();
TaskQuery taskQuery = taskService.createTaskQuery();
taskQuery = taskQuery.taskCandidateUser(userId).orderByTaskCreateTime().asc();
List<org.activiti.engine.task.Task> list = taskQuery.listPage(0, MAX_TASKS_RESULTS);

This generates this  DB select:


SELECT DISTINCT t. *
FROM act_ru_task t
INNER JOIN act_ru_identitylink i
ON i.task_id_    = t.id_
WHERE i.type_    = 'candidate'
AND i.group_id_ IN ( 'DISPATCHER' )
AND t.assignee_ IS NULL
ORDER BY t.create_time_ ASC

This query is executed about 8 seconds. I am using Activiti 5.9 with Oracle 11g database on Linux machines. All other queries are executed within milliseconds.
I tried to found out where the problem is and it is with IS NULL condition in assignee_ column. If I changed condition in SQL to IS NOT NULL or = '1' the query is executed in milliseconds.

So I realized that the problem is only with candidate tasks where assignee is not set (IS NULL).

Do you have some solution to fix it?

Thank you in advance.

Outcomes