Hello,
I`m received a recommendation from DBA to correct the request
How can I fix queries?
Camunda 7.7.0
DB - PostgreSQL
new query 2.txt (617 Bytes)
new query 1.txt (619 Bytes)
original query 1.txt (3.9 KB)
original query 2.txt (3.7 KB)
original query 1:
SELECT RES.*
FROM ACT_HI_TASKINST RES
LEFT JOIN (
SELECT SELF.PROC_DEF_KEY_ AS KEY_
FROM
(
SELECT DISTINCT DKEYS.PROC_DEF_KEY_
FROM ACT_HI_TASKINST DKEYS
)SELF
new query 1:
WITH RECURSIVE t AS (
SELECT MIN(PROC_DEF_KEY_) AS PROC_DEF_KEY_ FROM ACT_HI_TASKINST DKEYS
UNION ALL
SELECT (SELECT MIN(PROC_DEF_KEY_) FROM ACT_HI_TASKINST DKEYS WHERE PROC_DEF_KEY_ > t.PROC_DEF_KEY_)
FROM t WHERE t.PROC_DEF_KEY_ IS NOT NULL
)
SELECT RES.*
FROM ACT_HI_TASKINST RES
LEFT JOIN (
SELECT SELF.PROC_DEF_KEY_ AS KEY_
FROM
(
SELECT DISTINCT DKEYS.PROC_DEF_KEY_ FROM (SELECT PROC_DEF_KEY_ FROM t WHERE PROC_DEF_KEY_ IS NOT NULL
UNION ALL
SELECT NULL WHERE EXISTS(SELECT 1 FROM ACT_HI_TASKINST DKEYS WHERE PROC_DEF_KEY_ IS NULL)) DKEYS
)SELF
original query 2:
SELECT RES.*
FROM ACT_HI_VARINST RES
LEFT JOIN (
SELECT SELF.PROC_DEF_KEY_ AS KEY_
FROM
(
SELECT DISTINCT DKEYS.PROC_DEF_KEY_
FROM ACT_HI_VARINST DKEYS
)SELF
new query 2:
WITH RECURSIVE t AS (
SELECT MIN(PROC_DEF_KEY_) AS PROC_DEF_KEY_ FROM ACT_HI_VARINST DKEYS
UNION ALL
SELECT (SELECT MIN(PROC_DEF_KEY_) FROM ACT_HI_VARINST DKEYS WHERE PROC_DEF_KEY_ > t.PROC_DEF_KEY_)
FROM t WHERE t.PROC_DEF_KEY_ IS NOT NULL
)
SELECT RES.*
FROM ACT_HI_VARINST RES
LEFT JOIN (
SELECT SELF.PROC_DEF_KEY_ AS KEY_
FROM
(
SELECT DISTINCT DKEYS.PROC_DEF_KEY_ FROM (SELECT PROC_DEF_KEY_ FROM t WHERE PROC_DEF_KEY_ IS NOT NULL
UNION ALL
SELECT NULL WHERE EXISTS(SELECT 1 FROM ACT_HI_VARINST DKEYS WHERE PROC_DEF_KEY_ IS NULL)) DKEYS
)SELF