Support Article
Database query performance degrades
Summary
Database query performance degrades and Rule Assembly Cache exceeds the threshold value.
Error Messages
Alerts in the log:
PEGA0005 and PEGA0039
Steps to Reproduce
Unknown
Root Cause
A defect or configuration issue in the operating environment.
Indexes are not present in some table columns. Several users have checkout privileges as observed in the below Personal Ruleset.
Rule assembly process has exceeded the threshold of 400 ms: 703 ms. Details: ;Total Rule assembly process: ELAPSED time = 703;CPU time = 0;Delta Java assembly process: Delta Assembly ELAPSED time = 703;Delta Assembly CPU time = 0;Delta Compile process: Delta Compile ELAPSED time = 0;Delta Compile CPU time = 0;Assembled class name = com.pegarules.generated.html_section.ra_stream_pxuserdashboard_1d58e55125c01cf5f78c7638ee96b56b;Assembled class size = 108,637;Rules count = 11;Personal Ruleset = xxxxxx@;AccessGroup = Wxxxx:Administrators;Application = WTON 01.01.01;FuaKeys = RULE-HTML-SECTION|CPM-PORTAL!PXUSERDASHBOARD;STREAM|Wxxx:Administrators!Prod!01.01.01$04390db9221a9b4bddec31ad6a2f852d
Resolution
Perform the following local-change:
1. Run the below scripts to create an Index.
CREATE INDEX
RULES.PR_LOG_RULE_NODE_IDX1
ON
RULES.PR4_LOG_RULE_USAGE
(
PXSYSTEMNODE ASC,
PYLABEL ASC,
PXLASTUSE DESC
)
CREATE INDEX
RULES.REF_RULES_IDX1
ON
RULES.PR_INDEX_REFERENCE
(
pxInsIndexedKey ASC,
pzTenantId ASC,
PXINDEXPURPOSE ASC
)
USE [PEGA72]
GO
CREATE NONCLUSTERED INDEX [pr4_rule_vw_idx7]
ON [PEGA72].[pr4_rule_vw] ([pyClass],[pyCircumstanceType],[pyRuleStarts],[pyRuleEnds],[pzClassType],[pyRuleAvailable])
INCLUDE ([pxInsId],[pyRuleSet],[pyRuleSetVersion],[pzRuleSetVersionMajor],[pzRuleSetVersionMinorPatch])
GO
Note: Replace PEGA72 by the appropriate schema name and check for a unique index name.
2. Run the Static Assembler to preassemble the rules.
Published March 27, 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.