Monday, August 31, 2009

Oracle ADF Tuning: Preventing SQL Query Execution on Page Load

While tuning Oracle ADF application in our team, we have discovered extremely good approach for application tuning on page load time. You can read about this problem in my previous blog post as well - Improving Performance in ADF Applications - Page Load Time in CRUD Forms.

Main idea of tuning approach is to prevent default query execution on page load and do it as easy as possible. We have achieved this by invoking executeEmptyRowSet() method for master View Object before opening page itself. We are performing invocation from Method Call activity in ADF Task Flow. Download sample application -

How it works: let's open our data entry page from main index page:

Data entry page opens in Insert mode. However, by default even page opens in Insert mode, framework still is sending default SQL query to database. If this query is complex and database performs many joins, it will take time to open your page. With this approach I'm describing today, no SQL query will be sent to database and page will open fast:

If user wants to edit data, ADF Query component should be used to filter data, only at that point SQL query will be sent to database:

How its implemented: ADF Task Flow contains Default Activity, this means it will be invoked each time when entering ADF Task Flow. This activity is Method Call:

This Method Call is invoking custom method implemented in Application Module implementation class - initPage() method:

Custom method implements call to executeEmptyRowSet() on master View Object and inserts new row. Method executeEmptyRowSet() prevents SQL query execution on page load by creating empty row set and in such way initializing iterator:

Spanish Summary:

En este artículo se explora una carateristica de ADF Business Componentes que permite evitar realizar consultas innecesarias a la base de datos mejorando la performance de las aplicaciones.


Unknown said...

Hi Andrejus,
I have a ViewObject Query table(select * from table) which loads by default on page load on my .jspx page.
My use case is display nothing 'no data to display' (on table) on page load ... like you're inserting a blank row (which is also fine) but ONLY display data when I do a "Search" (i.e click on a 'search' button )(I have a ViewCriteria query which performs the search)

I tried to apply your example on mine ...but all I could get to is i'm able to insert a blank row on page load (with your code) but after initial page load , I want to be able to display data after I perform a 'Search'

Unknown said...

continuation of my previous comment ....also forgot to add this ....
if I delete whatever I typed in the 'search' input box , it should also NOT display any data again ...
thanks a lot.

Anonymous said...

Hi your example is working but the initPage() methods is called twice,so its inserting two records in the viewObject so i cant perform other operations on the page like execute etc..

Any idea.

Andrejus Baranovskis said...

No, is called once - this is task flow method call.


Unknown said...

Hi Andrejus,

this works which is fine by me. For the sake of curiosity and continuous improvement :), just wanna know whether after these years, this is still the best approach for such case.

Best Regards, Pedro.

Anonymous said...

Hi Andrejus,
I have one scenario could you help to solve that.
Start-Date End-Date
Record 1 02-Jan-16 01-Jan-17 CHANGEButton

Click On change button
userenter startdate&end date new start date new end date
02-Mar-16 05-Jul-16

The pervious existing Record1 status becomes active=N we need to update below mention way

New start date after user entered dates
Start-date End-Date
Rec1 02-Jan-16 01-Mar-16
Rec2 02-Mar-16 04-Jul-16
Rec3 05-Jul-16 01-Jan-17

How achieve this in java using stored procedure please help me.