topic Sql performance in Alfresco Archive https://connect.hyland.com/t5/alfresco-archive/sql-performance/m-p/151549#M106293 <HTML><HEAD></HEAD><BODY><SPAN>Hello!! I have a problem with an sql, it takes to long to return the data. Maybe this is not the place ask it, but I do not know how to improve it, so any suggestion are wellcome. This is the sql:</SPAN><BR /><BR /><SPAN>SELECT DISTINCT</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; TASK.* </SPAN><BR /><SPAN>FROM</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; act_hi_taskinst TASK</SPAN><BR /><SPAN>INNER JOIN act_hi_varinst VARFLAG</SPAN><BR /><SPAN>ON</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; (</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TASK.ID_ = VARFLAG.TASK_ID_</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; AND VARFLAG.NAME_ = 'flag'</SPAN><BR /><SPAN>&nbsp;&nbsp;&nbsp; )</SPAN><BR /><SPAN>ORDER BY VARFLAG.TEXT_ LIMIT 15,15;</SPAN><BR /><BR /><SPAN>&nbsp; explain:</SPAN><BR /><BR /><SPAN>id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; select_type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; table&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; type&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; possible_keys&nbsp;&nbsp; key&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; key_len&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ref&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; rows&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Extra</SPAN><BR /><SPAN>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "SIMPLE"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "VARFLAG"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "ref"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "ACT_IDX_HI_PROCVAR_NAME_TYPE"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "ACT_IDX_HI_PROCVAR_NAME_TYPE" "767"&nbsp;&nbsp;&nbsp;&nbsp; "const" 106502&nbsp; "Using where; Using temporary; Using filesort"</SPAN><BR /><SPAN>1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "SIMPLE"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "TASK"&nbsp; "eq_ref"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "PRIMARY"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "PRIMARY"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; "194"&nbsp;&nbsp;&nbsp;&nbsp; "activity_perf.VARFLAG.TASK_ID_"&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ""</SPAN><BR /><SPAN>Time : 30~40 segundos.</SPAN><BR /><SPAN>Count(*) act_hi_taskinst = 50.000 rows</SPAN><BR /><SPAN>Count(*) act_hi_varinst = 200.000 rows</SPAN><BR /><BR /><SPAN>Thanks!</SPAN><BR /></BODY></HTML> Tue, 29 Oct 2013 16:46:59 GMT yahekia 2013-10-29T16:46:59Z Sql performance https://connect.hyland.com/t5/alfresco-archive/sql-performance/m-p/151549#M106293 Hello!! I have a problem with an sql, it takes to long to return the data. Maybe this is not the place ask it, but I do not know how to improve it, so any suggestion are wellcome. This is the sql<IMG id="smileyfrustrated" class="emoticon emoticon-smileyfrustrated" src="https://migration33.stage.lithium.com/i/smilies/16x16_smiley-frustrated.png" alt="Smiley Frustrated" title="Smiley Frustrated" />ELECT DISTINCT&nbsp;&nbsp;&nbsp; TASK.* FROM&nbsp;&nbsp;&nbsp; act_hi_taskinst TASKINNER JOIN act_hi_varinst VARFLAGON&nbsp;&nbsp;&nbsp; (&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TA Tue, 29 Oct 2013 16:46:59 GMT https://connect.hyland.com/t5/alfresco-archive/sql-performance/m-p/151549#M106293 yahekia 2013-10-29T16:46:59Z Re: Sql performance https://connect.hyland.com/t5/alfresco-archive/sql-performance/m-p/151550#M106294 <HTML><HEAD></HEAD><BODY><SPAN>You should try posting that question on a SQL forum, or maybe on Stackoverflow.com</SPAN></BODY></HTML> Wed, 30 Oct 2013 17:56:14 GMT https://connect.hyland.com/t5/alfresco-archive/sql-performance/m-p/151550#M106294 felipe1 2013-10-30T17:56:14Z Re: Sql performance https://connect.hyland.com/t5/alfresco-archive/sql-performance/m-p/151551#M106295 <HTML><HEAD></HEAD><BODY><SPAN>Which database are you using? Make sure you got the right indexes for this query to start with.</SPAN><BR /><BR /><SPAN>Best regards,</SPAN></BODY></HTML> Thu, 31 Oct 2013 08:54:53 GMT https://connect.hyland.com/t5/alfresco-archive/sql-performance/m-p/151551#M106295 trademak 2013-10-31T08:54:53Z Re: Sql performance https://connect.hyland.com/t5/alfresco-archive/sql-performance/m-p/151552#M106296 <HTML><HEAD></HEAD><BODY><SPAN>We are using mysql, and the indexes we have are from the create script and one custom index to improve another queries, but with this one no indexes helps us to improve the performance.</SPAN><BR /><BR /><SPAN>So our solution for now is transform this variables into jpa entitiy instead of variables, because we were using the variables to extend the task entity. And in the future, we will add an external index like elasticsearch to avoid this kind of problems with orders.</SPAN><BR /><BR /><SPAN>Do you think is a good approach?? We plan to have more than one million of task….</SPAN></BODY></HTML> Mon, 04 Nov 2013 11:18:59 GMT https://connect.hyland.com/t5/alfresco-archive/sql-performance/m-p/151552#M106296 yahekia 2013-11-04T11:18:59Z Re: Sql performance https://connect.hyland.com/t5/alfresco-archive/sql-performance/m-p/151553#M106297 <HTML><HEAD></HEAD><BODY><SPAN>Yes that's a perfectly fine approach. </SPAN><BR /><BR /><SPAN>Best regards,</SPAN></BODY></HTML> Tue, 05 Nov 2013 13:09:07 GMT https://connect.hyland.com/t5/alfresco-archive/sql-performance/m-p/151553#M106297 trademak 2013-11-05T13:09:07Z