Question
Pegasystems Inc.
IN
Last activity: 5 Aug 2020 4:18 EDT
Report that fetches specific values of a column along with null values is slow
We have a report definition where requirement is to fetch records with specific values in a column along with null values. Due to very huge data in the table (over 1.5 million records) & OR logic in the filter criteria it is very slow.
Say, distinct values of a property named ManagementLevel are Top, High, Medium, Low & null (database null, not string).
Filter condition in report to fetch needed results is A OR B
A is ManagementLevel equals "Low","Medium"
B is ManagementLevel is null
Is there a way to avoid this OR logic which does a full scan / union of results that is leading to slowness?
Accepted Solution
Updated: 5 Aug 2020 4:18 EDT
Pegasystems Inc.
IN
I have resolved this issue in our application and would like to bring the trick to everybody's attention. This is a major issue when data is huge and full table scans and unions lead to performance degradation.
To achieve results needed in the mentioned scenario avoiding the OR logic that is leading to slowness, please do below actions.
Replace A OR B with one filter (just A).
Enter ManagementLevel on left hand side of the report filter and click fx to apply a function on it. Choose pxCaseWhenNULLInput from the autocomplete results.
Configure it as: When .ManagementLevel is NULL, then return NullResult else return .ManagementLevel
On the right hand side of the filter enter "Low","Medium","NullResult"
Here, NullResult refers to any arbitrary text that is not a valid value for the column referred. Get rid of OR logic would avoid full table scan / union of results leading to much better performance of the same report.