Performance problem with task listing

[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

[martin_grofcik]

Hi

Is there a way to do this with Activiti's support?

You can add new colum to the DB table. There are DB scripts to create DB and to upgrade DB in activiti sources. Just to modify them for your  purposes.

Are there any better solutions?

I would vote for DB changes( as you did) or limit the query scope.

Regards
Martin

[mrbean]

Is there a way to do this with Activiti's support?

You can add new colum to the DB table. There are DB scripts to create DB and to upgrade DB in activiti sources. Just to modify them for your purposes.

Thanks Martin
We will investigate this approach further. We will update the db creation scripts to create this new column. I will need to figure out how this change will be kept when we upgrade activiti from 5.11 to 5.15 and beyond. Question is how do we insert the value in this new column. I can think of some db mechanism (trigger that sets value for this column when a new row is created) or some event mechanism in activiti which updates the task record with a value for this column. Thanks

[jbarrez]

> some event mechanism in activiti which updates the task record with a value for this column.

In Activiti 5.15, you can now listen for entity creation events, see http://activiti.org/userguide/index.html#eventDispatcher

[mrbean]

Thanks Joram.
In the process of improving the performance of the query, we are also modifying Task.xml file to remove the "distinct" from the select statement. "distinct" seems to go to table scan instead of using the index. I noticed that there are insert and update statements in that file. I am wondering if I can modify those to change my insert and update instead of triggers or events. Both triggers and events will slow down creation/modification of task where as modification of insert statement/update statement will have no effect on performance.

I am thinking something like this (additions bolded) - I think I need to figure out mybatis syntax to make it work correctly.

    <insert id="insertTask" parameterType="org.activiti.engine.impl.persistence.entity.TaskEntity">
        insert into ${prefix}ACT_RU_TASK (ID_, REV_, NAME_, PARENT_TASK_ID_, DESCRIPTION_, PRIORITY_, InvertedPriority_, CREATE_TIME_, OWNER_,
        ASSIGNEE_, DELEGATION_, EXECUTION_ID_, PROC_INST_ID_, PROC_DEF_ID_, TASK_DEF_KEY_, DUE_DATE_, SUSPENSION_STATE_)
        values (#{id, jdbcType=VARCHAR},
        1,
        #{name, jdbcType=VARCHAR},
        #{parentTaskId, jdbcType=VARCHAR},
        #{description, jdbcType=VARCHAR},
        #{priority, jdbcType=INTEGER},
        #{0-priority, jdbcType=INTEGER},
        #{createTime, jdbcType=TIMESTAMP},
        #{owner, jdbcType=VARCHAR},
        #{assignee, jdbcType=VARCHAR},
        #{delegationStateString, jdbcType=VARCHAR},
        #{executionId, jdbcType=VARCHAR},
        #{processInstanceId, jdbcType=VARCHAR},
        #{processDefinitionId, jdbcType=VARCHAR},
        #{taskDefinitionKey, jdbcType=VARCHAR},
        #{dueDate, jdbcType=TIMESTAMP},
        #{suspensionState, jdbcType=INTEGER}
        )
    </insert>

[jbarrez]

Directly changing the query is not possible, however you can write your own mybatis query: http://www.jorambarrez.be/blog/2014/01/17/execute-custom-sql-in-activiti/