]
Bostjan Dolenc commented on JBRULES-2732:
-----------------------------------------
I've had to implemented this change myself as SQL was not property generated for this
query when running on Oracle. I probably could contribute it for inclusion in the trunk,
but it's very trivial change anyway.
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: All
Affects Versions: 5.1.1.FINAL
Environment: N/A
Reporter: Anatoly Polinsky
Assignee: Mark Proctor
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: