Support Article
Performance degrades while creating cases or running Obj-Browse
SA-81347
Summary
Performance degrades while creating cases or running Obj-Browse methods.
Error Messages
WITH app_rulesets_pc0 AS ( SELECT "RULES".pzRuleSetName AS "pzRuleSetName", "RULES".pzRuleSetVersionMajor AS "pzRuleSetVersionMajor", "RULES".pzRuleSetVersionMinor AS "pzRuleSetVersionMinor", "RULES".pzRuleSetVersionPatch AS "pzRuleSetVersionPatch" , "RULES"."pzRuleSetOverallOrder" AS "pzRuleSetOverallOrder", "RULES".pzRuleSetVersionMinorPatch AS "pzRuleSetVersionMinorPatch" FROM ( SELECT "RULES".pzRuleSetName, "RULES".pzRuleSetVersionMajor, "RULES".pzRuleSetVersionMinor, "RULES".pzRuleSetVersionPatch, ROW_NUMBER() OVER (PARTITION BY "RULES".pzRuleSetName ORDER BY "PC0AH".pzAppHeight) AS "pzRuleSetOrder" , ROW_NUMBER() OVER (ORDER BY "PC0AH".pzAppHeight, "RULES".pzRuleSetIndex) AS "pzRuleSetOverallOrder" , "RULES".pzRuleSetVersionMinorPatch FROM PegaRULES.pr_sys_app_hierarchy_flat "PC0AH" INNER JOIN PegaRULES.pr_sys_app_ruleset_index "RULES" ON ("PC0AH".pzAppHash = "RULES".pzAppHash) WHERE "PC0AH".pzTopAppHash = ? AND "PC0AH".pzAppName != 'PegaRULES') "RULES" WHERE "RULES"."pzRuleSetOrder" = 1 ) ,rr_inheritance_pc0 AS ( SELECT "r".pzInsKey, "r".pyRuleAvailable, "r".pyClassName, "r".pyRuleSet, "r".pyRuleSetVersion, "r".pxInsId, "r".pyClass, RANK() OVER (PARTITION BY "r".pxInsId, "r".pyClass, "r".pyClassName ORDER BY "i"."pzRuleSetOverallOrder", "r".pyRuleSetVersion DESC) AS "rank_idx", DENSE_RANK() OVER (ORDER BY "r".pxInsId, "r".pyClass, "r".pyClassName) AS "group_idx" FROM PegaRULES.pr4_rule_vw "r" INNER JOIN app_rulesets_pc0 "i" ON ("r".pyRuleSet = "i"."pzRuleSetName") AND ("r".pzRuleSetVersionMajor = "i"."pzRuleSetVersionMajor" AND "r".pzRuleSetVersionMinorPatch = "i"."pzRuleSetVersionMinorPatch") WHERE "r".pxObjClass = ? 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 "r".pzInsKey FROM ( SELECT "r".pzInsKey, "r".pyRuleAvailable FROM ( SELECT "s".pzInsKey, "s".pyRuleAvailable, RANK() OVER (PARTITION BY "s"."group_idx" ORDER BY "s"."rank_idx") AS "finalRank" FROM PegaRULES.pr4_rule_vw "r" INNER JOIN app_rulesets_pc0 "i" ON ( "r".pyRuleSet = "i"."pzRuleSetName" AND ("r".pzRuleSetVersionMajor = "i"."pzRuleSetVersionMajor" AND "r".pzRuleSetVersionMinorPatch = "i"."pzRuleSetVersionMinorPatch") AND "r".pzClassType = 0 AND "r".pyCircumstanceType IS NULL AND "r".pyRuleStarts IS NULL AND "r".pyRuleEnds IS NULL AND "r".pyRuleAvailable = 'Withdrawn' ) RIGHT OUTER JOIN rr_inheritance_pc0 "s" ON ( "r".pxInsId = "s".pxInsId AND "r".pyClass = "s".pyClass AND "r".pyClassName = "s".pyClassName AND "r".pyRuleSet = "s".pyRuleSet AND
Steps to Reproduce
Create a new case type.
Root Cause
A defect or configuration issue in the operating environment. The SQL Server database does not honor the order of the joins and does not use indexes for queries.
Resolution
Make the following change to the operating environment:Purpose: reporting/useForceOrderHint
Ruleset: Pega-RULES
Value: true
Purpose: reporting/useForceOrderHintForRRandAC
Rulset : Pega-RULES
Value :true
Purpose: reporting/useMergeHintForRRquery
Ruleset: Pega-Reporting
Value: true
Purpose: racache/useForceOrderHintForInvalidationQuery
Rulset : Pega-RULES
Value :true
Published August 15, 2019 - Updated December 2, 2021
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.