AnsweredAssumed Answered

Performance problem with task listing

Question asked by mrbean on Mar 31, 2014
Latest reply on Apr 1, 2014 by mrbean
This is not necessarily a problem of Activiti. It's more of a problem of the underlying database. I want to know if there is any recommendation for this problem or if others have faced similar problem and how they may have handled it.

Here is the scenario.
We have about 300K unassigned tasks in the system. We want to list those in our task lists ordered by priority descending and due date ascending. Activiti generates the query per our criteria and the query is like this

select distinct RES.*
      from ACT_RU_TASK RES inner join ACT_RU_IDENTITYLINK I on I.TASK_ID_ = RES.ID_
      WHERE RES.ASSIGNEE_ IS NULL
            and RES.ASSIGNEE_ is null
            and I.TYPE_ = 'candidate'
            and ( I.GROUP_ID_ IN ( ?,
                                   ?,
                                   ? )
                   )
      order by RES.PRIORITY_ desc, RES.DUE_DATE_ asc
      LIMIT 60 OFFSET 0

Due to the different orders of the order by, mysql cannot use index and does filesort and this operation takes 90 seconds on a decent windows hardware (8 gb ram, quadcore cpu etc)

One suggested solution by our dbas was adding a new column InvertedPriority so we can order by in the same order. Is there a way to do this with Activiti's support? Are there any better solutions?

Thanks

Outcomes