topic Performance issue due to dynamic query creation in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/performance-issue-due-to-dynamic-query-creation/m-p/83962#M56465 <HTML><HEAD></HEAD><BODY><SPAN>Hi,</SPAN><BR /><BR /><SPAN>we made some performance tests with Activiti on a hardware with 12 cores (tomcat server with Activiti + separate database server).</SPAN><BR /><SPAN>During these tests we observed a contention in MyBatis' dynamic query creation mechanism which is called from </SPAN><EM>ExecutionEntity.remove()</EM><SPAN> for every finished process instance. </SPAN><BR /><SPAN>The dynamic queries are created by: </SPAN><EM>TaskQueryImpl(commandContext).executionId(id).list()</EM><SPAN> and </SPAN><EM>JobQueryImpl(commandContext).executionId(id).list()</EM><SPAN>, see below:</SPAN><BR /><PRE class="language-none line-numbers"><CODE><BR />&nbsp;&nbsp;&nbsp; …<BR />&nbsp;&nbsp;&nbsp; CommandContext commandContext = Context.getCommandContext();<BR />&nbsp;&nbsp;&nbsp; List&lt;TaskEntity&gt; tasks = (List) new TaskQueryImpl(commandContext)<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .executionId(id)<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .list();<BR />&nbsp;&nbsp;&nbsp; for (TaskEntity task : tasks) {<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (replacedBy!=null) {<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; task.setExecution(replacedBy);<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } else {<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; commandContext<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .getTaskManager()<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .deleteTask(task, TaskEntity.DELETE_REASON_DELETED, false);<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<BR />&nbsp;&nbsp;&nbsp; }<BR /><BR />&nbsp;&nbsp;&nbsp; List&lt;Job&gt; jobs = new JobQueryImpl(commandContext)<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .executionId(id)<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; .list();<BR />&nbsp;&nbsp;&nbsp; for (Job job: jobs) {<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; if (replacedBy!=null) {<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ((JobEntity)job).setExecution((ExecutionEntity) replacedBy);<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; } else {<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ((JobEntity)job).delete();<BR />&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }<BR />&nbsp;&nbsp;&nbsp; }<BR />&nbsp;&nbsp;&nbsp; …<BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><SPAN>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.</SPAN><BR /><BR /><SPAN>As an example the statement for selecting the jobs belonging to a process instance (from our changed </SPAN><EM>/org/activiti/db/mapping/entity/Job.xml</EM><SPAN><span class="lia-unicode-emoji" title=":disappointed_face:">😞</span></SPAN><BR /><PRE class="language-none line-numbers"><CODE><BR />&nbsp; &lt;select id="selectJobsByExecutionId" parameterType="string" resultMap="jobResultMap"&gt;<BR />&nbsp;&nbsp;&nbsp; select *&nbsp;&nbsp;&nbsp; <BR />&nbsp;&nbsp;&nbsp; from ACT_RU_JOB J<BR />&nbsp;&nbsp;&nbsp; where J.EXECUTION_ID_ = #{executionId}<BR />&nbsp; &lt;/select&gt;<BR /><SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN></CODE></PRE><SPAN>Note also the following quote from </SPAN><A href="http://forums.activiti.org/en/viewtopic.php?f=6&amp;t=1523#p6386" rel="nofollow noopener noreferrer">http://forums.activiti.org/en/viewtopic.php?f=6&amp;t=1523#p6386</A><SPAN> about the queries in </SPAN><EM>ExecutionEntity.remove()</EM><SPAN>:</SPAN><BR /><BLOCKQUOTE class="jive-quote">Profiler shows that they consume at least 50-70% of overall time.</BLOCKQUOTE><SPAN>We created a Jira issue (</SPAN><A href="https://jira.codehaus.org/browse/ACT-1056" rel="nofollow noopener noreferrer">ACT-1056</A><SPAN>) and can contribute a patch if needed.</SPAN><BR /><BR /><SPAN>Some more details concerning our tests:</SPAN><BR /><SPAN>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).</SPAN><BR /><BR /><SPAN>The test process had the following features:</SPAN><BR /><UL><LI>main process:<UL><LI>two ServiceTasks performing db updates (each with asynchronous continiuation)</LI><BR /><LI>two ServiceTasks sending jms notifications back to the test client</LI><BR /><LI>call of a sub process (with asynchronous continiuation)</LI></UL></LI><LI>sub process<UL><LI>two ServiceTasks that call WebServices (SoapUI simulator was used for these WebServices)</LI><BR /><LI>two exclusive gateways</LI></UL></LI></UL><SPAN>Perhaps there are other (more elegant&nbsp; <img id="smileywink" class="emoticon emoticon-smileywink" src="https://connect.hyland.com/i/smilies/16x16_smiley-wink.png" alt="Smiley Wink" title="Smiley Wink" /> ) alternatives that we may have overlooked.</SPAN></BODY></HTML> Fri, 13 Jan 2012 17:15:41 GMT waldorf 2012-01-13T17:15:41Z Performance issue due to dynamic query creation https://connect.hyland.com/t5/alfresco-archive/performance-issue-due-to-dynamic-query-creation/m-p/83962#M56465 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 insta Fri, 13 Jan 2012 17:15:41 GMT https://connect.hyland.com/t5/alfresco-archive/performance-issue-due-to-dynamic-query-creation/m-p/83962#M56465 waldorf 2012-01-13T17:15:41Z Re: Performance issue due to dynamic query creation https://connect.hyland.com/t5/alfresco-archive/performance-issue-due-to-dynamic-query-creation/m-p/83963#M56466 <HTML><HEAD></HEAD><BODY><SPAN>Interesting find. </SPAN><BR /><BR /><SPAN>I'm definitely interested in a patch attached to the jira.</SPAN></BODY></HTML> Mon, 30 Jan 2012 14:40:50 GMT https://connect.hyland.com/t5/alfresco-archive/performance-issue-due-to-dynamic-query-creation/m-p/83963#M56466 jbarrez 2012-01-30T14:40:50Z Re: Performance issue due to dynamic query creation https://connect.hyland.com/t5/alfresco-archive/performance-issue-due-to-dynamic-query-creation/m-p/83964#M56467 <HTML><HEAD></HEAD><BODY><SPAN>ok, I will upload a patch soon</SPAN><BR /><BR /><SPAN>Best regards,</SPAN><BR /><BR /><SPAN>Christian</SPAN></BODY></HTML> Tue, 31 Jan 2012 12:18:38 GMT https://connect.hyland.com/t5/alfresco-archive/performance-issue-due-to-dynamic-query-creation/m-p/83964#M56467 waldorf 2012-01-31T12:18:38Z Re: Performance issue due to dynamic query creation https://connect.hyland.com/t5/alfresco-archive/performance-issue-due-to-dynamic-query-creation/m-p/83965#M56468 <HTML><HEAD></HEAD><BODY><SPAN>Hi,</SPAN><BR /><BR /><SPAN>I attached two patches to the JIRA issue. One for the current trunk, one for Activiti, version 5.8.</SPAN><BR /><SPAN>I am looking forward to the review.</SPAN><BR /><BR /><SPAN>Best regards,</SPAN><BR /><BR /><SPAN>Christian</SPAN></BODY></HTML> Thu, 02 Feb 2012 13:51:30 GMT https://connect.hyland.com/t5/alfresco-archive/performance-issue-due-to-dynamic-query-creation/m-p/83965#M56468 waldorf 2012-02-02T13:51:30Z Re: Performance issue due to dynamic query creation https://connect.hyland.com/t5/alfresco-archive/performance-issue-due-to-dynamic-query-creation/m-p/83966#M56469 <HTML><HEAD></HEAD><BODY><SPAN>Thanks, I'll review them shortly and comment on the issue. Thanks!</SPAN></BODY></HTML> Fri, 03 Feb 2012 08:45:27 GMT https://connect.hyland.com/t5/alfresco-archive/performance-issue-due-to-dynamic-query-creation/m-p/83966#M56469 jbarrez 2012-02-03T08:45:27Z Re: Performance issue due to dynamic query creation https://connect.hyland.com/t5/alfresco-archive/performance-issue-due-to-dynamic-query-creation/m-p/83967#M56470 <HTML><HEAD></HEAD><BODY><SPAN>Hi Jerome,</SPAN><BR /><BR /><SPAN>any news on this? Did you review the patch?</SPAN><BR /><BR /><SPAN>Thanks in advance,</SPAN><BR /><BR /><SPAN>Christian</SPAN></BODY></HTML> Tue, 06 Mar 2012 13:09:02 GMT https://connect.hyland.com/t5/alfresco-archive/performance-issue-due-to-dynamic-query-creation/m-p/83967#M56470 waldorf 2012-03-06T13:09:02Z