Support Article
Query timed out "...WITH rr_inheritance..." (SQL Server 2016)
SA-64536
Summary
Query timeout occurs on right clicking a class in the Application explorer and selecting the Create Properties option. This occurs on invoking the Data Designer for the first time for a given class.
As a result, Data Designer UI which displays seems like the CSS of the page is not applied.
Error Messages
[default task-121] [TABTHREAD1] [ ] [leBranchApp:01.01.01] (dbms.JdbcConnectionManagerImpl) ERROR deve.crm.xxxxx|127.0.0.1 xxxxxx- Not returning connection 11 for database "pegarules" to the pool as it previously encountered the following error
User ID: xxxxxxxx
Last SQL: WITH rr_inheritance_pc0 AS (SELECT "r".pzInsKey, "r".pyRuleAvailable, "r".pyClassName, "r".pyRuleSet, RANK() OVER (PARTITION BY "r".pxInsId, "r".pyClass ORDER BY "c".pxHeight, "i"."pzRuleSetIndex", "r".pyRuleSetVersion DESC) AS "rank_idx", DENSE_RANK() OVER (ORDER BY "r".pxInsId, "r".pyClass) AS "group_idx" FROM pegarules.pr4_rule_vw "r" INNER JOIN pegarules.pr_sys_class_ancestors "c" ON ("r".pyClassName = "c".pxClassName) 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 pegarules.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") "i" ON ("r".pyRuleSet = "i"."pzRuleSetName") AND ("r".pzRuleSetVersionMajor = "i"."pzRuleSetVersionMajor" AND "r".pzRuleSetVersionMinorPatch <= "i"."pzRuleSetVersionMinorPatch") WHERE "r".pyClass = ? AND "c".pxLeafClassName = ? 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 pegarules.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 pegarules.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") "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 pegarules.pr4_rule_vw "r" WHERE 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"."pyStreamName" AS "pyStreamName" , "PC0"."pxInsId" AS "pxInsId" , "PC0"."pyLabel" AS "pyLabel" , "PC0"."pyRuleSet" AS "pyRuleSet" , "PC0"."pzInsKey" AS "pzInsKey" FROM pegarules.pr4_rule_section "PC0" INNER JOIN resolved_rules_pc0 "resolved_rules_pc0" ON ("PC0".pzInsKey = "resolved_rules_pc0".pzInsKey) WHERE ("PC0"."pyClassName" = ?) AND "PC0"."pxObjClass" = ? ORDER BY 3 ASC
com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
Steps to Reproduce
- Install Pega 7.4.
- Right click a class in the Data Designer.
Root Cause
A defect in Pegasystems’ code or rules.
Rule Resolution queries may take longer than the defined alert threshold during intensive actions, such as switching applications.
In SQL Server 2014-2016, queries produced by Rule Resolution filtered report definitions timeout constantly. This behavior is related to the Cardinality Estimator feature introduced in SQL Server 2014. This feature causes complex queries to run slow.
Resolution
- Apply HFix-44396.
- Create the following Dynamic System Settings (DSS).
a. Purpose: reporting/useForceOrderHint
Ruleset: Pega-RULES
Value: true for ac
b. Purpose: reporting/useForceOrderHintForRRandAC
Ruleset: Pega-RULES
Value: true
c. Purpose: reporting/useMergeHintForRRquery
Ruleset: Pega-Reporting
Value: true
- Restart the server.
Published October 9, 2018 - 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.