topic Re: Performance problem with task listing in Alfresco Archive

Performance problem with task listing

mrbean — Mon, 31 Mar 2014 20:10:32 GMT

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 sy

Re: Performance problem with task listing

martin_grofcik — Tue, 01 Apr 2014 06:44:32 GMT

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

Re: Performance problem with task listing

mrbean — Tue, 01 Apr 2014 14:35:32 GMT

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

Re: Performance problem with task listing

jbarrez — Tue, 01 Apr 2014 20:36:20 GMT

> 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

Re: Performance problem with task listing

mrbean — Wed, 02 Apr 2014 18:29:29 GMT

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>

Re: Performance problem with task listing

jbarrez — Tue, 22 Apr 2014 08:26:04 GMT

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/