[jboss-jira] [JBoss JIRA] Updated: (JBRULES-2732) ProcessInstancesWaitingForEvent Query is Extremely Inefficient and Should Be Simplified

[Kris Verlaenen (JIRA)]

     [ https://issues.jboss.org/browse/JBRULES-2732?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Kris Verlaenen updated JBRULES-2732:
------------------------------------

    Component/s: drools-core (flow)
                     (was: All)


> ProcessInstancesWaitingForEvent Query is Extremely Inefficient and Should Be Simplified
> ---------------------------------------------------------------------------------------
>
>                 Key: JBRULES-2732
>                 URL: https://issues.jboss.org/browse/JBRULES-2732
>             Project: Drools
>          Issue Type: Bug
>      Security Level: Public(Everyone can see) 
>          Components: drools-core (flow)
>    Affects Versions: 5.1.1.FINAL
>         Environment: N/A
>            Reporter: Anatoly Polinsky
>            Assignee: Kris Verlaenen
>              Labels: drools, drools-flow, persistence
>   Original Estimate: 2 hours
>  Remaining Estimate: 2 hours
>
> "ProcessInstancesWaitingForEvent" query that is used after each process / subprocess ends is extremely inefficient, and since there is no way to force an INDEX without being coupled with JPA provider, this query execution time increase exponentially as the number of processes goes up. Currently with only 20000 processes, the query takes 18(!!!) seconds to execute:
> <named-query name="ProcessInstancesWaitingForEvent">
>     <query>
>         SELECT processInstanceInfo.processInstanceId
>         FROM ProcessInstanceInfo processInstanceInfo
>         WHERE :type in elements ( processInstanceInfo.eventTypes )
>     </query>
> </named-query> 
> It is inefficient not only because there is no INDEX enforced, but because it goes into the sub query for no good reason. The better/correct query is:
> SELECT processInstanceInfo.processInstanceId
> FROM ProcessInstanceInfo processInstanceInfo, EventTypes eventTypes
> WHERE eventTypes.element = :type
> AND processInstanceInfo.processInstanceId = eventTypes.processInstanceId 
> In order to get to this "better" query, since it is a JPA one, an EventType/s entity should be created ( it does not currently exist in 5.1.1 ).
> /Anatoly

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira