SA-29290

Summary

A systems administrator has observed progressively slower performance from auto generated EMAIL correspondence.  

Specifically, the administrator observes that as system process load increases, the number of EMAIL correspondence waiting for transmission increases, and takes progressively longer to send, in hours long duration. This is a concern because of the time sensitive nature of the correspondence.

Error Messages

No error messages are reported on screen; no specific error messages were found in the Pega logfile.  

However, the Pega ALERT log does contain PEGA0005 alerts around the standard table PR_OTHER.

Steps to Reproduce

To reproduce this issue, trigger business logic that generates EMAIL correspondence.

Root Cause

The root cause was a problem in the design of the business logic.  

Specifically, the business logic had not been updated to map the specific standard class DATA-CORR-EMAIL to its own unique database table.

 As a result of this and other mistakes, several pieces of business logic were writing instances to the PegaRULES standard table PR_OTHER.  

This standard table has subsequently grown very large, and because of the few exposed properties, two database events were occurring: database full table scans to locate instances that satisfied queries, and reading the BLOB data of the instances, since properties were not exposed.

Resolution

To resolve this issue, advised administrator to create a unique database table for the class DATA-CORR-EMAIL, using the following procedure:

1) Copy the schema for the standard table PR_DATA, along with the standard two additional schema objects (unique constraint and INDEX).  Implement this copied schema as a new table.

2) Within PRPC: Define a new Class to Database Table definition (DATA-ADMIN-DB-TABLE or RECORDS / SYSADMIN / DATABASE TABLE). Map standard class DATA-CORR-EMAIL to this new database table

3) Move the existing instances of class DATA-CORR-EMAIL from the table PR_OTHER to the new table.  MOVE = destructive copy from the old table to the new table

4) On the new table, expose properties as needed.  After exposing the new needed properties for queries, run the standard PRPC utility COLUMN POPULATOR UTILITY

5) On the database, create additional INDEX schema objects as needed to tune performance of queries.