Uploaded image for project: 'jBPM'
  1. jBPM
  2. JBPM-3206

ProcessInstancesWaitingForEvent Query is Extremely Inefficient and Should Be Simplified

    XMLWordPrintable

Details

    • Low

    Description

      "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

      Attachments

        Activity

          People

            kverlaen@redhat.com Kris Verlaenen
            Anat0ly Anatoly Polinsky (Inactive)
            Votes:
            2 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Time Tracking

                Estimated:
                Original Estimate - 2 hours
                2h
                Remaining:
                Remaining Estimate - 2 hours
                2h
                Logged:
                Time Spent - Not Specified
                Not Specified