Question
paypal
US
Last activity: 8 Sep 2017 8:07 EDT
OOTB Report Definition Pagiation
I have an question on OOTB RD pagiation. While using data pagination, we dont see any rownum in the generated query. Does it actually run the same query whenever we navigate to next page and fetch entire resultset from database and while copying the resultset in the clipbooard, copies only 10[say pagniation is 10] rows in the clipboard, discarding the rest?
Blue Rose Technologies GmbH
BE
Hi,
Paging allows the system to retrieve only the data needed for the current display, rather than the full set of data, and this can improve response time and reduce the size of the clipboard.
For more information, you can check the paging section in the below link :
https://community.pega.com/sites/default/files/help_v72/procomhelpmain.htm
Virtusa IT Consulting
AE
"while copying the resultset in the clipbooard, copies only 10[say pagniation is 10] rows in the clipboard, discarding the rest?" - I don't think it will discard the rest.
I would have used tracer to find out the query structure for each page on the report, by enabling 'DB query' event in tracer settings.
Pegasystems Inc.
US
Hi Vinay,
Pagination works this way:
Each navigation to a different page will execute the query and fetch all the results. What results have to be shown will be decided during packaging (pxResults formation)
Lets say I have enabled paging with page size 10 and there are 30 records in total. Results will have 3 pages. On click of Page2 navigation:
- Inputs captured: Page index-2 Page size-10. Using these inputs it will calculate start index and end index of the rows to be returned.
- Execute query and fetch all rows
- Packages only the rows from start index to end index(11-20 in this case).
Hope this information might help you.
Regards
Mahesh
paypal
US
Thanks Mahesh !!!
If each navigation fetches all the results then how would the performance be better?
So Pagination Reduces the Clipboard Size the UI Rendering time but it does not use the rownum in the query to restrict the Resultset from the DB?
Pegasystems Inc.
US
Hi Vinay,
I think you misunderstood my explanation, it will use Page Index and Page Size inputs to fetch the results from database and it wont fetch all the records from DB. May be the sentence "fetch all rows" confused you, it means that it will fetch all the 10 records from 11-20 using Page index and page size.
Hope this is clear to you.
Regards
Mahesh
paypal
US
Hi Mahesh,
When i turned the DB Tracer on and checked the prepared statement, i dont see the rownum or limit clause in the query with or without paging option.I also dont see any restriction to limit the result set for listResultCount and packageResult.
Does PEGA add the rownum clause internally and not visible to us in the DB Trace, i am just trying to find the implementation as the prepared statement is the same with or without pagination option selected.We want to do this to improve performance.
Pegasystems Inc.
US
Hi Vinay,
After further research found the below information over here.
For list-type reports that can return a large amount of data, you need to consider how to display the report to the user in the most convenient and comprehensible way. With a long report, an alternative to making the user scroll down to see additional rows is to divide the results into "pages". The user can then navigate through the pages of the report to browse the data and find items of interest..
Paging allows the system to retrieve only the data needed for the current display, rather than the full set of data, and this can improve response time and reduce the size of the clipboard.
Paging is not available with summary-type reports, whether based on Summary View or Report Definition rules. The system needs to acquire all the report data at once in order to summarize it, so there is no technical benefit to be gained by using paging in such reports.
Hi Vinay,
After further research found the below information over here.
For list-type reports that can return a large amount of data, you need to consider how to display the report to the user in the most convenient and comprehensible way. With a long report, an alternative to making the user scroll down to see additional rows is to divide the results into "pages". The user can then navigate through the pages of the report to browse the data and find items of interest..
Paging allows the system to retrieve only the data needed for the current display, rather than the full set of data, and this can improve response time and reduce the size of the clipboard.
Paging is not available with summary-type reports, whether based on Summary View or Report Definition rules. The system needs to acquire all the report data at once in order to summarize it, so there is no technical benefit to be gained by using paging in such reports.
This article explains how to enable paging for reports in Process Commander, and also reviews report features that may make paging unnecessary.
Note: You can use list-type reports (whether generated by Report Definition rules or List View rules) as the data source for a grid layout. However, in this circumstance, the paging options set for the grid have precedence over any pagination established within the report's rule.
I sourced a grid with report definition and added the pagination of 10 records in each page. When i checked the clipboard i couldn't see the usage of pyPageIndex and pyPageSize parameters in my Pre or Post queries of the report definition. But when i check the number of records being fetched in my report definition it is 10 as per pagination which is shown in screenshot. So for sure we are fetching only the specified 10 records from the database out of 18 records in my case as shown in tracer.
I would like to do further investigation regarding the usage of pyPageIndex and pyPageSize parameters in the code to perform the pagination.
Hope this gives you further information regarding your query.
Regards
Mahesh
paypal
US
Hi Mahesh,
Ignore my request, even you are not able to see the pageIndex in pre or post query. Based on the DB Trace, I am thinking that the result set from DB is to fetch all with where clause and no rownum. Once the DB returns the result set, PEGA is selecting the required sub set as part of packaging(Inside PEGA Engine) before getting it to the clipboard.
Is there a way where we can confirm this with PEGA engineering team?
Pegasystems Inc.
US
Hi Vinay,
I will try to connect with the engineering team and get this information.
Regards
Mahesh
Pegasystems Inc.
US
Hi Vinay,
After cross checking with the Product specialists came to know that the filtering of the records are being done at the JDBC API level by using setMaxRows method in PreparedStatement as explained over here that is the reason the limit doesn't show in the query as database driver handles the limit under the covers.
To confirm you we do the query and then we jump to the result we want in the ResultSet and do a separate query for every page navigation. If we were fetching all the records each time and then performing the filter then it will affect the performance drastically as each page navigation needs database interaction.
Hope this information is clear to you now.
Regards
Mahesh
paypal
US
Thanks Mahesh !!!
Can you please help us clarify the below line from your post.
"To confirm you we do the query and then we jump to the result we want in the ResultSet and do a separate query for every page navigation."
What we understood is that there are 2 queries which are getting executed based on your reply, so can you please let us know what does the first & second query do? Also it would be of great help if you can share the queries. Also if we add sorting(Order By Clause) how does the pagination work?
We are confused with the statement "we do query and then we jump to result and do a separate query" what does the separate query do if you already have the results and jumping to the required resultset.
Pegasystems Inc.
US
Vinay - i think you got confused, i mean to say that we execute the original report definition query by enabling the pagination via PreparedStatement.setMaxRows(count) method for each page navigation.
For example, if you have 30 records and you added the pagination of 20 records in each page then it will fetch first 20 records from the database with the help of pyPageIndex and pyPageSize fields with the help of JDBC API filter. Now when you navigate to second page then it will run the select query again to fetch the records of second page with the new values of pyPageIndex and pyPageSize.
So for each page navigation it will fetch the records from database with the corresponding filter criteria with pagination property values.
Hope it is clear to you now.
Pegasystems Inc.
US
Adding to the above information you can have a look into Rule-Obj-Report-Definition.pyContent.pyPaging page which holds the information about the paging mechanism and this page is used to get the results from the query.
Capgemini
IN
I have also seen this and thought of its impact on performance. I think pega fetches all the results and while packaging it to pxResults consider the pagination. It certainly reduces the screen rendering time but does not help in the DB fetch time.
If you try to execute executeRDB api from pega engine it also does the same thing. It packages 50 rows from the entire data set(without filtering the data at DB level) but you would be able to override it via setting max limit on the RDB page.
-Saikat
Pegasystems Inc.
US
Hi Sai,
No, we don't fetch all the records from database then perform the filter. We perform the filter at the JDBC API level using PreparedStatement setMaxRows(count) method as explained over here.
Regards
Mahesh