[CAM-11339] Weak performance of count queries on SQL Server

Type: Bug Report
Resolution: Unresolved
Priority: L3 - Default
Fix Version/s: None
Affects Version/s: 7.11.0
Component/s: engine, webapp
Labels:
None

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

Vuk Vasic created issue - 28/Jan/20 2:26 PM

Vuk Vasic made changes - 28/Jan/20 2:29 PM

Description

New: 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. This is one example that we got:
{code:java}
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
{code}

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

Vuk Vasic made changes - 28/Jan/20 2:29 PM

Component/s		New: engine [ 11656 ]
Component/s		New: webapp [ 13552 ]

Vuk Vasic made changes - 28/Jan/20 2:29 PM

Labels

New: SUPPORT

Vuk Vasic made changes - 28/Jan/20 2:29 PM

Affects Version/s

New: 7.11.0 [ 15343 ]

Vuk Vasic made changes - 28/Jan/20 2:30 PM

Description

Original: 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. This is one example that we got:
{code:java}
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
{code}

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

New: 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. This is one example that we got:
{code:java}
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
{code}

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

Vuk Vasic made changes - 29/Jan/20 9:45 AM

Description

Original: 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. This is one example that we got:
{code:java}
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
{code}

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

New: 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. This is one example that we got:
{code:java}
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
{code}

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:

{code:sql}
CREATE NONCLUSTERED INDEX [ACT_RE_PROCDEF_KEY_TENANT_ID]
ON [dbo].[ACT_RE_PROCDEF] ([KEY_],[TENANT_ID_])
INCLUDE ([VERSION_])
GO
{code}

Vuk Vasic made changes - 29/Jan/20 9:45 AM

Mentioned Roles

Vuk Vasic made changes - 29/Jan/20 9:45 AM

Mentioned Groups

Vuk Vasic made changes - 29/Jan/20 1:34 PM

Description

Original: 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. This is one example that we got:
{code:java}
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
{code}

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:

{code:sql}
CREATE NONCLUSTERED INDEX [ACT_RE_PROCDEF_KEY_TENANT_ID]
ON [dbo].[ACT_RE_PROCDEF] ([KEY_],[TENANT_ID_])
INCLUDE ([VERSION_])
GO
{code}

New: 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:
{code:java}
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
{code}

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:

{code:sql}
CREATE NONCLUSTERED INDEX [ACT_RE_PROCDEF_KEY_TENANT_ID]
ON [dbo].[ACT_RE_PROCDEF] ([KEY_],[TENANT_ID_])
INCLUDE ([VERSION_])
GO
{code}

Vuk Vasic made changes - 29/Jan/20 1:34 PM

Mentioned Roles

Vuk Vasic made changes - 29/Jan/20 1:34 PM

Mentioned Groups

Assignee:: Unassigned

Reporter:: Vuk Vasic

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Created:: 28/Jan/20 2:26 PM

Updated:: 12/Oct/22 11:02 AM

camunda BPM

Details

Description

mgm-controller-panel

This is the controller panel for Smart Panels app

Attachments

Activity

People

Dates

Salesforce