Uploaded image for project: 'camunda BPM'
  1. camunda BPM
  2. CAM-9837

Remove inner * selection from job acquisition query

    Details

      Description

      The job acquisition query performs an exists subquery to ensure that no other exclusive jobs are currently locked. This subquery uses select *. It should use select 1 instead, which saves a table access.

      Query:

      SELECT sub.*
      FROM   ( SELECT res.*,
                      Row_number( )
                        OVER (
                          ORDER BY res.priority_ DESC) rnk
               FROM   ( SELECT DISTINCT res.*
                        FROM   ACT_RU_JOB res
                        WHERE  ( res.retries_ > 0 ) AND
                               ( res.duedate_ IS NULL  OR
                                 res.duedate_ <= @P0 ) AND
                               ( res.lock_owner_ IS NULL  OR
                                 res.lock_exp_time_ < @P1 ) AND
                               res.suspension_state_ = 1 AND
                               ( res.deployment_id_ IS NULL  OR
                                 ( res.deployment_id_ IN ( @P2, @P3, @P4, @P5,
                                                           @P6, @P7, @P8, @P9,
                                                           @P10, @P11, @P12, @P13,
                                                           @P14, @P15, @P16, @P17,
                                                           @P18, @P19, @P20, @P21,
                                                           @P22, @P23, @P24 ) ) ) AND
                               ( ( res.exclusive_ = 1 AND
                                   NOT EXISTS
                                       ( SELECT j2.*
                                         FROM   ACT_RU_JOB j2
                                         WHERE  j2.process_instance_id_ = res.process_instance_id_ -- from the same proc. inst.
                                                AND
                                                ( j2.exclusive_ = 1 ) -- also exclusive
                                                AND
                                                ( j2.lock_owner_ IS NOT NULL AND
                                                  j2.lock_exp_time_ >= @P25 ) -- in progress
                                        ) )  OR
                                 res.exclusive_ = 0 ) )res ) sub
      WHERE  sub.rnk >= @P26 AND
             sub.rnk < @P27
      ORDER  BY sub.rnk
      

      SELECT j2.* is where it is at.

        Issue Links

          Activity

          There are no comments yet on this issue.

            People

            • Assignee:
              Unassigned
              Reporter:
              thorben.lindhauer Thorben Lindhauer
            • Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development