Question
Areteans
NZ
Last activity: 30 Aug 2023 9:05 EDT
Custom pagination when fetching huge set of records from database using Stored Procedure
UI is sourced by a data page which accepts IDs as input which can be a csv of huge number of values(Ex: 3k). So we are using stored proc to get result set as report definition doesnt support in this case. Result set as well can be huge (Ex: 3k records for an input of 3k IDs as csv).
Though pagination can be configured on UI, all the records returned by stored proc would still be in clipboard which can cause performance issues. Can you let me know how custom pagination can be implemented in this case if any one has implemented it earlier or any suggestions to handle this.
Any suggestions are appreciated.
Thanks,
Satish
Pegasystems Inc.
GB
@SatishKumarL5551 To implement custom pagination for a large result set from a stored procedure, you can follow these steps:
Modify the stored procedure to accept additional parameters for pagination, such as 'startIndex' and 'pageSize'.
Update the stored procedure to return only the required records based on the 'startIndex' and 'pageSize' parameters.
Create a data page with parameters for the IDs, 'startIndex', and 'pageSize'.
In the data page, configure the source to call the stored procedure with the provided parameters.
On the UI, configure the table to use the data page as the source and enable pagination.
When the user interacts with the pagination controls, update the 'startIndex' and 'pageSize' parameters of the data page accordingly. This will fetch only the required records for the current page, reducing the memory footprint and improving performance.
This answer came from a Pega gen-AI assistant using the below references. As a Support Center moderator, I reviewed the answer and references for accuracy:
Data API performance and limitations > Performance considerations
@SatishKumarL5551 To implement custom pagination for a large result set from a stored procedure, you can follow these steps:
Modify the stored procedure to accept additional parameters for pagination, such as 'startIndex' and 'pageSize'.
Update the stored procedure to return only the required records based on the 'startIndex' and 'pageSize' parameters.
Create a data page with parameters for the IDs, 'startIndex', and 'pageSize'.
In the data page, configure the source to call the stored procedure with the provided parameters.
On the UI, configure the table to use the data page as the source and enable pagination.
When the user interacts with the pagination controls, update the 'startIndex' and 'pageSize' parameters of the data page accordingly. This will fetch only the required records for the current page, reducing the memory footprint and improving performance.
This answer came from a Pega gen-AI assistant using the below references. As a Support Center moderator, I reviewed the answer and references for accuracy:
Data API performance and limitations > Performance considerations
Best practices for configuring large data pages in offline-enabled mobile apps > Gui
Best practices for creating reports > Managing report settings