Support Article
Report Definition rule is not opening in Desinger studio
SA-32393
Summary
All Report definition rules are taking too long to open and some rules are getting opened.
Error Messages
Not Applicable
Steps to Reproduce
- Open any report definition rule in CSHC 7.21 on Pega 7.2.1
Root Cause
Below SQL query takes a long time to return result is causing the issue.
WITH rr_inheritance_pc0 AS ( SELECT "r".pzInsKey, "r".pyRuleAvailable, "r".pyClassName, "r".pyRuleSet,
RANK() OVER (PARTITION BY "r".pxInsId, "r".pyClass, "r".pyClassName ORDER BY "i"."pzRuleSetIndex",
"r".pyRuleSetVersion DESC) AS "rank_idx", DENSE_RANK() OVER (ORDER BY "r".pxInsId, "r".pyClass,
"r".pyClassName) AS "group_idx" FROM XXXX.pr4_rule_vw "r" INNER JOIN ( SELECT "i".pzRuleSetName
AS "pzRuleSetName", "i".pzRuleSetIndex AS "pzRuleSetIndex", "i".pzRuleSetVersionMajor AS "pzRuleSetVersionMajor",
"i".pzRuleSetVersionMinor AS "pzRuleSetVersionMinor", "i".pzRuleSetVersionPatch AS "pzRuleSetVersionPatch" ,
"i".pzRuleSetVersionMinorPatch AS "pzRuleSetVersionMinorPatch" FROM XXXX.pr_sys_ruleset_index
"i" WHERE "i".pzRuleSetListHash = ? UNION ALL SELECT CAST(? AS VARCHAR(128))
AS "pzRuleSetName", -1 AS "pzRuleSetIndex", 1 AS "pzRuleSetVersionMajor",
1 AS "pzRuleSetVersionMinor", 1 AS "pzRuleSetVersionPatch" , '01-01' AS
"pzRuleSetVersionMinorPatch" FROM DUAL ) "i" ON ("r".pyRuleSet = "i"."pzRuleSetName")
AND ("r".pzRuleSetVersionMajor = "i"."pzRuleSetVersionMajor" AND "r".pzRuleSetVersionMinorPatch <= "i"."pzRuleSetVersionMinorPatch")
WHERE "r".pyClass = ? AND "r".pyRuleAvailable != 'No' AND "r".pyCircumstanceType
IS NULL AND "r".pyRuleStarts IS NULL AND "r".pyRuleEnds IS NULL AND "r".pzClassType = 0 ) ,
bc_inheritance_pc0 AS ( SELECT "bc".pzInsKey FROM ( SELECT "tmp".pzInsKey, "tmp".pyRuleAvailable
FROM ( SELECT "second".pzInsKey, "second".pyRuleAvailable, RANK() OVER (PARTITION BY "second"."group_idx"
ORDER BY "second"."rank_idx") AS "finalRank" FROM rr_inheritance_pc0 "first" RIGHT OUTER JOIN rr_inheritance_pc0
"second" ON ( "first"."group_idx" = "second"."group_idx" AND "first".pyClassName = "second".pyClassName AND
"first".pyRuleSet = "second".pyRuleSet AND "first".pyRuleAvailable = 'Withdrawn' AND coalesce("first"."rank_idx", 0)
<= "second"."rank_idx" ) WHERE "first"."rank_idx" IS NULL ) "tmp" WHERE "tmp"."finalRank" = 1 )
"bc" WHERE "bc".pyRuleAvailable != 'Blocked' ) , rr_wo_inheritance_pc0 AS ( SELECT "r".pzInsKey,
"r".pyRuleAvailable, "r".pyClassName, "r".pyRuleSet, RANK() OVER (PARTITION BY "r".pxInsId, "r".pyClass
ORDER BY "i"."pzRuleSetIndex", "r".pyRuleSetVersion DESC) AS "rank_idx", DENSE_RANK() OVER (ORDER BY "r".pxInsId, "r".pyClass)
AS "group_idx" FROM XXXXX.pr4_rule_vw "r" INNER JOIN
( SELECT "i".pzRuleSetName AS "pzRuleSetName", "i".pzRuleSetIndex AS "pzRuleSetIndex", "i".pzRuleSetVersionMajor AS
"pzRuleSetVersionMajor", "i".pzRuleSetVersionMinor AS "pzRuleSetVersionMinor",
"i".pzRuleSetVersionPatch AS "pzRuleSetVersionPatch" , "i".pzRuleSetVersionMinorPatch
AS "pzRuleSetVersionMinorPatch" FROM XXXXX.pr_sys_ruleset_index "i" WHERE "i".pzRuleSetListHash = ?
UNION ALL SELECT CAST(? AS VARCHAR(128)) AS "pzRuleSetName", -1 AS "pzRuleSetIndex", 1 AS "pzRuleSetVersionMajor", 1
AS "pzRuleSetVersionMinor", 1 AS "pzRuleSetVersionPatch" , '01-01' AS "pzRuleSetVersionMinorPatch" FROM DUAL ) "i"
ON ("r".pyRuleSet = "i"."pzRuleSetName") AND ("r".pzRuleSetVersionMajor = "i"."pzRuleSetVersionMajor" AND
"r".pzRuleSetVersionMinorPatch <= "i"."pzRuleSetVersionMinorPatch") WHERE "r".pyClass = ? AND "r".pyRuleAvailable != 'No'
AND "r".pyCircumstanceType IS NULL AND "r".pyRuleStarts IS NULL AND "r".pyRuleEnds IS NULL AND "r".pzClassType = 1 ) ,
bc_wo_inheritance_pc0 AS ( SELECT "bc".pzInsKey FROM ( SELECT "tmp".pzInsKey, "tmp".pyRuleAvailable FROM ( SELECT "second".pzInsKey,
"second".pyRuleAvailable, RANK() OVER (PARTITION BY "second"."group_idx" ORDER BY "second"."rank_idx") AS "finalRank"
FROM rr_wo_inheritance_pc0 "first" RIGHT OUTER JOIN rr_wo_inheritance_pc0 "second" ON ( "first"."group_idx" = "second"."group_idx"
AND "first".pyRuleSet = "second".pyRuleSet AND "first".pyRuleAvailable = 'Withdrawn' AND coalesce("first"."rank_idx", 0) <= "second"."rank_idx" )
WHERE "first"."rank_idx" IS NULL ) "tmp" WHERE "tmp"."finalRank" = 1 ) "bc" WHERE "bc".pyRuleAvailable != 'Blocked' ) ,
non_rr_rules_pc0 AS ( SELECT "r".pzInsKey FROM XXXYYY.pr4_rule_vw "r"
HERE COALESCE("r".pyRuleAvailable, 'Yes') != 'No' AND "r".pyCircumstanceType IS NULL AND "r".pyRuleStarts
IS NULL AND "r".pyRuleEnds IS NULL AND "r".pzClassType = 2 ) ,
resolved_rules_pc0 AS ( SELECT pzInsKey FROM bc_inheritance_pc0
UNION ALL SELECT pzInsKey FROM bc_wo_inheritance_pc0
UNION ALL SELECT pzInsKey FROM non_rr_rules_pc0 )
SELECT "PC0"."PYCLASSNAME" AS "pyClassName" , "PC0"."PYLABEL" AS "pyLabel" , "PC0"."PYVALIDWHEN"
AS "pyValidWhen" , "PC0"."PXINSTANCELOCKEDBY" AS "pxInstanceLockedBy" , "PC0"."PXINSNAME"
AS "pxInsName" , "PC0"."PZINSKEY" AS "pzInsKey" FROM XXXYY.pr4_rule_delegationconfig "PC0"
INNER JOIN resolved_rules_pc0 "resolved_rules_pc0" ON ("PC0".pzInsKey = "resolved_rules_pc0".pzInsKey) WHERE "PC0"."PXOBJCLASS" = ?
Resolution
Create below index to resolve the issue.
USE [PEGA72]
GO
CREATE NONCLUSTERED INDEX [pr4_rule_vw_idx7]
ON [PEGA72].[pr4_rule_vw] ([pyClass],[pyCircumstanceType],[pyRuleStarts],[pyRuleEnds],[pzClassType],[pyRuleAvailable])
INCLUDE ([pxInsId],[pyRuleSet],[pyRuleSetVersion],[pzRuleSetVersionMajor],[pzRuleSetVersionMinorPatch])
GO
Note: replace PEGA72 by appropriate schema name and check the name of index so that it should be unique.
Published February 21, 2017 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.