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****
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).
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.