Hi PetrF,
Thank you for reaching out to us with this request.
Please keep in mind that we are providing support for different databases, versions and setups (small nodes and large clusters) - Supported environments.
Whenever we evaluate improvements in the database environments, we try to make an informative decision considering all scenarios and based on concrete data.
As next step I would like to ask you to provide further details so we can evaluate the suggested improvements:
- Could you please share more information for your setup:
- What is the architecture - do you have a cluster environment, how many nodes, etc.
- What is your jdbc driver? You mentioned using SQL Server 2018, is that correct?
- Could you please share cardinalities for the specified query [1] - number of entries in ACT_RU_EXT_TASK, ACT_RU_EXECUTION, ACT_RE_PROCDEF tables
- Please upload explain plan without and with the improvement of the query
- Current query
SELECT SUB.* FROM (
select RES.* , row_number() over (ORDER BY RES.PRIORITY_ desc) rnk FROM (
select distinct RES.* from (
select RES.*, PI.BUSINESS_KEY_, PD.VERSION_TAG_
from ACT_RU_EXT_TASK RES
left join ACT_RU_EXECUTION PI on RES.PROC_INST_ID_ = PI.ID_
inner join ACT_RE_PROCDEF PD on RES.PROC_DEF_ID_ = PD.ID_
WHERE (RES.LOCK_EXP_TIME_ is null or RES.LOCK_EXP_TIME_ <= @LockExpTime)
and (RES.SUSPENSION_STATE_ is null or RES.SUSPENSION_STATE_ = 1)
and (RES.RETRIES_ is null or RES.RETRIES_ > 2)
and ( RES.TOPIC_NAME_ like '%' )
) RES
)RES
) SUB
WHERE SUB.rnk >= @start AND SUB.rnk < @end ORDER BY SUB.rnk
- Suggested improvement
select distinct RES.* from (
select RES.*, PI.BUSINESS_KEY_, PD.VERSION_TAG_
from ACT_RU_EXT_TASK RES
left join ACT_RU_EXECUTION PI on RES.PROC_INST_ID_ = PI.ID_
inner join ACT_RE_PROCDEF PD on RES.PROC_DEF_ID_ = PD.ID_
WHERE (RES.LOCK_EXP_TIME_ is null or RES.LOCK_EXP_TIME_ <= @LockExpTime)
and (RES.SUSPENSION_STATE_ is null or RES.SUSPENSION_STATE_ = 1)
and (RES.RETRIES_ is null or RES.RETRIES_ > 2)
and (RES.TOPIC_NAME_ like '%')
) RES
ORDER BY RES.PRIORITY_ desc
OFFSET @start ROWS
FETCH NEXT @end - @start ROWS ONLY
[1]: https://forum.camunda.org/t/sql-server-performance-improvements/26587/3
Best regards,
Yana
Feature Request
L3 - Default
Hi PetrF,
Thank you for reaching out to us with this request.
Please keep in mind that we are providing support for different databases, versions and setups (small nodes and large clusters) - Supported environments.
Whenever we evaluate improvements in the database environments, we try to make an informative decision considering all scenarios and based on concrete data.
As next step I would like to ask you to provide further details so we can evaluate the suggested improvements:
[1]: https://forum.camunda.org/t/sql-server-performance-improvements/26587/3
Best regards,
Yana