We notice a significant difference in performance when using the REST API when issuing GET /task?assignee=x vs. GET /history/task?assignee=x.
On our development environment, the GET /task takes 15-17 seconds to return.
The GET /history/task takes 2-3 seconds to return.
We are using a mysql database hosted on AWS.
The ACT_RU_TASK table contains 5015 rows.
The ACT_HI_TASKINST table contains 15,495 rows.
The GET /task?assignee=x returns 456 tasks.
The GET /task?assignee=x returns 1409 tasks.
We have the following questions:
Is this difference in performance expected?
Do you know of any way that the performance of the GET /task can be improved?
Would it be safe to always use the GET /history/task?unfinished=true to get a list of current tasks? We have sometimes noticed a delay before data gets propagated to history, so we have concerns if querying history is a reliable and timely way to get the list of current tasks.
I agree with Rob. Please let us know if the task query can be simplified.
Should be good as long as you use a history level that writes task history. Since history and runtime tasks are comitted in the same transaction, they should appear in the database at the same time, from another application’s perspective. Keep in mind that if history grows significantly larger than the runtime table, then the performance may flip.
We captured the SQL queries logged by the database during this request and have included a portion of this below. We notice that the initial query does not query for a specified task assignee (even though the REST request is for a specific task assignee). Instead it seems to get all tasks and then there is a very long sequence of additional queries. I can’t tell if these queries are being done for each task returned in the initial request or only for the specified task assignee. If the subsequent queries are done for each task, that seems like a possible problem. It also seems like it might be more efficient for the initial query to filter on the specified assignee.
2017-07-12T14:52:39.632463Z 28 Query 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
order by RES.ID_ asc
LIMIT 2147483647 OFFSET 0
2017-07-12T14:52:39.696732Z 28 Query select * from ACT_RE_PROCDEF where ID_ = 'ff73f054-d9a8-11e6-8268-0242ac120003'
2017-07-12T14:52:39.697465Z 28 Query select * from ACT_RE_DEPLOYMENT where ID_ = 'ff6dfce2-d9a8-11e6-8268-0242ac120003'
2017-07-12T14:52:39.697981Z 28 Query select
* from
ACT_GE_BYTEARRAY
where
DEPLOYMENT_ID_ = 'ff6dfce2-d9a8-11e6-8268-0242ac120003'
and
NAME_ = 'api-upload.bpmn'
2017-07-12T14:52:39.706860Z 28 Query select *
from ACT_RE_PROCDEF
where DEPLOYMENT_ID_ = 'ff6dfce2-d9a8-11e6-8268-0242ac120003'
and KEY_ = '_506f163e-a6c2-4f21-b4b3-fe3a11e4f102'
2017-07-12T14:52:39.707765Z 28 Query select *
from ACT_RE_PROCDEF RES
where KEY_ = '_506f163e-a6c2-4f21-b4b3-fe3a11e4f102'
and TENANT_ID_ = 'GET-WFIs-TEST'
and VERSION_ = (
select max(VERSION_)
from ACT_RE_PROCDEF
where KEY_ = '_506f163e-a6c2-4f21-b4b3-fe3a11e4f102' and TENANT_ID_ = 'GET-WFIs-TEST')
2017-07-12T14:52:39.708512Z 28 Query select * from ACT_RE_PROCDEF where ID_ = 'a2463930-0a72-11e7-8541-0242ac120003'
2017-07-12T14:52:39.709178Z 28 Query select * from ACT_RE_PROCDEF where ID_ = 'ffa5f198-2b76-11e7-9a99-0242ac120003'
2017-07-12T14:52:39.709754Z 28 Query select * from ACT_RE_DEPLOYMENT where ID_ = 'ff9fb006-2b76-11e7-9a99-0242ac120003'
2017-07-12T14:52:39.710275Z 28 Query select
* from
ACT_GE_BYTEARRAY
where
DEPLOYMENT_ID_ = 'ff9fb006-2b76-11e7-9a99-0242ac120003'
and
NAME_ = 'api-upload.bpmn'
2017-07-12T14:52:39.715527Z 28 Query select *
from ACT_RE_PROCDEF
where DEPLOYMENT_ID_ = 'ff9fb006-2b76-11e7-9a99-0242ac120003'
and KEY_ = '_5d8afd9c-2776-4f78-a99c-7e754507418d'
2017-07-12T14:52:39.717939Z 28 Query select *
from ACT_RE_PROCDEF RES
where KEY_ = '_5d8afd9c-2776-4f78-a99c-7e754507418d'
and TENANT_ID_ = '085952a2-3e4c-4c74-bda3-fbe8a1914f15'
and VERSION_ = (
select max(VERSION_)
from ACT_RE_PROCDEF
where KEY_ = '_5d8afd9c-2776-4f78-a99c-7e754507418d' and TENANT_ID_ = '085952a2-3e4c-4c74-bda3-fbe8a1914f15')
2017-07-12T14:52:39.718776Z 28 Query select * from ACT_RE_PROCDEF where ID_ = 'ffb882ad-365d-11e7-b1e9-0242ac120003'
2017-07-12T14:52:39.719668Z 28 Query select * from ACT_RE_DEPLOYMENT where ID_ = 'ffb3046b-365d-11e7-b1e9-0242ac120003'
2017-07-12T14:52:39.720211Z 28 Query select
*
from
ACT_GE_BYTEARRAY
where
DEPLOYMENT_ID_ = 'ffb3046b-365d-11e7-b1e9-0242ac120003'
and
NAME_ = 'api-upload.bpmn'
2017-07-12T14:52:39.725174Z 28 Query select *
from ACT_RE_PROCDEF
where DEPLOYMENT_ID_ = 'ffb3046b-365d-11e7-b1e9-0242ac120003'
and KEY_ = '_87c0b40b-a912-4eeb-a51c-a9c15808bf58'
2017-07-12T14:52:39.726261Z 28 Query select *
from ACT_RE_PROCDEF RES
where KEY_ = '_87c0b40b-a912-4eeb-a51c-a9c15808bf58'
and TENANT_ID_ = 'c64bd7da-ce49-4e44-abd1-2da312e9d6f7'
and VERSION_ = (
select max(VERSION_)
from ACT_RE_PROCDEF
where KEY_ = '_87c0b40b-a912-4eeb-a51c-a9c15808bf58' and TENANT_ID_ = 'c64bd7da-ce49-4e44-abd1-2da312e9d6f7')
2017-07-12T14:52:39.727016Z 28 Query select * from ACT_RE_PROCDEF where ID_ = 'f25c44b8-48d0-11e7-9d85-0242ac120003'
2017-07-12T14:52:39.727701Z 28 Query select * from ACT_RE_DEPLOYMENT where ID_ = 'f24f2556-48d0-11e7-9d85-0242ac120003'
2017-07-12T14:52:39.728315Z 28 Query select
*
from
ACT_GE_BYTEARRAY
where
DEPLOYMENT_ID_ = 'f24f2556-48d0-11e7-9d85-0242ac120003'
and
NAME_ = 'api-upload.bpmn'
2017-07-12T14:52:39.736361Z 28 Query select *
from ACT_RE_PROCDEF
where DEPLOYMENT_ID_ = 'f24f2556-48d0-11e7-9d85-0242ac120003'
and KEY_ = '_482f575b-e43e-454b-9eec-103976d4f510'
2017-07-12T14:52:39.737361Z 28 Query select * from ACT_RU_JOBDEF where PROC_DEF_ID_ = 'f25c44b8-48d0-11e7-9d85-0242ac120003'
2017-07-12T14:52:39.738117Z 28 Query select *
from ACT_RE_PROCDEF RES
where KEY_ = '_482f575b-e43e-454b-9eec-103976d4f510'
and TENANT_ID_ = '7d48ec6a-2144-4535-b54c-2c23e703f3e1'
and VERSION_ = (
select max(VERSION_)
from ACT_RE_PROCDEF
where KEY_ = '_482f575b-e43e-454b-9eec-103976d4f510' and TENANT_ID_ = '7d48ec6a-2144-4535-b54c-2c23e703f3e1')
2017-07-12T14:52:39.800768Z 28 Query select * from ACT_RE_PROCDEF where ID_ = 'f69b1333-034e-11e7-b858-0242ac120003'
2017-07-12T14:52:39.801611Z 28 Query select * from ACT_RE_DEPLOYMENT where ID_ = 'f6945c71-034e-11e7-b858-0242ac120003'
2017-07-12T14:52:39.802119Z 28 Query select
*
from
ACT_GE_BYTEARRAY
where
DEPLOYMENT_ID_ = 'f6945c71-034e-11e7-b858-0242ac120003'
and
NAME_ = 'api-upload.bpmn'
2017-07-12T14:52:39.810690Z 28 Query select *
from ACT_RE_PROCDEF
where DEPLOYMENT_ID_ = 'f6945c71-034e-11e7-b858-0242ac120003'
and KEY_ = '_482f575b-e43e-454b-9eec-103976d4f510'
2017-07-12T14:52:39.811599Z 28 Query select * from ACT_RU_JOBDEF where PROC_DEF_ID_ = 'f69b1333-034e-11e7-b858-0242ac120003'
2017-07-12T14:52:39.812295Z 28 Query select * from ACT_RE_PROCDEF where ID_ = 'c674f67a-2917-11e7-9b21-0242ac120003'
2017-07-12T14:52:39.812938Z 28 Query select * from ACT_RE_DEPLOYMENT where ID_ = 'c66edbf8-2917-11e7-9b21-0242ac120003'
2017-07-12T14:52:39.813447Z 28 Query select
*
from
ACT_GE_BYTEARRAY
where
DEPLOYMENT_ID_ = 'c66edbf8-2917-11e7-9b21-0242ac120003'
and
NAME_ = 'api-upload.bpmn'
2017-07-12T14:52:39.819318Z 28 Query select *
from ACT_RE_PROCDEF
where DEPLOYMENT_ID_ = 'c66edbf8-2917-11e7-9b21-0242ac120003'
and KEY_ = '_44f93d11-b28e-4192-b48c-533972a522f8'
2017-07-12T14:52:39.820217Z 28 Query select *
from ACT_RE_PROCDEF RES
where KEY_ = '_44f93d11-b28e-4192-b48c-533972a522f8'
and TENANT_ID_ = '976d10e2-d112-4a92-b484-0cf761a6f743'
and VERSION_ = (
select max(VERSION_)
from ACT_RE_PROCDEF
where KEY_ = '_44f93d11-b28e-4192-b48c-533972a522f8' and TENANT_ID_ = '976d10e2-d112-4a92-b484-0cf761a6f743')
... The query log has been truncated here. There are many more similar queries.