Support Article

Inefficient OOTB Report definition causing performance issue

SA-47747

Summary



Inefficient OOTB Report definition causing performance issue


Error Messages



Warning is thrown in Pega Alerts file with exceeding threshold value
KPI Threshold:500
KPI Value:2051


Steps to Reproduce



While the application is in use Pega platform uses this report definition to search for relevant properties


Root Cause



This particular generic query is something which we are aware and has slight performance issues based on various factors. The WITH app_rulesets_pc0 queries tend to have long running times as it helps to retrieve relevant rules eligible for rule resolution and filtering the results for user in runtime. Please note that there are multiple rule metada & cache tables included on this query and hence takes more than expected time.

This is scheduled to be enhanced in future releases.

Resolution



Perform the following local-change:


W.r.t the reported performance issue on OOTB RD pzGetRelevantPropertiesForDataType, observed few PEGA005 alerts on the WITH app_rulesets_pc0 query.

Inspecting the PEGA005 alerts for this particular RD (Query) is showing 3-4 seconds as an average. In order to finetune this query execution we have the below index recommendations on pr4_rule_vw & pr4_rule_property tables which can added on your Pega DB.

Recommended Indexes for a performance benefit:

CREATE NONCLUSTERED INDEX pr4_rule_vw_idx7
ON
[PEGA722].[pr4_rule_vw] ([pyClass],[pyCircumstanceType],[pyRuleStarts],[pyRuleEnds],[pzClassType],[pyRuleAvailable])
INCLUDE ([pxInsId],[pyRuleSet],[pyRuleSetVersion],[pzRuleSetVersionMajor],[pzRuleSetVersionMinorPatch],[pzInsKey])
GO


CREATE NONCLUSTERED INDEX pr4_rule_vw_tidx2
ON
[PEGA722].[pr4_rule_vw] ([pyCircumstanceType],[pyRuleStarts],[pyRuleEnds],[pzClassType])
INCLUDE ([pyRuleAvailable],[pzInsKey])
GO

CREATE NONCLUSTERED INDEX pr4_rule_property_tidx2
ON
[PEGA722].[pr4_rule_property] ([pyClassName], [pxObjClass])
include (pypropertyname, pyLabel,
pyPropertyMode,
pyStringType,
pyMaxLength,
pyPageClass,
pzInsKey)
GO

Note: Replace PEGA722 with your DB/schema name.

You can also replace the name of the index “pr4_rule_vw_idx7” by the name of your choice. Please ensure you don't overwrite an existing index.

You can go ahead & apply these indexes on your Pega DB and monitor the performance here after.



Suggest Edit

Published March 29, 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.