[GSS](6.4.z) Severe performance issue with "/query/runtime/process" endpoint

By default the endpoint tries to get the last value of the process variables. It results in a subquery inside the main query and it is basically the reasons to make it hang: select distinct variablein0_.id as id1_37_, variablein0_.log_date as log_date2_37_, variablein0_.externalId as external3_37_, variablein0_.oldValue as oldValue4_37_, variablein0_.processId as processI5_37_, variablein0_.processInstanceId as processI6_37_, variablein0_.value as value7_37_, variablein0_.variableId as variable8_37_, variablein0_.variableInstanceId as variable9_37_ from VariableInstanceLog variablein0_ where (variablein0_.id in (select max(variablein1_.id) from VariableInstanceLog variablein1_ group by variablein1_.variableId , variablein1_.processInstanceId)) and variablein0_.processInstanceId=1 order by variablein0_.processInstanceId asc The workaround is to get all the process variables values for the given endpoint using the query parameter "all": select distinct variablein0_.id as id1_37_, variablein0_.log_date as log_date2_37_, variablein0_.externalId as external3_37_, variablein0_.oldValue as oldValue4_37_, variablein0_.processId as processI5_37_, variablein0_.processInstanceId as processI6_37_, variablein0_.value as value7_37_, variablein0_.variableId as variable8_37_, variablein0_.variableInstanceId as variable9_37_ from VariableInstanceLog variablein0_ where variablein0_.processInstanceId=1 order by variablein0_.processInstanceId asc It results on a great performance improvement: [wsiqueir@wsiqueir kie-remote]$ time curl -u 'bpmsAdmin:redhat2014!' -X GET 'http: //localhost:8080/business-central/ rest /query/runtime/process?s=1' <?xml version= "1.0" encoding= "UTF-8" standalone= "yes" ?><query-process-instance-result><processInstanceInfoList><process-instance><process-id>get_redhat_proj.main_proc</process-id><id>1</id><state>2</state><parentProcessInstanceId>-1</parentProcessInstanceId></process-instance><variables><name>processId</name><string>project1.main</string><lastModificationDate>2016-09-05T14:39:26-03:00</lastModificationDate></variables><variables><name>initiator</name><string>bpmsAdmin</string><lastModificationDate>2016-10-31T18:59:01-02:00</lastModificationDate></variables></processInstanceInfoList></query-process-instance-result> real 0m16.025s user 0m0.002s sys 0m0.004s [wsiqueir@wsiqueir kie-remote]$ time curl -u 'bpmsAdmin:redhat2014!' -X GET 'http: //localhost:8080/business-central/ rest /query/runtime/process?s=1&all' <?xml version= "1.0" encoding= "UTF-8" standalone= "yes" ?><query-process-instance-result><processInstanceInfoList><process-instance><process-id>get_redhat_proj.main_proc</process-id><id>1</id><state>2</state><parentProcessInstanceId>-1</parentProcessInstanceId></process-instance><variables><name>initiator</name><string>bpmsAdmin</string><lastModificationDate>2016-06-13T21:05:17-03:00</lastModificationDate></variables><variables><name>initiator</name><string>bpmsAdmin</string><lastModificationDate>2016-08-24T16:41:24-03:00</lastModificationDate></variables><variables><name>processId</name><string>project1.main</string><lastModificationDate>2016-09-05T14:39:26-03:00</lastModificationDate></variables><variables><name>initiator</name><string>bpmsAdmin</string><lastModificationDate>2016-09-05T14:39:26-03:00</lastModificationDate></variables><variables><name>initiator</name><string>bpmsAdmin</string><lastModificationDate>2016-10-31T18:59:01-02:00</lastModificationDate></variables></processInstanceInfoList></query-process-instance-result> real 0m0.015s user 0m0.002s sys 0m0.004s An easy solution for this issue is only filter the variable on the application side, once you have the process list, go one by one and filter the list of variable values to get the last one instead doing it in the database query.