POST for List of tasks is slow

Markus · April 28, 2017, 9:55am

Hi,

I implemented a custom tasklist using this REST operation https://docs.camunda.org/manual/7.6/reference/rest/task/post-query/ to query for tasks.

The post request sorts result by priority and uses pagination so that response will contain only 10 tasks.

Maybe filtering for string process variable could be part of the request.

Camunda authorization is enabled so authorizations will be checked when querying in database.

Now there is a group of users which are allowed to READ nearly all tasks. Actually there are 100000 tasks in database.

My problem is that query took very long. Nearly 8 seconds. Is that normal performance for such a number of tasks?

Below is a example request for Rest API

Query String parameters
firstResult:0
maxResults:10

Request payload
{"unassigned":true, "sorting" :[{"sortBy":"priority", "sortOrder":"desc"}, {"sortBy": "created","sordOrder":"desc"}], "processVariables": []}

Best regards,

Markus

mppfor_manu · April 28, 2017, 2:19pm

Yes, I would say that it is probably normal. However, this depends upon your environment (hardware, capacity, etc.).

If you want better performance, you may need to apply additional indices to the database. We’ve done that in the past and saw order of magnitude improvements in performance. The challenge is finding exactly what you need to index.

Markus · April 28, 2017, 3:09pm

It would be enough if query without filter but with sorting on priority would be fast but a index on priority is already set.

Markus · July 7, 2017, 6:20am

Hi again,

We analyzed the query which is fired against database.

The following query is the actual one which is very slow.

SELECT DISTINCT RES.REV_
               ,RES.ID_
               ,RES.NAME_
               ,RES.PARENT_TASK_ID_
               ,RES.DESCRIPTION_
               ,RES.PRIORITY_
               ,RES.CREATE_TIME_
               ,RES.OWNER_
               ,RES.ASSIGNEE_
               ,RES.DELEGATION_
               ,RES.EXECUTION_ID_
               ,RES.PROC_INST_ID_
               ,RES.PROC_DEF_ID_
               ,RES.CASE_EXECUTION_ID_
               ,RES.CASE_INST_ID_
               ,RES.CASE_DEF_ID_
               ,RES.TASK_DEF_KEY_
               ,RES.DUE_DATE_
               ,RES.FOLLOW_UP_DATE_
               ,RES.SUSPENSION_STATE_
               ,RES.TENANT_ID_
FROM ACT_RU_TASK    RES
     INNER JOIN ACT_RE_PROCDEF D ON RES.PROC_DEF_ID_ = D.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 ('l.mittellos', '*')
                 OR A.GROUP_ID_ IN ('DEPARTMENT_GROUP'))
            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_, '*'))
WHERE     RES.ASSIGNEE_ IS NULL
      AND D.NAME_ LIKE '%%'
      AND (   (RES.CASE_EXECUTION_ID_ IS NOT NULL)
           OR (AUTH.RESOURCE_ID_ IS NOT NULL))
      AND (RES.TENANT_ID_ IS NULL)
ORDER BY RES.PRIORITY_ DESC, RES.CREATE_TIME_ DESC
LIMIT 11
OFFSET 0

It took more than 1 minute on a mysql database with 8200 entries in ACT_RU_TASK.
Authorization is enabled.

We refactored this query to use a union.

SELECT DISTINCT RES.REV_
               ,RES.ID_
               ,RES.NAME_
               ,RES.PARENT_TASK_ID_
               ,RES.DESCRIPTION_
               ,RES.PRIORITY_
               ,RES.CREATE_TIME_
               ,RES.OWNER_
               ,RES.ASSIGNEE_
               ,RES.DELEGATION_
               ,RES.EXECUTION_ID_
               ,RES.PROC_INST_ID_
               ,RES.PROC_DEF_ID_
               ,RES.CASE_EXECUTION_ID_
               ,RES.CASE_INST_ID_
               ,RES.CASE_DEF_ID_
               ,RES.TASK_DEF_KEY_
               ,RES.DUE_DATE_
               ,RES.FOLLOW_UP_DATE_
               ,RES.SUSPENSION_STATE_
               ,RES.TENANT_ID_
FROM ACT_RU_TASK    RES
     INNER JOIN ACT_RE_PROCDEF D ON RES.PROC_DEF_ID_ = D.ID_
      JOIN ACT_RE_PROCDEF PROCDEF ON RES.PROC_DEF_ID_ = PROCDEF.ID_
      JOIN
     (SELECT A.*
      FROM ACT_RU_AUTHORIZATION A
      WHERE     A.TYPE_ < 2
            AND (   A.USER_ID_ IN ('l.mittellos', '*')
                 OR A.GROUP_ID_ IN ('DEPARTMENT_GROUP'))
            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_, '*'))
WHERE     RES.ASSIGNEE_ IS NULL
      AND D.NAME_ LIKE '%%'   
      AND (RES.TENANT_ID_ IS NULL)
union
SELECT DISTINCT RES.REV_
               ,RES.ID_
               ,RES.NAME_
               ,RES.PARENT_TASK_ID_
               ,RES.DESCRIPTION_
               ,RES.PRIORITY_
               ,RES.CREATE_TIME_
               ,RES.OWNER_
               ,RES.ASSIGNEE_
               ,RES.DELEGATION_
               ,RES.EXECUTION_ID_
               ,RES.PROC_INST_ID_
               ,RES.PROC_DEF_ID_
               ,RES.CASE_EXECUTION_ID_
               ,RES.CASE_INST_ID_
               ,RES.CASE_DEF_ID_
               ,RES.TASK_DEF_KEY_
               ,RES.DUE_DATE_
               ,RES.FOLLOW_UP_DATE_
               ,RES.SUSPENSION_STATE_
               ,RES.TENANT_ID_
FROM ACT_RU_TASK    RES
     INNER JOIN ACT_RE_PROCDEF D ON RES.PROC_DEF_ID_ = D.ID_
      JOIN ACT_RE_PROCDEF PROCDEF ON RES.PROC_DEF_ID_ = PROCDEF.ID_
WHERE     RES.ASSIGNEE_ IS NULL
      AND D.NAME_ LIKE '%%'      
      AND RES.CASE_EXECUTION_ID_ IS NOT NULL
      AND (RES.TENANT_ID_ IS NULL)    
ORDER BY PRIORITY_ DESC, CREATE_TIME_ DESC
LIMIT 11
OFFSET 0;

Its the same result but it takes only about 3 seconds to execute it.
Maybe the query which is executed in version 7.7 could be refactored in one of the next versions because in combination with authorization it is very slow at the moment.

Best regards,

Markus

mppfor_manu · July 13, 2017, 5:20pm

What you experienced is pretty common. I think the queries used by Camunda have to be fairly generic as they support a wide variety of databases. We’ve also found ways to optimize performance through the use of additional indices.

The challenge is that you must then maintain these changes through version upgrades.

hawky4s · July 13, 2017, 7:25pm

Hi @Markus,

It would be nice if you could create a jira issue with your findings and your improvements.

Cheers,
Christian

mppfor_manu · July 13, 2017, 10:45pm

I agree, but I’m speculating that the desire to maintain as generic a schema structure as possible would weigh against this. I’m sure you’ve noticed there are no triggers, stored procedures, etc. All of these native functions within their respective databases generally provide far greater functionality and performance, but then they must all be developed, tested, and maintained.

Moreover, they would need to be appropriate for the entire range of use cases. For example, we’ve had to abandon Camunda’s history service as it is simply too slow and produces too much data. We could impose additional schema changes in an attempt to fix this, but we’re unwilling to modify core functionality without Camunda’s blessing.