ADF BC Tuning III: View Objects, Part 1

Now that we’ve looked at tuning entity objects and associations, we’ll turn to talking about tuning your ADF view objects for good performance and memory management. There’s a lot to say about tuning view objects (more than for any other business component, in my opinion), so I’m going to break this topic up over two posts. This week, we’ll discover the reasons for and against basing read-only view objects on entity objects, learn how to control how much data is fetched into the middle tier at one time (and how to optimize this for your particular case), and talk about what passivation of view objects is and how to control whether and how much of it happens. Next week, we’ll talk about query-level range paging, forward-only mode, and the spill-to-disk feature for handling very large caches.

Before you even try to read this post, especially (but not exclusively) the first section, I strongly recommend making sure you really undertstand the relationship between view objects and entity objects: how view objects use (or do not use) underlying entity objects to cache and write data. The simplest sort of view objects, “SQL-only” view objects, do not use underlying entity objects at all. They simply read data from the database, and store that data directly in view rows held in the view object’s cache. This data can be changed in memory if you desire, but changes cannot be written back to the database.

Caching behavior in view objects based on entity objects is rather more complex. The best on-line treatment of it I’ve seen is actually in the JDeveloper 10.1.3 documentation, here. If you’re a little murky on how this relationship works, go and read that document, and then come back.

To Entity or not to Entity?

Up until JDeveloper 11gwas released, there was a simple rule about when to base view objects on entity objects and when to have them cache their own data, with no underlying entity object usages: Use entity object usages when you need to write data back to your datasource, and use SQL-only view objects when you only need to read data from the datasource. This rule got so entrenched that the JDeveloper 11gUI and documentation still regularly refers to SQL-only view objects as “read-only” view objects, and entity-based view objects as “updatable” view objects. But this is actually quite misleading.

One part is true: If you want to change the data in the cache and write that data back to the data source, you do need to use a view object with entity usages. However, in general, JDeveloper 11g generally recommends using entity usages even when you don’t want to take advantage of entity objects’ ability to update the database.

Why the change? Entity-based view objects have advantages beyond being able to write, but before 11g, the overhead of creating underlying entity object instances for each view row was considered significant enough to outweigh these advantages in a majority of cases. However, this overhead has been significantly reduced for 11g, and may never have been quite as significant as was thought. The balance now seems to be tipping in the other direction.

The particular advantage that the documentation mentions strikes me as the smaller part of it, actually. While it’s true that non-expert-mode, entity based view objects with no calculated attributes can be maintained without writing any code (whereas SQL-only view objects require writing their SQL query). But the SQL generated for such entity-based view objects is so simple that, in my opinion, having to maintain it would not create a significant addition to the maintenance effort. In addition, since you can create basic SQL-only view objects on the third page of the Business Components from Tables wizard (which you can use multiple times on a project–I often use it to prototype a single, simple VO even late in the development lifecycle; it’s quicker and easier than the VO wizard), getting a first step is often quite easy.

And I’m not entirely sure I buy the documantation’s list of exceptions. SQL-calculated attributes (I think that’s what it means instead of “SQL-validated attributes”; I don’t see what it could mean otherwise) can certainly be added to entity-based view objects or even entity objects themselves. And queries with group bys and multiple tables will benefit from having their non-grouped portion mapped to entity object instances; for example, consider a view object with the following query:

SELECT Departments.DEPARTMENT_ID,
       Departments.DEPARTMENT_NAME,
       Departments.MANAGER_ID,
       COUNT(EMPLOYEES.EMPLOYEE_ID) AS NUM_EMPLOYEES
FROM DEPARTMENTS Departments,
     EMPLOYEES
WHERE Departments.DEPARTMENT_ID = EMPLOYEES.DEPARTMENT_ID
GROUP BY Departments.DEPARTMENT_ID,
         Departments.DEPARTMENT_NAME,
         Departments.MANAGER_ID

That view object could certainly make good use of a Departments entity object, though not an Employees entity object (by contrast, there would indeed be no point in basing a view object with the SQL SELECT COUNT(*) NUM_EMPS, MAX(SALARY) MAX_SAL FROM EMPLOYEES on an entity object; that really should be SQL-only, because none of its data corresponds to particular database rows).

No, the real advantages in using entity objects, even for read-only view objects, come from how their cache works at runtime. The full list of these advantages are a discussion for another day, but from a tuning perspective:

  • View object instances based on multiple entity objects may use less memory, because the data from each table row is stored only once, even if it appears in many times in the query result.
  • Multiple view object instances based on the same entity object use less memory, because they can share entity object data.

So rather, my rule of thumb for when to use entity object usages (from a tuning perspective; if you need functionality such as writing back to the database or reference information maintenance, then of course you need to use underlying entity objects) in view objects is this:

  • If the view object
    • queries multiple tables (or multiple aliases of the same table) using a WHERE clause that expresses anything but a one-to-one relationship AND
    • more than one table/alias isn’t involved in group functions, AND
    • more than  onte table/alias contributes uncalculated columns to the query,

    OR

  • If multiple view object instances in your data model (whether or not they’re instances of distinct view objects) use the same table

then use entity object usages; otherwise, unless you have to because you need entity object functionality, don’t.

JDBC Fetch Size

When you execute a query thorugh JDBC, the result is not, by default, all brought into the middle tier at once. Instead, a few rows are brought in at a time, depending on a parameter called the JDBC fetch size. By default, JDeveloper uses a JDBC fetch size of 1, meaning only one row at a time is brought into the middle tier. You can change this by changing the value of the “in Batches of” field in the Tuning panel of the General page of a view object’s editor, as shown here:

View Object Tuning Panel

And that’s just what you want to do in the majority of cases, because a fetch size of 1 is incredibly inefficient, particularly for data you’re going to display as a table or in another multi-row format.  If, say, you’re going to use an iterator binding with a range size of 15, and you have a range size of 1, that’s 15 round-trips to the database to retrieve each range of rows. Yuck.

So, how high should you set this JDBC fetch size? Steve Muench has recommended (in a presentation which alas I can no longer find; it doesn’t seem to be on his blog) using your iterator binding’s range size + 3. I’m not quite sure how he arrived at this figure, but I trust him–if Steve says range size + 3, it’s probably range size +3. Of course, if you’re really only going to display one row ever for a particular session (for example, you set a bind parameter at session start which limits the query to one row based on the authenticated user), you can simply select the “At Most One Row” button, which will ensure that only that first row will ever be retrieved into the middle tier.

Another interesting fetch size option is to use a fetch size of 0 (which you can do by selecting the “No Rows” radio button). That will never retrieve any rows into the middle tier. This is ideal if, for example, you’re creating a form that allows people to insert rows in the database, but does not need to display, change, or delete existing rows.

Finally, there are a few places where you might want to fetch all rows at once. This brings lots of rows into memory, and creates a performance bump at query execution time, but may well be worth it for, for example, view objects that will only be used in application-scoped shared application module instances. The memory isn’t as big an issue when every user is sharing the same rows, and the bump isn’t a big problem if it happens only once (except when explicitly called) for the entire OC4J session.

Check out that “Query Optimizer Hint” field too. Pretty cool, eh?

Controlling Passivation of View Objects

At various times during a user’s session, you application module may need to be passivated. Passivating an application module involves writing some information about its state, and the state of the caches it controls (such as transactional changes) to the database so that its information can be activated into another application module instance later, effectively creating a clone.

I’d rather save talking about when application module passivation happens in the post on tuning application modules–the idea there will be to ensure that it happens fairly rarely, because it can be an expensive operation. But even if you tune an application module appropriately, it is likely to occur during peak hours for most applications, and may occur once per request for highly critical applications (where being able to handle server glitches is so important that it’s worth slowing down performance dramatically).

What sort of information goes into the database during passivation?

  1. A log of transactional changes made to entity object instances but not yet committed.
  2. Misellaneous “user data” your application or the framework has stored for the user’s session.
  3. By default, data about view object state, such as the bind variable values of all view object instances and the iterator state of all their row set iterators.
  4. Optionally, he values of all transient and SQL-calculated attributes from all view rows.

The idea here is that the rest of the application module’s state can be rebuilt by requerying data already in the database. If you don’t want 1 or 2 above, you really don’t want passivation/activation functionality; I’ll talk about how to turn it off entirely in the post on tuning application modules. But for specific view objects, you may not want #3 or #4.

Here’s an example of a case where you don’t want #3: You have a view object that is only used to display lookup data in a choice component, or to display a table with range size -1. In this case, there are no bind variables to maintain, and current iterator state just doesn’t matter. You’ll slightly speed up passivation and re-activation by not storing this info. You can turn off passivation for a particular view object (although, alas, not for a particular view object instance–you’d have to do that programmatically) by clearing the “Passivate State” checkbox on the Tuning panel shown above.

Even if you do want to passivate these aspects of view object state, you might not (indeed probably won’t) want to passivate the values of a view object’s transient and SQL-only attributes. If (like most transient attributes), they’re just used to hold calculated information–well, passivation/activation won’t keep you from having to re-execute the query anyway, so passivating information calculated in it won’t really save any time. You only generally want to passivate transient view attributes that are used to temporarily store user-entered data. (Another example–if you have a view object attribute that requires a complex stored function result for its value, you might not want to put it in the query, but instead put it in a transient attribute that lazily calls the procedure on first request. Passivating such an attribute, preventing having to re-execute the function, is almost certainly worth it.)

By default, the framework assumes that your transient and SQL-only attributes don’t need to be passivated. But if you have some that do, you can either enable passivation of all transient view attributes in a view object by selecting the “Including Transient Attributes” checkbox in the panel above, or for a particular transient or SQL-only view attribute by editing the attribute (there’s a pretty obvious “Passivate” checkbox that appears for such attributes).

So, turn off passivation for view objects when you don’t need to keep track of iterator or bind variable state, and only turn it on for transient attributes that can’t be recalculated without further user input or very expensive calculation.

That’s all for this week. Next week we’ll return with more things you can do to make your view objects into mean, lean data access machines.

5 thoughts on “ADF BC Tuning III: View Objects, Part 1”

  1. When read-only SQL-based View Objects were first introduced they struck me as being a bit of a workaround – especially since they can’t access the nice hints, renames etc that you create in an EO. Ideally for simplicity I’d prefer VOs to always be based on EOs, however as you say there is still a performance trade-off. The main exception is populating, say, an informational field on a form, where you typically have a single SQL statement (as per your MAX example above) – in that case I reckon it’s definitely better to have a read-only VO than to have a chunk of SQL buried somewhere in the java. Likewise LOVs are probably easier as separate RO VOs as they don’t usually have many columns. Anyway, I’ve been really enjoying these tuning posts Avrom – keep up the great work!

Leave a Reply

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