Hi everyone.
This is my first time posting an APEX question so go easy on me! I searched for similar questions and answers but couldn't find any
I have been tasked with assessing the performance (and perceived performance) of Interactive Reports and Interactive Grid. A lot of the perceived performance issues centre around pagination. The 'caching' of data on the webserver that IG does goes a long way to mitigating that which is great.
From conducting my own tests I have observed that the query fetch size is based on the 'rows per page' setting. If it is set to 10 it will fetch 52 records initially, then upon the first pagination it will re-execute the same query and fetch 104 records, then the next pagination it actually executes a second query (where apx$rownum<=:p$_max_rows )where apx$rownum>=:p$_first_row) and fetches 102 records per pagination, this second query is then re-executed every 10 paginations (each time fetching another 102). If rows per page is set to 1000, for example, this perceived performance boost is lost because the fetch size is 1002, so each pagination results in a new query execution. Paginating backwards is a dream, because the data is cached on the webserver, whereas in Interactive Reports the full query is re-executed for each pagination.
A lot of this is a bit academic, and yes why would anyone ever want to page through thousands of rows of data, I know... But I'm just trying to figure out how it works and what the limitations are.
So to cut a long story short here are my questions;
1) is the fetch size configurable? and if so, how and where. Could we configure it to always fetch 10,000 records at a time for example?
2) is there a limit to the amount of data that can be 'cached', either per session or instance-wide? (i.e paginating back and forth through 10 million records, will all of the data be cached or will it have to go back to the DB at some point)
3) is there any documentation out there that goes into any detail about how this all works?
Any replies welcome, even negative ones!
Thanks
Mark