Report definition - Parallel hint
Hello!
I have a report definition which is taking a very long processing time. We managed to extract the SQL which the report definition is performing and handled to our DBA team to analyse it. Their response is:
- The query will not be improved with indexes.
- Add PARALLEL hint in the query in order to increase the performance (it will increase the CPU usage).
Anyone here implemented the "PARALLEL" hint in the query performed by the Report Definition? Can Pega handle it?
***Edited by Moderator Marissa to update platform capability tags****
Accenture Brazil
BR
Hi Marc!
I am not sure that I can share the Report Definition openly. I will need to check with my project managers.
Meanwhile, I am using PRPC-7.3.0-126.
Barclays
GB
Hello,
Can you confirm if you are not making lot of class joins in the report definition and also under filter condition, logical operations are not using wildcards such as "contains, starts with or ends with"?
If so, you may check with your DBA and translate it into a Database view table and eventually you may directly refer that table in Pega (by creating a new db table class mapping).
Accenture Brazil
BR
Hi,
This report is making one class join and using seven filters with the following conditions:
- Is equal: 2
- Is not null: 2
- Does not start: 1
- Does not contain: 2
Do you think this is a lot?
Pegasystems Inc.
US
The "Does not contain"s are the main culprits here, I suspect, although the "Does not start" doesn't help either.
Does not contain involves a full table scan, and indexing does not help. And you're doing 2 of them.
If the report is always using the same "Does not contain" values, and depending on the time constraints of the report (e.g., if the report is on data up to the end of business yesterday), then you can pre-load a view with the costly filters already applied.
If the report is not used interactively, you can use BIX to generate it during off hours and have it ready for your users when they start their day.