Performance issue querying process instances by variable name and value

Solution In Progress - Updated -

Issue

We are seeing slow response times when fetching process instances by variable name and value. The DBA noticed the following slow sql, taking several seconds during a load test:

select processins0_.processInstanceId as col_0_0_, processins0_.processId as col_1_0_, processins0_.processName as col_2_0_, processins0_.processVersion as col_3_0_, processins0_.status ascol_4_0_, processins0_.externalId as col_5_0_, processins0_.start_date as col_6_0_, processins0_.user_identity as col_7_0_, processins0_.processInstanceDescription as col_8_0_, processins0_.correlationKey as col_9_0_, processins0_.parentProcessInstanceId as col_10_0_ from ProcessInstanceLog processins0_ where (processins0_.status in (? , ?)) and(processins0_.processInstanceId in (select variablein1_.processInstanceId from VariableInstanceLog variablein1_ where variablein1_.variableId=? and (variablein1_.value like ?) and(variablein1_.id in (select max(variablein2_.id) from VariableInstanceLog variablein2_ where variablein2_.variableId=variablein1_.variableId group by variablein2_.processInstanceId)))) and(processins0_.externalId in (? , ? , ..., ?)) order by processins0_.processInstanceId ASC limit ?
0.27 / min4.16 s

Environment

  • Red Hat JBoss BPM Suite
    • 6.4
  • Red Hat Process Automation Manager
    • 7.x

Subscriber exclusive content

A Red Hat subscription provides unlimited access to our knowledgebase of over 48,000 articles and solutions.

Current Customers and Partners

Log in for full access

Log In