AnsweredAssumed Answered

Performance issue due to dynamic query creation

Question asked by waldorf on Jan 13, 2012
Latest reply on Mar 6, 2012 by waldorf
Hi,

we made some performance tests with Activiti on a hardware with 12 cores (tomcat server with Activiti + separate database server).
During these tests we observed a contention in MyBatis' dynamic query creation mechanism which is called from ExecutionEntity.remove() for every finished process instance.
The dynamic queries are created by: TaskQueryImpl(commandContext).executionId(id).list() and JobQueryImpl(commandContext).executionId(id).list(), see below:

    …
    CommandContext commandContext = Context.getCommandContext();
    List<TaskEntity> tasks = (List) new TaskQueryImpl(commandContext)
      .executionId(id)
      .list();
    for (TaskEntity task : tasks) {
      if (replacedBy!=null) {
        task.setExecution(replacedBy);
      } else {
        commandContext
          .getTaskManager()
          .deleteTask(task, TaskEntity.DELETE_REASON_DELETED, false);
      }
    }

    List<Job> jobs = new JobQueryImpl(commandContext)
      .executionId(id)
      .list();
    for (Job job: jobs) {
      if (replacedBy!=null) {
        ((JobEntity)job).setExecution((ExecutionEntity) replacedBy);
      } else {
        ((JobEntity)job).delete();
      }
    }
    …
For our tests we changed the creation of the two dynamic queries to static queries (since the dynamic queries always evaluate to the same SELECT statement anyway). This change led to a performance increase of up to 100% in our tests.

As an example the statement for selecting the jobs belonging to a process instance (from our changed /org/activiti/db/mapping/entity/Job.xml):

  <select id="selectJobsByExecutionId" parameterType="string" resultMap="jobResultMap">
    select *   
    from ACT_RU_JOB J
    where J.EXECUTION_ID_ = #{executionId}
  </select>
Note also the following quote from http://forums.activiti.org/en/viewtopic.php?f=6&t=1523#p6386 about the queries in ExecutionEntity.remove():
Profiler shows that they consume at least 50-70% of overall time.
We created a Jira issue (ACT-1056) and can contribute a patch if needed.

Some more details concerning our tests:
The test scenario was a multi-threaded test client that started process instances and waited for their completion, measuring the throughput: how many processes completed in a given time (e.g. per minute).

The test process had the following features:
  • main process:
    • two ServiceTasks performing db updates (each with asynchronous continiuation)

    • two ServiceTasks sending jms notifications back to the test client

    • call of a sub process (with asynchronous continiuation)
  • sub process
    • two ServiceTasks that call WebServices (SoapUI simulator was used for these WebServices)

    • two exclusive gateways
Perhaps there are other (more elegant  ;) ) alternatives that we may have overlooked.

Outcomes