Generating Report Definition considering performance criteria in Production
In DPage I have one post processing activity where I am calling one report which will get all results like
- What are the cases we worked upon,
- Report should be based on certain conditions like SLA, if SLA is less than deadline.
- Like that I have couple of more sorting criteria
- I have 200+ thousand records
For better performance, logic should be done at one go. How?
Thanks,
***Edited by Moderator: Pallavi to update platform capability tags***
Pegasystems Inc.
US
I'm not sure what your question is here, can you elaborate?
It sounds like you will have ~100K rows sorted by multiple columns in your report. Are you asking how best to do that from a performance standpoint?
If so, a lot of that will happen in the database, not in Pega. In order to get the query to run efficiently, the indexes on the table(s) must be set up correctly.
Depending on the nature of the data, you might want to consider setting up a database view that is pre-populated ahead of time.
Accenture
IN
Hi RonLee,
My apologies, I have reconstructed/rephrased my question. I saw your response which is really useful, would like to add any additional point for better performance of a report?
Thanks,
Updated: 16 Apr 2020 14:58 EDT
Accenture Federal Services
US
Filtering the result on the report definition that incorporates the business logic criteria like SLA is less than deadline should enhance performance. So, the more filters that pair down the results, the less results the activity has to process.
Pegasystems Inc.
US
I assume that the result set is fixed by whatever filter criteria are used, so the post processing will just have to deal with whatever the query returns. Again, the performance of the query will depend on the indexing on the filter columns, as well as the columns involved in join conditions if there are any joins. This is particularly true of large tables (and 200+ K records is starting to qualify as large).
Once you have created the report definition, you should have your database people run the query and generate an explain plan for it. That will tell them what opportunities there are for optimizing the tables/columns in the database for the report to run as fast as possible.