L3 - Default In Camunda Cockpit we get Timeout on count api endpoints when there is nearly 3000 active process instances.
Debugging what is taking so long we got that this COUNT(DISTINCT query is running to slow (Few minutes). This is one example that we got:
select count(distinct RES.ID_)
from ACT_RE_PROCDEF RES
inner join
(select KEY_, TENANT_ID_, max(VERSION_) as MAX_VERSION
from ACT_RE_PROCDEF
group by TENANT_ID_, KEY_) VER
on RES.KEY_ = VER.KEY_
left JOIN (
SELECT A.*
FROM ACT_RU_AUTHORIZATION A
WHERE A.TYPE_ < 2
AND ( A.USER_ID_ in ( 'sample.user', '*')
OR A.GROUP_ID_ IN ( 'Head manager' , 'Admin' )
)
AND (
(
A.RESOURCE_TYPE_ = 6 AND A.PERMS_ &2 = 2
)
)
)
AUTH ON (AUTH.RESOURCE_ID_ in (RES.ID_, RES.KEY_, '*'))
WHERE RES.VERSION_ = VER.MAX_VERSION and
(RES.TENANT_ID_ = VER.TENANT_ID_ or (RES.TENANT_ID_ is null and VER.TENANT_ID_ is null))
AND
AUTH.RESOURCE_ID_ IS NOT NULL
I see that there is already simular bug report which was resolved for PostgreSQL:
https://jira.camunda.com/browse/CAM-10282
but i am not sure if this is also fixed for SQL Server in version 7.12
It would be nice to have some Index workaround until the query is optimized as in example:
https://stackoverflow.com/questions/13924688/performance-using-distinct-count
UPDATE
Adding following index makes count faster but still it takes few seconds to run:
CREATE NONCLUSTERED INDEX [ACT_RE_PROCDEF_KEY_TENANT_ID] ON [dbo].[ACT_RE_PROCDEF] ([KEY_],[TENANT_ID_]) INCLUDE ([VERSION_]) GO
This is the controller panel for Smart Panels app
[CAM-11339] Weak performance of count queries on SQL Server
Hi @miklas.boskamp,
Root cause of this was already written in the issue, the same issue was already resolved for postgres so ideally the same guy should work on this issue as he knows the context.
I would say the issue is critical because Camunda Cockpit is not usable at all because of this on SQL Server for both Community and Enterprise versions.
I would like to contribute creating Pull Request but unforchantly currently opening an issue is best I can do.
Hi vukvasic,
the query you posted is not related to the number of running instances but to process definitions. Also, I can see from the query that you are running with authorization which introduces complexity to the query and might decrease performance. Without investigating deeper here we can not tell what is causing this.
If you can disable authorization, this might help.
As I previously said, currently we can not invest the effort of root causing this.
Best,
Miklas
Hi,
Yes this is related to process definitions, but this is only one query. Every SELECT COUNT(DISTINCT query is running slow on SQL Server not just this one so root cause is the way query is built for COUNT. I already added workaround that is currently fine for us (adding index in post) but still this is just an workaround not real solution of problem
This ticket was migrated to github: https://github.com/camunda/camunda-bpm-platform/issues/2377. Please use this link for any future references and continue any discussion there.
Hi vukvasic,
thanks for bringing this to our attention.
However, root causing this would require high effort on our side, which we currently can not invest. If you are very interested in optimizing this query, I encourage you to raise a pull request and also provide some query analysis that proves the performance advantages of the new solution.
Cheers,
Miklas