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

Task Query with Authorization and Identity link are slow

    XMLWordPrintable

    Details

    • Type: Bug Report
    • Status: Open
    • Priority: L3 - Default
    • Resolution: Unresolved
    • Affects Version/s: 7.5.0, 7.6.0
    • Fix Version/s: 7.5.x, 7.6.x, 7.7.x
    • Component/s: engine
    • Labels:
      None

      Description

      The task query in combination with authorization and identity link are slow on MySQL.

      To reproduce start 5000 process instances of the invoice process.

      EXPLAIN SELECT Count( DISTINCT res.id_ )
      FROM ACT_RU_TASK res
      inner join ACT_RU_IDENTITYLINK i
      ON i.task_id_ = res.id_
      left join ACT_RE_PROCDEF procdef
      ON res.proc_def_id_ = procdef.id_
      left join ( SELECT a.*
      FROM ACT_RU_AUTHORIZATION a
      WHERE a.type_ < 2 AND
      ( a.user_id_ IN ( 'demo', '*' ) OR
      a.group_id_ IN ( 'camunda-admin', 'management', 'accounting', 'sales' ) ) AND
      (( a.resource_type_ = 7 AND
      a.perms_ & 2 = 2 OR
      a.resource_type_ = 6 AND
      a.perms_ & 64 = 64 )) ) auth
      ON ( auth.resource_id_ IN ( res.id_, procdef.key_, '*' ) ) #<-- this is the problematic part 
      WHERE res.assignee_ IS NULL AND
      res.assignee_ IS NULL AND
       i.type_ = 'candidate' AND
      ( i.group_id_ IN ( 'accounting', 'camunda-admin', 'management', 'sales' ) ) AND
       res.suspension_state_ = 1 AND
      ( ( res.case_execution_id_ IS NOT NULL ) OR
      ( auth.resource_id_ IS NOT NULL ) );
      

      With authorization the query returns a result after 30s, without authorization after 1 s.
      On 7.4 with the old authorization query it runs as well much faster.

      Problem is that on this combination the existing indices are not used by MySQL.

      Hint:
      Since we test in the performance test suite the task query with authorization and 50k task.
      I assume that the combination with the identity link table is the problem.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              christopher.zell Christopher Zell
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated: