Discussion
Capgemini, Sweden
SE
Last activity: 13 Apr 2015 18:36 EDT
Performance Issue in opening and downloading list view Report
Hi All,
I am having a list view report without pagination. It is having close to 8000 records (records are increasing daily). So opening and downloading the report takes hours and in future it will be impossible as it will have more records.
Its a business requirement to download all records in one excel, so i cannot have pagination because it downloads just 200 records in one time download.
Could anyone please suggest any idea to improve it or to implement in other way?
Thanks,
Kausar
Pegasystems Inc.
US
Hi Kuasur,
Hi Kuasur,
Before exploring other implementation approaches you should first try to optimize the performance of the report you are currently using. To learn How to detect lengthy PegaRULES database operations
(that may be generating PEGA0002, PEGA0003, PEGA0005, or PEGA0042 alerts) please refer to the following Pega 7 Help Topic: (https://pdn.pega.com/sites/pdn.pega.com/files/help_v71/procomhelpmain.htm#howto/howto2/monitorlengthydatabaseresponses.htm). To improve the performance of the report you may need to optimize some of the Properties you are using in the report by exposing them as columns this is especially important for Properties used as Filters in your Report Definition Rule. To learn How to expose a property as a database column please refer to the following Pega 7 Help Topic: (https://pdn.pega.com/sites/pdn.pega.com/files/help_v71/procomhelpmain.htm#howto/howto2/howtoexposeapropertyascolumn.htm). In some cases simply exposing the Property as a column may not be enough (for example if you are exposing properties from a PageList or PageGroup), in those cases you may have to expose the data using a Declarative Index. To learn How to set up a declarative index please refer to the following Pega 7 Help Topic: (https://pdn.pega.com/sites/pdn.pega.com/files/help_v71/procomhelpmain.htm#howto/howto2/howtosetupindex.htm).
Finally, there are several other suggestions including alternative implentation approaches to consider for reports that generate result sets larger than 10,000 rows. Please review the “How to address advanced reporting requirements” Pega 7 Help Topic for more details (https://pdn.pega.com/sites/pdn.pega.com/files/help_v71/procomhelpmain.htm#howto/howto2/howtoadvancedreporting.htm).
Hope this helps.
Regards,
Henry
Pega Academy
Capgemini, Sweden
SE
Thanks Henry for your reply.
Actually i am using an external database and a view for the source of list view report . So all columns are exposed.
Thanks,
Kausar
Pegasystems Inc.
US
Hi Kausar,
Consider working with your DBA to optimize the SQL used by the View. For example avoid hard coded values (use bind variables instead). For example, "SELECT DAYS_AVAILABLE from {Class:MyCo-HR-Data-PTO} WHERE OPERATOR_ID = {.OPERATOR_ID}". Also try to write queries that return smaller result sets. Double check that the View being used is using indexes efficiently. In general try to avoid database-intensive queries. Additional document for writing effective SQL for PRPC is available on the PDN.
Regards,
Henry
Pega Academy
Capgemini, Sweden
SE
Hi Henry,
In view (used for list-view) I am using exposed columns and nothing like as you suggested.
Only problem is becasue I need to show 8000 records but when I am using same view in TEST environment with 4000-5000 records then it gives better performance.
I was wondering if it is possible to show only filter criteria for list-view without any results and on providing filter criteria system can pull the results. In this case system has not to show all 8000 records at once and it can improve the performance.
Please let me know.
Thanks,
Kausar