ADF View Object Performance Tuning

  Recently there is a use case which is very interesting. We have an Enterprise web application which supports multiple countries. For one country it specifically has something called Contributor Class. It is comprised of more than 8000 static records, and needs to be displayed on UI page as an ADF LOV. The initial design and implementation of this LOV from another developer was to use a transient view object. Then the developer overrides transient view object's life cycle methods:

     executeQueryForCollection(),  createRowFromResultSet(),  hasNextForCollection(), getQueryHitCount(), findByViewCriteriaForViewRowSet()

  What I derived from such implementation is the original developer wants to achieve case insensitive search in the UI LOV. Because I saw such case insensitive logic in overridden findByViewCriteriaForViewRowSet method.

  Later on another developer implemented some logic in web service layer to default a value for contributor class on UI page when a member id is entered.

  Due to corporate security policy I cannot disclose source code here, and no screen shot either.

  To help you understand, I am re-iterating all the facts again below:

1. Order capture enterprise web application has an ADF LOV - (Contributor Class) on UI page backed by an ADF transient view object;
2. A web service was implemented to default the Contributor Class LOV at run-time when a member id is entered;
3. Order capture web application has a header section which has a shipping information section and Contributor Class LOV is part of it; the shipping information section ADF iterator binding has 'ppr' as its change event policy;

  Then after these 2 new features went in production, user reported that when they a) enter a member id, a contributor class was defaulted; b) they found the defaulted contributor class is not correct, they corrected it; c) they priced the order; Bang! These 3 simple steps took 11 minutes to finish. Our production server is a powerful Linux multi-CPU with multiple virtual servers for our order capture application.

  Why?

  This contributor class is an ADF input LOV backed by a transient vo, and its parent view object is shipping address view object. In page definition file its change event policy is ppr. So any other shipping field change will cause it to refresh - to re-execute its query. By default since it is programmatic view object, there is no actual database SQL query, its view cache will be cleared when executeQuery() is called and the framework will repeatedly call createRowFromResultSet in its ViewImpl class to re-create 8000 rows again. On UI, when end user clicks on LOV amplifier glass, or enters a value it will take more than 30 seconds for the LOV search criteria pop-up to come on screen. User browses and selects a value from 8000 rows will also be painfully slow. User makes a selection and closes the pop-up will take 30 seconds again each time.

  Enough story, what I did to address it?

1. Create a database table, build Entity Object; Check 'Use Update Batching' in Entity Object 'General' tab 'Tuning' section. This option doesn't help much in this case
2. Create View Object from Entity Object; 
3. Create 'insert only' version View Object from Entity Object; JDeveloper View Object editor 'Overview' 'General' tab 'Tuning' section 'Retrieve from the database' section, choose 'No  Rows' (i.e. used only for inserting new rows). 'Access Mode' choose 'Forward Only'. This step is used for programmatic populate contributor class view object from web service. Since it is forward only view, framework doesn't need to care about run-time scrolling back and forth within the retrieved data set. Since 8000 records uploading into database will have a lot of overhead, this actually will be slower than populate transient view object. But this forward only view object will be the fastest for populating a view object backed by an entity object.
4. Create view criteria for view object created in 2; the criteria item will by default support case insensitive search. Also notice that query execution mode is 'In Memory'. 
5. In parent view object, specify the search region to use view criteria you created in step 4. which supports case insensitive search;
6. Define SQL query hint for view object created in 2;  this step is very important. We already know that SQL query hint will improve SQL query drastically. This tells SQL parser what you intend it to do. In this case the SQL parser will do its best to populate run-time UI  view port with records retrieved from database in the fastest way it could.
7. Generate database loading SQL scripts to populate database table; This way we can skip step 3. If using step 3. we will put some initial loading logic in ApplicaitonModuleImpl prepareSesion() method to do so. And such initial loading of 8000 records will take about 45 seconds when using Entity Object backed View Object in Forward Only mode. Why do we need to bear with such time taken loading? We will be better off populating the database table and making sure such table will be synchronized every once or twice a year when government updates contributor class list.

  That's it, by doing the above the performance issue was addressed nicely. The waiting time in production was reduced from around 10 minutes to a couple of seconds.

  There might be other approaches, like change transient view object searching mode so that executeQuery() won't clear view object cache. I might try such approach in the future. But it never harms to use standard approach recommend by Oracle - create Entity Object and updatable View Object to take advantage of entity object cache. They are reusable business objects after all!