topic Re: Activiti SQL query performance in Alfresco Archive

Activiti SQL query performance

shailendra1 — Wed, 08 Jan 2014 06:16:10 GMT

During one of our performance tuning exercise, we observed that while querying for all variables using Activity was using following query defined in the xml <select id="selectTaskWithVariablesByQueryCriteria" parameterType="org.activiti.engine.impl.TaskQueryImpl" resultMap="taskAndVariablesResult

Re: Activiti SQL query performance

shailendra1 — Wed, 08 Jan 2014 06:20:11 GMT

The resulting query fired is like this

select distinct 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.PROC_INST_ID_ = VAR.EXECUTION_ID_ and VAR.TASK_ID_ is null;

Re: Activiti SQL query performance

jbarrez — Thu, 09 Jan 2014 09:38:24 GMT

I've removed the distinct: https://github.com/Activiti/Activiti/commit/efdcdf99549456dd9b982b3824940b747d96d4ed

All tests run green on our QA, so it seems safe to keep.
Thanks for pointing this out.

Re: Activiti SQL query performance

shailendra1 — Fri, 10 Jan 2014 07:56:36 GMT

Thanks Jorram,
Glad to contribute.