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.
Tags:
Published July 23, 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.