Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

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

  1. Install Pega 7.4.
  2. 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

  1. Apply  HFix-44396
  2. Create the following Dynamic System Settings (DSS).

    a. Purpose: reporting/useForceOrderHint 
        Ruleset: Pega-RULES 
        Value: true for ac 

    b. Purpose: reporting/useForceOrderHintForRRandAC 
        RulesetPega-RULES 
        Value: true 

    c. Purpose: reporting/useMergeHintForRRquery 
        Ruleset: Pega-Reporting 
        Value: true

     
  3. Restart the server.

Published October 9, 2018 - Updated December 2, 2021

Was this useful?

100% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice