Posted: 17 Aug 2017 3:06 EDT 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?
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.
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.
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.
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?
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.
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.
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.
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.
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.