ADF BC Tuning II: Associations

Last week, I talked about tuning your ADF entity objects for maximum performance. This week, I’m going to talk about ADF associations.

Custom Association Views

One of the concepts I covered last week was an entity object’s default query, the all-columns query that would be created in a default view object for that entity object. I also talked about one place where the default query is used, even if you don’t create a default view object: entity object fault-in.

But there’s another place that this possibly inefficient query gets used, at least by default: Whenever your business logic traverses an association accessor.

Let’s suppose that you have two entity objects, Departments and Employees. Suppose, in particular, that Employees has attributes corresponding to every column in the EMPLOYEES table (that’s 15 columns total). The default query for Employees is:

SELECT Employees.EMPLOYEE_ID,
       Employees.FIRST_NAME,
       Employees.LAST_NAME,
       Employees.EMAIL,
       Employees.PHONE_NUMBER,
       Employees.HIRE_DATE,
       Employees.JOB_ID,
       Employees.SALARY,
       Employees.COMMISSION_PCT,
       Employees.MANAGER_ID,
       Employees.DEPARTMENT_ID,
       Employees.CREATED_BY,
       Employees.CREATED_DATE,
       Employees.MODIFIED_BY,
       Employees.MODIFIED_DATE
FROM EMPLOYEES Employees

Of course, view objects you base on this entity object should usually have much shorter queries; it’s rare that you would really need to query all this data.

But now, let’s consider what happens when you traverse an  association, EmpDeptFkAssoc, that associates Departments with Employees. This could happen in any number of ways, such as by calling the association’s accessor method on a Departments entity object instance, or by using a validation rule on Departments or one of its attributes that traverses the association or needs to retrieve associated entities.

EmpDeptFkAssoc has DepartmentId (from Departments) as its source attribute, and EmployeeId (from Employees) as its destination attribute. Its “association WHERE clause,” therefore, is :Bind_DepartmentId = Employees.EMPLOYEE_ID. The Departments instance’s DepartmentId value is passed into the :Bind_DepartmentId parameter, which is then appended to a query–Employees’ default query. That query gets executed to pull in any not-yet-queried rows from the database whenever you traverse the association accessor.

This is probably not what you want. Executing the full default query (with an appended WHERE clause) is massive overkill unless your business logic actually requires all the attributes from Employees. If, say, your rule is that only department 90 can contain employees with the JobId AD_VP, you *certainly* don’t need to query all data about all employees in your departments.

In this case, you can use a new 11g feature called Custom View Objects. You can, for example, create a view object called EmpJobsView, based on Employees, with the following query:

SELECT Employees.EMPLOYEE_ID,
       Employees.JOB_ID
FROM EMPLOYEES Employees

And specify EmpJobsView (on the Tuning page of an association’s editor) as EmpDeptFkAssoc’s custom view object for Employees. That way, when you traverse the association, even if new rows need to be retrieved,the only query that will be executed will be the following:

SELECT Employees.EMPLOYEE_ID,
       Employees.JOB_ID
FROM EMPLOYEES Employees
WHERE :Bind_DepartmentId = Employees.DEPARTMENT_ID

Much better.

Of course, you don’t want to get too aggressive with this. If you use a custom view object and then try to access an attribute it doesn’t populate, you’ll trigger fault-in, which, as discussed in last week’s post, is undesirable when you can avoid it.

Association Accessor Row Sets

When your application first traverses an association to a side with cardinality * or 1..*, there’s really a lot going on behind the scenes:

  • The framework creates a a query collection (essentially, a collection of rows from the entity cache) associated with both the query, WHERE clause, and parameter value (the unique identifier of the WHERE clause and parameter is called the query collection’s “filter”.
  • The framework creates a row set (essentially a pointer to a query collection plus default iterator) that uses the query collection.
  • The framework and returns the row set (typed to RowIterator)
  • When the application first attempts to access rows in the row set, The framework queries the associated rows using queries like the above.
  • The framework adds the rows to the entity cache (if they’re not already present), and adds both the previously existing and the new rows to the query collection, allowing them to be seen in the row set.

Built-in validation rules know how to process these row sets, or you can process thems yourself if you call them from inside an entity object method. But what happens to the row set when the validation rule or custom method is done with it?

By default, it’s released completely, ready for garbage collection. This doesn’t mean that the underlying entity object instances, or even the underlying query collection, disappear, but there’s no row set left that uses the query collection. When you traverse the association again, while it doesn’t need to re-retrieve the rows from the database, it still does need to find the query collection with the matching filter and create a new row set based on it.

If you only rarely traverse an association more than once from any given entity object instance, this makes sense: once you’re done processing the row set created by the association, holding it around will take up memory that you don’t need. Probably not much memory, of course–but if you retrieve association accessors from lots of individual entity object instances, it can add up.

On the other hand, if you know your application is likely to traverse the association from a given row many times (for example, they will likely repeatedly change the row during one application session, triggering validation that uses the association each time), keeping hold of the retrieved row set starts making more sense than requiring the application to construct it again and again. This shouldn’t be overstated–finding the query collection and creating a row set object is not a terribly expensive operation–but again, if it happens many, many times, it can add up. You can select the option “Retain Association Accessor Rowset” (in the Tuning section of an entity object editor’s General page) to tell instances of entity object to retain row sets created when they traverse the association.

One caution here: When a new row set is created, its “current row” pointer always points to the slot before the first row, so you can traverse its rows with code like:

RowIterator rowSet = getEmployees();
while (rowSet.hasNext()) {
    EmployeesImpl emp = rowSet.next();
    emp.doStuff();
}

You start before the first row; the first call to next() retrieves the first row, and so on until you’re pointing to the last row (when rowSet.hasNext() returns false, ending the loop).

If you’re always creating new row sets, you can always count on starting before the first row, but if you are retaining them, then the second call to getEmployees() above will retireve the same row set, which is already pointing to the last row. If you’re retaining row sets and need to cycle through them, explicitly call the method RowIterator.reset().

Association Consistency

As above, the first time you an association, data is retrieved from the database and inserted in a query collection. But what happens when you create a new entity instance that matches the filter of the query collection?

By default, whenever a new entity object instance is created, it notifies all the entity cache’s query collections, and each QC checks to see if it should be inserted based on its filter, and inserts it if so. This behavior is called “association consistency,” and in the vast majority of cases, it’s what you want. After all, if you create a new employee with DepartmentId 90, and then traverse EmpDeptFkAssoc from Department 90, you want it to show up immediately.

If it weren’t for association consistency, you’d have to post the new entity object instance to the database, and re-execute the association’s query, to see the new row. You don’t want to hit the database every time you insert a new entity object instance into the cache.

But there are a few cases–a very few cases–where you don’t need to see the new row immediately. For example, suppose your application allows users to insert a large number of employees simultaneously, and that the submit button for the page allowing the insert does a database commit. These rows are going to get posted to the database as soon as they are created anyway. So rather than notify and check every query collection each time every one of these rows is created, you might want to do something like the following:

In Departments

Create a transient, Boolean attribute, EmployeesIsDirty, with a default value of false.

In EmployeesImpl’s doDML() Method

Do the following:

protected void doDML(int operation, TransactionEvent event) {
    if (operation == DML_INSERT) {
        getDepartment().setEmployeesIsDirty(true);
    }
    super.doDML(operation, event);
}

In DepartmentsImpl’s getEmployees() Method

Do the following:

public RowIterator getEmployees() {
    RowSet employees = (RowSet) getAttributeInternal(EMPLOYEES);
    employees.setAssociationConsistent(false);
    if (getEmployeesIsDirty()) {
        employees.executeQuery();
        setEmployeesIsDirty(false);
    }
}

That call to setAssociationConsistent() keeps that row set’s query collection from being notified when a new employee is created. Instead, after the new employee is posted, the relevant department is marked as having a dirty employees accessor, and will requery its employees the next time the association is traversed. This means no posting that wouldn’t have occurred anyway, and one requery instead of many notifications.

Of course, if you’re being really clever about this, you’ll generalize this behavior and push it up into declaratively customizable framework classes.

That’s it for associations. Next week, I’ll talk about tuning your view objects.

One thought on “ADF BC Tuning II: Associations”

Leave a Reply

Your email address will not be published. Required fields are marked *