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

Database query performance degrades

SA-47846

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

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