Support Article
Report with "Filter by rule resolution" times out
SA-47352
Summary
Queries are getting timed out after 30 seconds when trying to change something on the section or when clicking basic palette.
The report definition used is 'pyGetBasicPegaControlsForPalette'.
The issue can be avoided by clearing the "Filter by rule resolution" check-box under Rule filtering options.
Error Messages
The error in the logs shows:
SQLState: HY008 Message: The query has timed out.
012 [nssl-nio-443-exec-11] [TABTHREAD1] [ ] [ <YourAPP>:01.01.01] (dbms.JdbcConnectionManagerImpl) ERROR <YourHostname>|<YourIP> User- Not returning connection 2 for database "pegarules" to the pool as it previously encountered the following error
User ID: User
Last SQL: 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 pr.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 pr.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 = 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 pr.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 pr.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 pr.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 DISTINCT "PC0"."pyLabel" AS "pyLabel" , "PC0"."pyPaletteOptions" AS "pyPaletteOptions" , "PC0"."pyControlImage" AS "pyControlImage" , "PC0"."pyStreamName" AS "pyStreamName" FROM pr.pr4_rule_html_property "PC0" INNER JOIN resolved_rules_pc0 "resolved_rules_pc0" ON ("PC0".pzInsKey = "resolved_rules_pc0".pzInsKey) WHERE ( "PC0"."pyRuleAvailable" NOT IN (? , ? , ? ) AND "PC0"."pyRuleSet" LIKE ? AND "PC0"."pySectionPalette" = ? AND "PC0"."pyPaletteOptions" = ? ) AND "PC0"."pxObjClass" = ? ORDER BY 1 ASC
com.microsoft.sqlserver.jdbc.SQLServerException: The query has timed out.
at com.pegarules.generated.definition.ra_action_pygetbasicpegacontrolsforpalette_86584d24ce28924efd4c6aff9c274fdc.perform(ra_action_pygetbasicpegacontrolsforpalette_86584d24ce28924efd4c6aff9c274fdc.java:52) ~[?:?]
Steps to Reproduce
- Open a section rule.
- Click basic pallet.
Root Cause
Cardinality Estimator feature, added since MS SQL Server 2014, is responsible for the observed behavior.
The issue can be addressed by applying the FORCE ORDER Query Hint to the affected queries.
Resolution
Install HFix-39255 and create the following DSS to add the FORCE ORDER Query Hint to generated queries showing this behavior:
- Purpose: reporting/useForceOrderHint
- Ruleset: Pega-RULES
- Value: true
Published March 23, 2018 - 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.