topic Activiti SQL query performance in Alfresco Forum https://connect.hyland.com/t5/alfresco-forum/activiti-sql-query-performance/m-p/40455#M16954 <HTML><HEAD></HEAD><BODY><P>During tuning long time queries, we&nbsp;observed&nbsp;this query</P><P>select&nbsp;distinct RES.*, DEF.KEY_ as PROC_DEF_KEY_, DEF.NAME_ as PROC_DEF_NAME_, DEF.VERSION_ as PROC_DEF_VERSION_, DEF.DEPLOYMENT_ID_ as DEPLOYMENT_ID_,<BR /> VAR.ID_ as VAR_ID_, <BR /> VAR.NAME_ as VAR_NAME_, <BR /> VAR.VAR_TYPE_ as VAR_TYPE_, <BR /> VAR.REV_ as VAR_REV_,<BR /> VAR.PROC_INST_ID_ as VAR_PROC_INST_ID_, <BR /> VAR.EXECUTION_ID_ as VAR_EXECUTION_ID_, <BR /> VAR.TASK_ID_ as VAR_TASK_ID_,<BR /> VAR.BYTEARRAY_ID_ as VAR_BYTEARRAY_ID_, <BR /> VAR.DOUBLE_ as VAR_DOUBLE_, <BR /> VAR.TEXT_ as VAR_TEXT_, <BR /> VAR.TEXT2_ as VAR_TEXT2_, <BR /> VAR.LAST_UPDATED_TIME_ as VAR_LAST_UPDATED_TIME_, <BR /> VAR.LONG_ as VAR_LONG_<BR /> from ACT_HI_PROCINST RES<BR /> left outer join ACT_RE_PROCDEF DEF on RES.PROC_DEF_ID_ = DEF.ID_<BR /> left outer join ACT_HI_VARINST VAR ON RES.PROC_INST_ID_ = VAR.EXECUTION_ID_ and VAR.TASK_ID_ is null<BR /> order by RES.START_TIME_ desc, VAR.LAST_UPDATED_TIME_ asc<BR /> LIMIT 20 OFFSET 0;</P><P></P><P>This query search process instance with process variables.</P><P></P><P>This query without distinct return same count of record like with it.</P><P>But time for query decrease for 6 times.</P><P>This query name is "<SPAN style="color: #032f62; background-color: #ffffff;">selectHistoricProcessInstancesByQueryCriteria" from&nbsp;HistoricProcessInstance.xml.</SPAN></P><P></P><P><SPAN style="color: #032f62; background-color: #ffffff;">I think, distinct should be removed from this query.</SPAN></P></BODY></HTML> Wed, 05 Sep 2018 14:31:21 GMT yurifun 2018-09-05T14:31:21Z Activiti SQL query performance https://connect.hyland.com/t5/alfresco-forum/activiti-sql-query-performance/m-p/40455#M16954 During tuning long time queries, we&nbsp;observed&nbsp;this queryselect&nbsp;distinct RES.*, DEF.KEY_ as PROC_DEF_KEY_, DEF.NAME_ as PROC_DEF_NAME_, DEF.VERSION_ as PROC_DEF_VERSION_, DEF.DEPLOYMENT_ID_ as DEPLOYMENT_ID_, VAR.ID_ as VAR_ID_, VAR.NAME_ as VAR_NAME_, VAR.VAR_TYPE_ as VAR_TYPE_, VAR.REV_ as VAR_RE Wed, 05 Sep 2018 14:31:21 GMT https://connect.hyland.com/t5/alfresco-forum/activiti-sql-query-performance/m-p/40455#M16954 yurifun 2018-09-05T14:31:21Z Re: Activiti SQL query performance https://connect.hyland.com/t5/alfresco-forum/activiti-sql-query-performance/m-p/40456#M16955 <HTML><HEAD></HEAD><BODY><P>There was a reason why the distinct keyword was added. Maybe not needed for all databases but in some it used to return duplicate records. You can use a native query or execute custom SQL&nbsp;to get a better performant query&nbsp;<A class="link-titled" href="https://www.activiti.org/5.x/userguide/#advanced.custom.sql.queries" title="https://www.activiti.org/5.x/userguide/#advanced.custom.sql.queries" rel="nofollow noopener noreferrer">Activiti User Guide</A>&nbsp;</P></BODY></HTML> Tue, 11 Sep 2018 15:28:56 GMT https://connect.hyland.com/t5/alfresco-forum/activiti-sql-query-performance/m-p/40456#M16955 bassam_al-saror 2018-09-11T15:28:56Z