Close popover
Zane Beckman (ZaneB617)
Senior System Architect
ZaneB617 Member since 2015 1 post
Posted: November 7, 2018
Last activity: November 7, 2018

Performance enhancement for Rule-Obj-Class.OnBeforeDisplay Activity

In the past, I've noticed a severe delay in opening the Rule-Obj-Class record for certain classes. I did some investigation and here is what I discovered.

Certain classes (System-Queue- classes for example) lack an index on the pxObjClass column to enhance write performance. On opening the Class rule, the following query is executed (replacing the class with the respective class that was opened):

SELECT count(*) as "pyCount" FROM {Class:System-Queue-DefaultEntry} WHERE pxObjClass = 'System-Queue-DefaultEntry';

Since there is no index, the database is forced to scan the entire table and generate a total count of rows matching pxObjClass. If there is a large number of rows, this can take several minutes or longer. One solution would be to simply add the index on pxObjClass, but then we lose the benefit of enhanced write performance. Further, on inspecting the Rule Form more closely, we actually don't need to know the total count, but rather just whether there is at least one row.

Given this information, we can make the following change to the query:

SELECT CASE WHEN EXISTS (SELECT 1 FROM {Class:System-Queue-DefaultEntry} WHERE pxObjClass = 'System-Queue-DefaultEntry') THEN 1 ELSE 0 END AS "pyCount" from DUAL;

This query will scan the table until it finds a single row matching the pxObjClass, which is orders of magnitude faster in some cases.

I hope this information can be forwarded to the correct team to evaluate for inclusion in the platform.

Pega Platform Enterprise Application Development Dev/Designer Studio System Administration Data Integration
Moderation Team has archived post,
Close popover This thread is closed to future replies. Content and links will no longer be updated. If you have the same/similar Discussion, please write a new Discussion.