ADF BC: performance-issues when using View Accessors and List of Values

One of the features of ADF BC view objects is the possibility to define a model-based list of values for an attribute. This comes with a lot of functionality and can be defined in an easy way in the view object. However it appears that the default behavior of the JDeveloper and ADF has a negative effect on the performance of your application.

Suppose we have two view objects in out model-project:

• EmployeesView
• DepartmentsView

The EmployeesView has an attribute DepartmentId for which we want to define a choice-list with all deparments. To create such a list we must perform the following steps:

First we need to define a view accessor to the DepartmentsView.

fig 1 - View accessor

Second we need to define a List of values for the field DepartmentId.

fig 2 - List of Values

To show the effect of this default LOV definition I've added some logging information in the executeQueryForCollection method of the view objects base-class.

Now suppose we create a simple employees.jspx view and we drag-and-drop the EmployeesView as a table from the datacontrol-pallet to the page. The page will look something like this

fig 3 - employees.jspx

The log-info that is generated by this simple screen is as follows:

fig 4 - logging

As you can see, the executeQueryForCollection is called once for the EmployeesView, as expected, but many times for the DepartmentsView. The query to determine the choice-list values for the field DepartmentId is executed for every row in the EmployeesView. This is unnecessary and can have a negative effect on the performance of your application.

The behavior is caused by a property of the view accessor. If we take a closer look to the Property Inspector in fig. 1, we see a property 'Row Level Bind Values' with a value 'true'. The purpose of this property is to indicate that there are bind variables defined in the lookup view object that can have a different value for each row in the base view object. In out situation that is not the case. A valid example would be a list of values for the attribute ManagerId, where the list of managers is limited to the department of the employee.

Now lets turn the value of the 'Row Level Bind Values' to 'false' and run the screen again. This generates the following log-information:

fig 5 - logging with Row Level Bind Values = false

Now the executeQueryForCollection is called only once for the lookup DepartmentView. Much better!

Setting the value of the property to empty will have the same effect, as long as there are now bind variables in the lookup view object at all. If there are bind variables, an empty value will have the same effect as  the value 'true'. This seems logical, but if you assign literal values to the bind variables instead of 'row level bindings', it is still undesired.

Conclusion
The default behavior of JDeveloper when creating a view accessor is that it will set the property 'Row Level Bind Values' to 'true'. As we have seen this can have a (very) negative effect on the number of queries executed, and the performance of your application. Leaving the property blank works in many cases, but not always. My advise is to set this value explicitly to 'false' if you have no bind variables that really depend on some attribute values in the row.
The problem is that property is not visible in the create/edit window for a view accessor. Furthermore JDeveloper has it's own ideas of what the value for this property should be. Sometimes when you change something in the view object, the value for the property is changed automatically. You should be aware of this.

To deal with this issue I've created a small extension for JDeveloper that defines an auditrule that checks for the correct settings of the 'Row Level Bind Values' property. It also provides an automatically fix for the settings. I will soon make this extension available.

Remarks: I'm working with JDeveloper 11.1.1.6. I don't know the behavior of JDeveloper 11.1.2.* for this issue.