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

Task inbox fails to display data due to inefficient jbpmHumanTasksWithUser query

    XMLWordPrintable

Details

    • 2021 Week 07-09 (from Feb 15)

    Description

      Running RHPAM against a DB with some test data (a few thousand records), opening the Task Inbox displays a spinner for 60s before it fails with an ERROR:

      Unable to complete your request. The following exception occurred: No available endpoints found.
      

      The log file shows the following events:

      2021-02-17 14:51:57,152 DEBUG [org.kie.server.services.jbpm.QueryDataServiceBase] (default task-6) About to perform query 'jbpmHumanTasksWithUser' with page 0 and page size 10
      2021-02-17 14:51:57,154 DEBUG [org.jbpm.kie.services.impl.query.QueryServiceImpl] (default task-6) About to query using jbpmHumanTasksWithUser definition with number of rows 10 and starting at 0 offset
      2021-02-17 14:51:57,160 DEBUG [org.jbpm.kie.services.impl.query.QueryServiceImpl] (default task-6) Applying order by createdOn and ascending false
      2021-02-17 14:51:57,161 DEBUG [org.jbpm.services.task.identity.JAASUserGroupCallbackImpl] (default task-6) Adding roles from JAAS subject
      2021-02-17 14:51:57,163 DEBUG [org.jbpm.kie.services.impl.query.preprocessor.PotOwnerTasksPreprocessor] (default task-6) Adding column filter: ((ENTITY_ID is_null  OR ENTITY_ID != rhpamAdmin) AND ((ID = kie-server, admin, rest-all, rhpamAdm
      in AND (ACTUALOWNER =  OR ACTUALOWNER is_null )) OR ACTUALOWNER = rhpamAdmin))
      ....
      2021-02-17 14:53:42,649 DEBUG [org.jbpm.kie.services.impl.query.QueryServiceImpl] (default task-6) Query result is org.dashbuilder.dataset.impl.DataSetImpl@47056ec7
      

      Enabling JDBC spy logging shows that the following query gets executed:

      SELECT activationTime AS `activationTime`, actualOwner AS `actualOwner`, createdBy AS `createdBy`, createdOn AS `createdOn`, deploymentId AS `deploymentId`, description AS `description`, dueDate AS `dueDate`, name AS `name`, parentId AS `parentId`, priority AS `priority`, processId AS `processId`, processInstanceId AS `processInstanceId`, processSessionId AS `processSessionId`, status AS `status`, taskId AS `taskId`, workItemId AS `workItemId`, lastModificationDate AS `lastModificationDate`, correlationKey AS `correlationKey`, processInstanceDescription AS `processInstanceDescription`, slaCompliance AS `slaCompliance`, sla_due_date AS `sla_due_date` FROM (select t.activationTime, t.actualOwner, t.createdBy, t.createdOn, t.deploymentId, t.description, t.dueDate, t.name, t.parentId, t.priority, t.processId, t.processInstanceId, t.processSessionId, t.status, t.taskId, t.workItemId, t.lastModificationDate, pil.correlationKey, pil.processInstanceDescription , oe.id, eo.entity_id, nil.sla_due_date, nil.slaCompliance from AuditTaskImpl t left join PeopleAssignments_PotOwners po on t.taskId=po.task_id left join OrganizationalEntity oe on po.entity_id=oe.id left join ProcessInstanceLog pil on pil.processInstanceId=t.processInstanceId left join PeopleAssignments_ExclOwners eo on t.taskId=eo.task_id left join NodeInstanceLog nil on nil.workItemId=t.workItemId) AS `dbSQL` WHERE (status = 'Ready' OR status = 'Reserved' OR status = 'InProgress') AND ((entity_id IS NULL OR entity_id <> 'rhpamAdmin') AND (((id = 'kie-server' OR id = 'admin' OR id = 'rest-all' OR id = 'rhpamAdmin') AND (actualOwner = '' OR actualOwner IS NULL)) OR actualOwner = 'rhpamAdmin')) GROUP BY taskId, activationTime, actualOwner, createdBy, createdOn, deploymentId, description, dueDate, name, parentId, priority, processId, processInstanceId, processSessionId, status, workItemId, lastModificationDate, correlationKey, processInstanceDescription, slaCompliance, sla_due_date ORDER BY createdOn DESC
      

      and all records (in my case 5987) are retrieved from the database. Pagination seems to be applied afterwards.

      Executing the same query against the DB directly:

      5987 rows in set (1 min 39.333 sec)
      

      And here is the EXPLAIN select:

      +------+-------------+-------+--------+--------------------------+-----------------------+---------+----------------------------+-------+--------------------------------------------------------+
      | id   | select_type | table | type   | possible_keys            | key                   | key_len | ref                        | rows  | Extra                                                  |
      +------+-------------+-------+--------+--------------------------+-----------------------+---------+----------------------------+-------+--------------------------------------------------------+
      |    1 | SIMPLE      | t     | ALL    | IDX_AuditTaskImpl_status | NULL                  | NULL    | NULL                       | 13473 | Using where; Using temporary; Using filesort           |
      |    1 | SIMPLE      | po    | ref    | IDX_PAsPot_TaskEntity    | IDX_PAsPot_TaskEntity | 8       | rhpam7.t.taskId            | 1     | Using where; Using index                               |
      |    1 | SIMPLE      | oe    | eq_ref | PRIMARY                  | PRIMARY               | 257     | rhpam7.po.entity_id        | 1     | Using where; Using index                               |
      |    1 | SIMPLE      | pil   | ref    | IDX_PInstLog_pInstId     | IDX_PInstLog_pInstId  | 8       | rhpam7.t.processInstanceId | 1     |                                                        |
      |    1 | SIMPLE      | eo    | ALL    | IDX_PAsExcl_Task         | NULL                  | NULL    | NULL                       | 1     | Using where; Using join buffer (flat, BNL join)        |
      |    1 | SIMPLE      | nil   | ALL    | NULL                     | NULL                  | NULL    | NULL                       | 53452 | Using where; Using join buffer (incremental, BNL join) |
      +------+-------------+-------+--------+--------------------------+-----------------------+---------+----------------------------+-------+--------------------------------------------------------+
      

      Note that in the customers case, the DB is much bigger, with lots of data in the audit log tables. Can we change this query to not use those log tables?
       

      Attachments

        Issue Links

          Activity

            People

              ftirados Francisco Javier Tirado Sarti
              ftirados Francisco Javier Tirado Sarti
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: