Question
paypal
US
Last activity: 12 Oct 2017 4:22 EDT
Max Records Query on Report Definition
Does anyone know if report definitions max rows to retrieve actually translates into a "fetch first 500 rows only" type of SQL or does Pega only process the first 500 results after all the results are returned from the DB?
The SQL seems to suggest the later, since there's no limiting SQL when looking at the SQL in pyReportContentPage.
PEG
IN
Hi ,
Check the 'general data access settings' in the report definition 'Data Access' tab. As said in below thread take care of performance impact before giving any limit or 0https://collaborate.pega.com/question/increase-max-records-report-definition
Updated: 27 Nov 2023 12:49 EST
Pegasystems Inc.
US
Hi Vinay & Greg,
I had explained about this query trigger confusion in this discussion earlier. You can have a look into it for more information about it.
Regards
Mahesh
Saltech Consulting
HU
Thanks, Mahesh!
I have also found that post. Based on your last reply, you use setMaxRows at the JDBC API level to limit records.
This means that the full query is executed and then limited on the java server. See:
Note that all that setMaxRows is doing is reducing the size of the ResultSet object. It won't affect the speed of the query. setMaxRows doesn't change the actual SQL - using top/limit/rownum e.g. - so it doesn't change the work the DB does. The query will return more results than your limit if there are more results to return, then truncate them to fit your ResultSet. via https://stackoverflow.com/questions/32399546/what-is-the-difference-between-statement-setmaxrows-vs-statement-setfetchsize-in
This is not the most performant way to retrieve 500 results from a query that can return millions, I think.
Pegasystems Inc.
US
Yes Greg, conceptually setFetchSize(int) can be better than setMaxRows(int) in terms of performance when we have huge data. I assume in pagination concept the user might navigate through the different pages frequently, so in this case the number of database round trips might become more if we use setFetchSize method. But i am not sure about the reason behind the usage of setMaxRows method for pagination concept using RD as i am not part of the engineering team. May be some Product expert in this domain might answer your queries Greg, as i had provided the above information by looking into the code.