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!

An implementation on automatic data push in ADF using Active Data Service (ADS)

  From time to time when we develop applications, our application is not running on its own. Chances are the application is always interacting with other applications. 

  Let's assume you have this requirement for your application: your application has a page to display data from a database table, but another application will update the same table from outside of your application's context. What is the best approach to synchronize your application with the data that are modified from another application?

  There are the following ways to achieve such requirement:

    1) Your application keeps waiting on possible data change; this is not recommended, because your application acts like it is blocked, it won't respond to other requests. Use this approach if you have to. Like your application has to wait for certain change to proceed. For example your application is a payment gateway, it redirects your end user to a bank page for the end user to do some online transaction. Your application has to wait for the end user to finish and process the bank transaction response in your application.

    2) Your application add a 'Refresh' button onto your page, ask your end user to click on this refresh button to refresh data update onto your screen; using this approach the user experience is largely relying on if end user is OK to keep pressing button to refresh application data.

    3) Your application initialize a thread to poll the database table at certain frequency ( for example poll database table every 30 seconds ); this approach will save end user from keep clicking 'Refresh' button, but the down size is your application might have to refresh page each time the poll happens. Some user might complain that your page flashes or flickers. Another big drawback is polling database may cause database performance issue.

  4) Your application continues after displaying data; you will still have some logic after the data changes are pushed to your application and your application can process such data changes at the back ground while still servicing other requests.

  As you can see approach 4) above using automatic data push gives your application more flexibility in a cleaner way. Every other approach might be OK in a case by case scenario. 

  Fortunately there is a simple approach to achieve auto data push in ADF using Oracle database and ADF Active Data Service - ADS technology. The following are the steps to implement this from scratch.

Grant 'change notification' to your database user

  In my sample application I am using scott database user, log into your database and issue the following SQL command:

    grant change notification to scott

  You need to grant such permission as sys or system or any user having DBA privilege.

Code your database change listener

  Your ADF code must have a database change listener registered to listen on the backend database table. The following code snippet is an example:

    private void startListenForDBChanges() throws Exception {
        OracleConnection conn = connect();
        Properties prop = new Properties();
        prop.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "true");
        dcr = conn.registerDatabaseChangeNotification(prop);

        try {
            dbChangeListener = (new DatabaseChangeListener() {
                    public void onDatabaseChangeNotification(DatabaseChangeEvent dce) {
                        String rowId =
                            dce.getTableChangeDescription()[0].getRowChangeDescription()[0].getRowid().stringValue();
                        System.out.println("Changed row id : " + rowId);
                        String changeDetail = formChangeDetail(rowId);
                        listener.onDBChangeListener(changeDetail);
                    }
                });

            dcr.addListener(dbChangeListener);

            System.err.println(dcr + " " + dbChangeListener);

            Statement stmt = conn.createStatement();
            ((OracleStatement)stmt).setDatabaseChangeRegistration(dcr);

            ResultSet rs =
                stmt.executeQuery("SELECT ename, job, sal, comm FROM bonus");

            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException ex) {
            if (conn != null) {
                conn.unregisterDatabaseChangeNotification(dcr);
                conn.close();
            }
            throw ex;
        }
    }

  In the above code, the "SELECT ename, job, sal, comm FROM bonus" is to let your code listen on ename, job, sal and comm columns of bonus table. Any changes related to thse 4 columns will be intercepted by your database change listener.

Construct your change notification message

  Your can have a method to construct the change notification message that might be displayed onto your application page.

    private String formChangeDetail(String rowId) {
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String ename = "";
        String job = "";
        BigDecimal sal = null;
        BigDecimal comm = null;
        OracleConnection connection = connect();
        try {
            stmt = connection.prepareStatement("SELECT ename, job, sal, comm FROM bonus WHERE ROWID=?");
            stmt.setString(1, rowId);
            rs = stmt.executeQuery();
            while (rs.next()) {
                ename = rs.getString(1);
                job = rs.getString(2);
                sal = rs.getBigDecimal(3);
                comm = rs.getBigDecimal(4);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs.close();
                stmt.close();
                connection.close();
            } catch (Exception ex) {
                ex.printStackTrace();
                System.err.println("Cannot close connection.");
            }
        }
        return "Emp name: " + ename + " job: " + job + " salary: " + sal + " commission: " + comm + ".";
    } 

 Code your AdsController class

  Your AdsController java class should have this declaration:

    public class AdsController extends BaseActiveDataModel implements DBChangeListener {}

  You will register your AdsController class in your application same as you do as backing bean class.

  

  Add active model statement in your AdsController constructor method:

    public AdsController() {
        ActiveModelContext vActiveModelContext =
            ActiveModelContext.getActiveModelContext();
        Object[] vKeyPath = new String[0];
        vActiveModelContext.addActiveModelInfo(this, vKeyPath, "Status");
    }

  Important: "Status" is the name of your change notification event name. When I was first implemented ADS, I was really confused by what the 3rd parameter means in addActiveModelInfo(), it is just a name. You should use such name consistently in your application. Later in triggerActiveDataUpdateEvent() we will use this same name again. You need to make sure they are the same.

Override startActiveData method in AdsController

    protected void startActiveData(Collection<Object> coll, int i) {
        System.err.println("Starting active data thread for: " + this);

        dbPushController = new DBPushController();
        try {
            dbPushController.addChangeListener(this);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

  In the startActiveData method, you add change listener into your ADS controller class.

  

Override onDBChangeListener method in AdsController

    public void onDBChangeListener(String changeDetail) {
        triggerActiveDataUpdateEvent(changeDetail);
    }

...

    public void triggerActiveDataUpdateEvent(String changeDetail) {
        counter.incrementAndGet();

        System.err.println("Trigger active data update event: " +
                           counter.get() + " " + this);
        ActiveDataUpdateEvent event =
            ActiveDataEventUtil.buildActiveDataUpdateEvent(ActiveDataEntry.ChangeType.UPDATE,
                                                           counter.get(),
                                                           new String[0], null,
                                                           new String[] { "Status" },
                                                           new Object[] { changeDetail });
        fireActiveDataUpdate(event);
        j++;
    }  

  Since we declared AdsController as implement DBChangeListener, we have to implement onDBChangeListener() method, in it we fire the active data update event. Notice "Status" in bold in triggerActiveDataUpdateEvent() method, it is the same data change name we used in AdsController constructor method.

  That's pretty much it. You need to define a connection in your ADF application model project. I am using scott and bonus table for this sample.

  Please see the screenshot of running this application below.

When application first runs, no data in scott.bonus table yet

Now add a record for SMITH and give SMITH 1.5% commission

Now modify SMITH's commission from 1.5% to 2%

At last we add 5% commission to Jones

  Congratulations! You now know all it about to implement Oracle ADF automatic data push via ADS and database change listener! 
  Please use the following link to download the source code of this sample:
Data Push Sample Source Code