APEX 5 IR Guts and Performance

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.

APEX5 IR Guts and Performance

Smart use of the IR settings makes for a better end user experience ~ and that’s the whole point!

5 thoughts on “APEX 5 IR Guts and Performance

  1. Hi,

    Nice posts!
    You are talking about ‘include features to FORCE users to filter first’. Where can I find this feature in the builder, how to implement this?



    • Hello Arian,

      The feature to declaratively (by the developer) force users to apply a filter before displaying the report is included in APEX 5.1 Interactive Grid. Look in the Interactive Grid Attributes.

      In APEX 5 and below, we simply add a condition to the report region, and that condition is that one or more filters in a separate, “selections” region must be not null. The Interactive Grid feature is nicer since the filter is an Interactive Grid filter, not an external LOV.

      Hopefully you are able to upgrade to APEX 5.1!
      If you need additional direction or have additional question, please contact me, KC

      • Thank you.

        We are using apex 5.1 at the moment. We were farmiliar with the method you described above for previous versions.

        I still can’t find the proper option to force users to filter first.
        How is it called? How to implement it?


      • Arian, Sorry to take so long to reply. Is the option there? Yes and No. Declaratively, No. But, as part of the Interactive Grid widget, yes. But is it ready for prime time? I think No. ( as of APEX 5.1.1) When I look at the widget options, I can see a filter option is there, but as yet I have not got it to work. And I do not know even if that is the intent of this option. Remember that the Interactive Grid widget options may not be completely working yet – they are a work in progress. so anything I saw prior to release – as I saw a demo of that filter-before-display feature – may not have actually made it into the release. I suspect it will be there eventually, but of course only Oracle can say for sure :).

  2. Hello,

    Nice post.
    Have you already implemented the feature to force user to filter first?
    How can we implement this in the Interactive grid ?


Leave a Reply