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

Portal loading takes long time

SA-29971

Summary



A systems administrator has observed low performance on PegaRULES standard tables PC_ASSIGN_WORKBASKET and PC_ASSIGN_WORKLIST.  

The systems administrator has requested assistance improving the performance of SQL imposed on these tables.

Error Messages



No error messages are displayed on screen; no applicable error messages were found in the Pega Logfile.  

Some PEGA0005 ALERT messages are present related to these tables.

Steps to Reproduce



Unknown.  No specific steps were found to reproduce the issue.  The issue appears as users return to their WorkLists to select WORK.

Root Cause



Low performance is attributed to poor database performance.

Resolution



Identify the standard seven INDEXes defined on both WorkBasket and WorkList tables.  These standard INDEXes are:

CREATE INDEX RefObjectWorkBasket ON pc_assign_workbasket (pxRefObjectKey ASC)
CREATE INDEX GetNextWorkFromBasket ON pc_assign_workbasket (pzInsKey ASC, pyErrorAssignment ASC, pxAssignedOperatorID ASC, pxUrgencyAssign DESC)
CREATE INDEX GetNextWorkInWorkGroup ON pc_assign_workbasket (pzInsKey ASC, pyErrorAssignment ASC, pxWorkGroup ASC, pxUrgencyAssign DESC)
CREATE INDEX BulkProcessFromBasket ON pc_assign_workbasket (pxAssignedOperatorID ASC, pxRefObjectKey ASC, pxRefQueueKey ASC)
CREATE INDEX RefObjectWorkList ON pc_assign_worklist (pxRefObjectKey ASC)
CREATE INDEX GetNextWorkFromList ON pc_assign_worklist (pxObjClass ASC, pxAssignedOperatorID ASC, pyErrorAssignment ASC, pxUrgencyAssign DESC)
CREATE INDEX BulkProcessFromList ON pc_assign_worklist (pxAssignedOperatorID ASC, pxRefObjectKey ASC, pxRefQueueKey ASC)


Administrator is advised to validate these INDEXes and ensure that they do exist; create them if necessary.

Also advised to RECOMPUTE STATISTICS for these INDEXes to ensure freshness. And to create standard overnight database maintenance jobs as necessary to maintain performance on these tables, as these tables are high-usage (High UPDATE/READ/WRITE/DELETE) tables.

Note: Custom Stored Procedures are also defined and are performing poorly (as per the ALERT log).  However, unable to determine if these stored procedures are in the same schema as the PegaRULES tables.  Therefore, if these custom stored procedures are in the same schema as the PegaRULES database, then the administrator must tune these stored procedures to perform better as they may be causing full table scans (FTS).
Suggest Edit

Published November 18, 2016 - Updated October 8, 2020

Did you find this content helpful? Yes No

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

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