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.