APEX 5 Interactive Reports (IR) are powerful out of the box, but one can significantly improve performance by strategic settings of certain key parameters. The full presentation covers all the options.
When your data volumes are low to medium, there may not be much noticeable difference. But when data volumes are high, or the query is resource intensive, the following advice and settings can have a significant performance impact.
In brief:
- Tune your query, then fine-tune your SQL query. Any slowness here will be magnified in APEX
- Check MAXROWCOUNT. The more rows, the longer it takes APEX to retrieve them. do users really need ALL the rows? OR is it reasonable to ask users to filter first. (APEX 5.1 Interactive Grid will include features to FORCE users to filter first – nice!) Consider supplying users who need to Download All a separate pag,e os not all users are handicapped by a high MAXROWCOUNT value.
- Check Max Rows Per Page
- Check your Pagination setting. The X to Y of Z is popular, but the “of Z” part is expensive – APEX needs to retrieve all rows then set up pagination. That All Rows part takes time. Consider using X to Y, and adding a page item that calculates the Z separately. (THere are various options to do this, a topic for another blog post, ranging from a straight COUNT(*) to a sophistcated use of APEX_IR to get the count of the as-filtered IR query)
These are your big-impact settings.
For more info, see the full presentation via the link below or Slideshare:
As you move to APEX 5 Interactive Grid, these three settings till apply – but you have better pagination options, and pagination works differently – heck, all of Interactive Grid works differently – better! So watch here for upcoming information on Interactive Grid.
Smart use of the IR settings makes for a better end user experience ~ and that’s the whole point!