Friday, May 18, 2018

Lazy Loading Report

I was recently asked to have a look at an APEX page that took some time to load. That particular page was accessed using the navigation menu. Since the load time was longer than usual, users were often clicking multiple times on it while waiting.

As usual, I turned debug mode on and had a look at the result. It turned out to be the underlying query of the page's report that was slow.

We were able to fine-tune the query to make everything load faster. But it still took a couple of seconds to render.

What I did was to make the report lazy load the data, similar to what the Interactive Grid can do.

The idea is the following
  1. Create a hidden item on the page
  2. Create a computation using the "After Regions" point to set the item to 'Y'
  3. In the report, add a where clause that checks if the item's value is equal to 'Y'
  4. Create a dynamic action on page load that refreshes the report
What this is doing is that when the report is being rendered for the first time, the calculation has not been executed and the item is still null so that the report will show nothing.
Then, the calculation will be executed and the dynamic actions will refresh the report showing the expected results.

Don't get me wrong here, the overall process is still going to take the exact same time.
If the page is taking 10 seconds to load, it will still take 10 seconds for the page to fully load.
But, the user experience is going to be a lot better because now the page will load instantaneously, then the report will take some time to load while displaying the processing icon.

Here's what the query would look like
select /* your columns */
  from /* your tables */
 where 1 = 1 
   and nvl(:P1_IS_LOADED, 'N') = 'Y'

If you also need to be able to download the report, you'll need to add another condition so that it can work:
select /* your columns */
  from /* your tables */
 where 1 = 1 
   and ( /* Standard Page Load */
         nvl(:P1_IS_LOADED, 'N') = 'Y'
         
         /* Report Download */
         or substr(:request, instr(:request, '_', -1) + 1) in ('CSV','XLS','PDF','RTF','HTMLD')
         
         /* Report Email */
         or wwv_flow.g_widget_action = 'SEND_EMAIL'
         )

For more information about the above condition, you can have a look here.

Here's what the end result looks like compared to the standard behaviour:


You can have a look at it in action in my Demo Application

Enjoy!

9 comments:

  1. Excellent solution.... Thank you so much Mazime

    ReplyDelete
  2. I was struggling for more than a week to achieve the page load graphics ring in APEX 5.0. I need to show this ring while user waits on a page to see the report content after few seconds. All my attempts with apex.widget.waitPopup and apex.util.showSpinner were either failed or not satisfactory. Finally I posted a question in Oracle community forum for APEX and got directed to this Blog page. This worked like a relief. Thanks a lot!

    ReplyDelete
  3. Thanks for sharing this info!
    That was highly informative

    Melbourne Web Developer

    ReplyDelete
  4. Great Thx gans for your artikel about Lazy Load, I hope you succesfully

    ReplyDelete
  5. This is a great solution to let users wait while page loading. But the report download is empty. How do we download report data using this solution

    ReplyDelete
    Replies
    1. Thanks Chellam

      Yes, downloading will not work nor would the subscription email.

      What you can do is this:
      select /* your columns */
      from /* your tables */
      where 1 = 1
      and ( /* Standard Page Load */
      nvl(:P1_IS_LOADED, 'N') = 'Y'
      /* Report Download */
      or substr(:request, instr(:request, '_', -1) + 1) in ('CSV','XLS','PDF','RTF','HTMLD')
      /* Report Email */
      or wwv_flow.g_widget_action = 'SEND_EMAIL'
      )

      I explain it here:
      https://max-tremblay.blogspot.com/2018/08/include-or-exclude-columns-in.html

      Delete
    2. Thanks a lot. Now I am able to download the report.

      Delete
  6. You rock! I've spent endless hours to fight back APEX's freeze before the data arrives. Now it shows the spinner!

    ReplyDelete