topic Performance Issue / Bug ? Querying tasks for process variable values for single variable results in lots of joins in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/performance-issue-bug-querying-tasks-for-process-variable-values/m-p/172476#M125686 <HTML><HEAD></HEAD><BODY><SPAN>Hi All,</SPAN><BR /><BR /><SPAN>I am using TaskQuery to find tasks which belong to processes having certain set of possible process variable values for a single variable</SPAN><BR /><BR /><SPAN>For e.g.,</SPAN><BR /><BR /><SPAN>TaskQuery query = taskService.createTaskQuery();</SPAN><BR /><SPAN>query.processVariableValueEquals("a1", "value1");</SPAN><BR /><SPAN>query.processVariableValueEquals("a1", "value2");</SPAN><BR /><SPAN>query.processVariableValueEquals("a1", "value3");</SPAN><BR /><SPAN>query.processVariableValueEquals("a1", "value4");</SPAN><BR /><SPAN>long count = query.count();</SPAN><BR /><BR /><SPAN>This call results in following SQL</SPAN><BR /><BR /><SPAN>select count(distinct RES.ID_)</SPAN><BR /><SPAN>FROM ACT_RU_TASK RES</SPAN><BR /><SPAN>INNER JOIN ACT_RU_VARIABLE A0 on RES.PROC_INST_ID_ = A0.PROC_INST_ID_</SPAN><BR /><SPAN>INNER JOIN ACT_RU_VARIABLE A1 on RES.PROC_INST_ID_ = A1.PROC_INST_ID_</SPAN><BR /><SPAN>INNER JOIN ACT_RU_VARIABLE A2 on RES.PROC_INST_ID_ = A2.PROC_INST_ID_</SPAN><BR /><SPAN>INNER JOIN ACT_RU_VARIABLE A3 on RES.PROC_INST_ID_ = A3.PROC_INST_ID_</SPAN><BR /><SPAN>WHERE A0.TASK_ID_ is null and A0.NAME_= 'a1' and A0.TYPE_ = 'string' and A0.TEXT_='value1'</SPAN><BR /><SPAN>AND</SPAN><BR /><SPAN>A1.TASK_ID_ is null and A1.NAME_= 'a1' and A1.TYPE_ = 'string' and A1.TEXT_ = 'value1'</SPAN><BR /><SPAN>AND</SPAN><BR /><SPAN>A2.TASK_ID_ is null and A2.NAME_= 'a1' and A2.TYPE_ = 'string' and A2.TEXT_ = 'value1'</SPAN><BR /><SPAN>AND</SPAN><BR /><SPAN>A3.TASK_ID_ is null and A3.NAME_= 'a1' and A3.TYPE_ = 'string' and A3.TEXT_ = 'value1';</SPAN><BR /><BR /><BR /><BR /><SPAN>As you can see that as the number of variables values increase, so does the number of joins.</SPAN><BR /><SPAN>This results in huge time for the query so it won't be practical to use this method for many values.</SPAN><BR /><BR /><SPAN>Is this a bug or there a better way of doing this ?</SPAN></BODY></HTML> Fri, 16 May 2014 17:18:48 GMT shailendra1 2014-05-16T17:18:48Z Performance Issue / Bug ? Querying tasks for process variable values for single variable results in lots of joins https://connect.hyland.com/t5/alfresco-archive/performance-issue-bug-querying-tasks-for-process-variable-values/m-p/172476#M125686 Hi All,I am using TaskQuery to find tasks which belong to processes having certain set of possible process variable values for a single variableFor e.g.,TaskQuery query = taskService.createTaskQuery();query.processVariableValueEquals("a1", "value1");query.processVariableValueEquals("a1", "value2");q Fri, 16 May 2014 17:18:48 GMT https://connect.hyland.com/t5/alfresco-archive/performance-issue-bug-querying-tasks-for-process-variable-values/m-p/172476#M125686 shailendra1 2014-05-16T17:18:48Z Re: Performance Issue / Bug ? Querying tasks for process variable values for single variable results in lots of joins https://connect.hyland.com/t5/alfresco-archive/performance-issue-bug-querying-tasks-for-process-variable-values/m-p/172477#M125687 <HTML><HEAD></HEAD><BODY><SPAN>Hi All,</SPAN><BR /><BR /><BR /><SPAN>Any comments please ? This issue is very important for us !</SPAN></BODY></HTML> Mon, 19 May 2014 07:33:26 GMT https://connect.hyland.com/t5/alfresco-archive/performance-issue-bug-querying-tasks-for-process-variable-values/m-p/172477#M125687 shailendra1 2014-05-19T07:33:26Z Re: Performance Issue / Bug ? Querying tasks for process variable values for single variable results in lots of joins https://connect.hyland.com/t5/alfresco-archive/performance-issue-bug-querying-tasks-for-process-variable-values/m-p/172478#M125688 <HTML><HEAD></HEAD><BODY><SPAN>Hi Shailendra.</SPAN><BR /><BR /><SPAN>In case that you can create your own query (more efficient one), you can use native query support.</SPAN><BR /><A href="http://www.activiti.org/userguide/#queryAPI" rel="nofollow noopener noreferrer">http://www.activiti.org/userguide/#queryAPI</A><BR /><BR /><SPAN>Regards</SPAN><BR /><SPAN>Martin</SPAN></BODY></HTML> Tue, 20 May 2014 06:38:26 GMT https://connect.hyland.com/t5/alfresco-archive/performance-issue-bug-querying-tasks-for-process-variable-values/m-p/172478#M125688 martin_grofcik 2014-05-20T06:38:26Z Re: Performance Issue / Bug ? Querying tasks for process variable values for single variable results in lots of joins https://connect.hyland.com/t5/alfresco-archive/performance-issue-bug-querying-tasks-for-process-variable-values/m-p/172479#M125689 <HTML><HEAD></HEAD><BODY><SPAN>Thanks Martin. As of now I will be using native query support.</SPAN></BODY></HTML> Wed, 21 May 2014 09:36:04 GMT https://connect.hyland.com/t5/alfresco-archive/performance-issue-bug-querying-tasks-for-process-variable-values/m-p/172479#M125689 shailendra1 2014-05-21T09:36:04Z