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

Slow CLM OOTB section CollectDocuments

SA-57542

Summary



Performance degrades during the load of the 'document collection' phase where the CollectDocuments out-of-the-box section displays in PegaCLMFS:07-22-01 ruleset.


Error Messages



According to the database trace, the following query is poor performing:

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 PREP02_RULES.pr4_rule_vw "r" INNER JOIN PREP02_RULES.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 PREP02_RULES.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 "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 PREP02_RULES.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 PREP02_RULES.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 PREP02_RULES.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"."PYLABEL" AS "pyLabel" , "PC0"."PYRULENAME" AS "pyRuleName" , "PC0"."PYCLASSNAME" AS "pyClassName" , "PC0"."PYRULESET" AS "pyRuleSet" , "PC0"."PYRULESETVERSION" AS "pyRuleSetVersion" , "PC0"."PZINSKEY" AS "pzInsKey" , "PC0"."PXINSNAME" AS "pxInsName" , PREP02_RULES.pr_read_from_stream('.AppliesWhen', "PC0".pzInsKey, "PC0".pzPVStream) AS "AppliesWhen" FROM PREP02_RULES.pr4_rule "PC0" INNER JOIN resolved_rules_pc0 "resolved_rules_pc0" ON ("PC0".pzInsKey = "resolved_rules_pc0".pzInsKey) WHERE ("PC0"."PYRULEAVAILABLE" IN (? , ?) AND PREP02_RULES.pr_read_from_stream('.AppliesWhen', "PC0".pzInsKey, "PC0".pzPVStream) IS NOT NULL) AND "PC0"."PXOBJCLASS" = ? ORDER BY 2 ASC


Steps to Reproduce



Run the Client Lifecycle Management (CLM) 'document collection' phase.


Root Cause



A defect in Pegasystems’ code or rules.

By default, the .AppliesWhen property in the Rule-Requirement class is not exposed. The GETREQUIREMENTSETLIST out-of-the-box report definition used the pr_read_from_stream user-defined function (UDF) to retrieve the property data from the BLOB.



Resolution



Perform the following local-change:

Expose the AppliesWhen column for the corresponding property in the Rule-Requirement class. As a result, data is not retrieved from the BLOB when the report definition is executed.
Refer to the following PDN article to expose a column: https://pdn.pega.com/support-articles/properties-are-not-optimized-warning-when-udfs-are-disabled

Alternatively, update to CLM 7.4.

Published July 23, 2018 - Updated December 2, 2021

Was this useful?

0% 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?

Want to help us improve this content?

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
Contact us