Continuing on from the last post, we’re looking at ways to tune ADF view objects for optimal performance and resource management.
In most applications, the user needs to be able to scroll through data both forwards and backwards–to return to a previous row in addition to simply being able to scroll forward, or to find rows an earlier view row after finding a later one. Because of this, by default, once rows from a view object’s query result are read into the view cache, they stay there. If your JDBC fetch size is 18, then initially, only 18 rows will be in the view cache, but when request rows outside the first 18, that number goes up to 36, then 54, then 72, and so on.
But sometimes, you really only need to cycle forward once through the rows in the query result. If, say, you’re displaying a read-only table, only once in a session, with no row selection ability, then, well, you really only need to retrieve each row and display it, right? Once you’ve displayed it, you’re done. Or suppose that, at the end of the application session, you want to simply make a big update of lots of rows from, and there’s no problem with updating the rows in the order in which the query returned them.
In either of these cases, the view cache is just taking up space. This isn’t terribly significant when the query returns, say, 72 rows, but it can start to get quite significant indeed when the query starts returning results in the 1000-or-so range, especially if the user or application is actually likely to retrieve the last one (and therefore the rest of them–otherwise just setting the JDBC fetch size appropriately may keep things small enough) at some point. And if your cache size is growing to tens of thousands of rows, or more, per user, well, that’s bad news.
If your application never needs to scroll backwards through a particular view object’s result set, you can use the view object in forward-only mode, either by calling setForwardOnly(true) on a particular view object instance, or by declaratively setting all instances of a view object to use forward-only mode by selecting “Forward Only” from the Access Mode dropdown on the Tuning panel of the General page of a view objects editor, shown next (with the default “Scrollable” option selected):
When a view object instance is in forward-only mode, as soon as one of its iterators scrolls off of a view row, that view row is dumped from the cache. So if, say, your JDBC fetch size is 18, you’ll never have more than 18 rows in the cache–if your application requests row 18, the first 17 rows will be dropped, and if it requests row 19, row 18 will be dropped before the next 18 rows are fetched in.
If your query result is large, and the user may scroll down to the end of it, and if forward-only navigation is sufficient for your purposes, forward-only mode can save you a lot of memory. Of course, sometimes a query result is large when forward-only mode just won’t work, or is so large that simply executing the query is a serious performance hit, regardless of how many or few rows are brought into the middle tier. What do you do then? Use range paging.
“Oh, never mind this,” you say, “I’m already using ranges in my applications.” That’s probably true, but you’re probably only using ranges at the ADF Model level (in your iterator bindings). What most people don’t know is that you can use ranges at the view object level as well–and it’s actually pretty different.
ADF Model ranges control how many rows are requested by the view or controller at once (which may have an effect on how many rows the middle tier needs to fetch from the database for each request). JDBC fetch size controls how many rows are retrieved into the middle tier at a time. But neither of these control how many rows are actually queried within the database at one time, and neither control how many rows are stored in the view object cache once retrieved (forward-only mode can do that, but only if forward-only navigation is sufficient). View object range paging can.
View object range paging, documented here, uses WHERE ROWNUM… clauses to restrict the rows actually returned by the query at any given time, and only stores a fixed number of view rows in memory at once, so even scrolling backwards to rows already retrieved will trigger a requery. Increasing the number of queries you have to execute is a pretty serious increase in overhead, so you should use view object paging with this in mind–you’re deliberately introducing the possibility of more queries in excahnge for a shorter query up front and less rows in memory at once. I wouldn’t usually recommend using view object range paging with queries that will never retrieve more than 100 rows, and I’d actually do comparative stress tests before committing to this path unless my queries were retrieving well into the four figures of rows (at the other extreme, if you’re going to allow queries returning tens of thousands of rows, range paging is more or less a must). In general, setting the JDBC fetch size appropriately is the first thing you should try, not this.
ADF offers three flavors of range paging: Range Paging, Range Paging Incremental, and Range Paging Auto-Post. All are available from the “Access Mode” dropdown of the tuning panel, on the General page of a view object’s editor, and allow you to set a “range size” in the Range Size text field.
Ordinary vs. Incremental Range Paging
In ordinary Range Paging mode, the range size specifies both how many rows are queried at a time, and how many view rows are held in the cache at one time. For example, if you have a view object in ordinary Range Paging mode, with a range size of 36, and a user executes the view object’s query, only the first 36 rows from the database will be queried and added into the caches. If the user then requests row 37, the first 36 view rows will be discarded from the view cache (changed, new, or deleted underlying entity object instances will still hang around in the entity cache, so you won’t lose transactional changes), and the next 36 will be queried and cached. If the user then wants to scroll back to row 36, the second 36 rows will be discarded, and the first 36 will need to be re-queried and re-cached. That’s better than forward-only mode, of course, because you can go backwards. But if you’re doing lots of paging back and forth across “pages” of rows, it can bog you down quite significantly.
In Range Paging Incremental mode, the range size specifies how many rows are queried at one time, but more view rows than that may be held in memory once queried. The total number of rows that can be held in memory is the range size times a number called the “range paging cache factor.” For example, if you have a view object in Range Paging Incremental mode, with a range size of 36 and a range paging cache factor of 5, the initial query will only retrieve 36 results, ad above. But when row 37 is requested, the first 36 rows are not discarded; the cache simply now holds 72 rows total. The first 36 rows will only be discarded when row 121 (36 * 5 + 1) or higher is requested, to make way for the sixth set of 36 rows. This allows you to trade off between a smaller cache size and fewer requeries when scrolling backwards (or re-scrolling forwards) through the rowset. (Thanks to Steve Muench for helping me understand this.)
Ordinary vs. Auto-Post Range Paging
With ordinary or incremental range paging, you have to be careful with unposted deletes and inserts. Suppose the user is working on the first 36 rows, and adds five new ones. Then, they ask to see rows 37-72. A second-36 query will retrieve rows 37-72 in the database, but the application and state of the UI would think of these rows as rows 42-77. That’s a bit funky. Worse, once the cache segment containing the unposted rows is dropped, there’s no way to get it back via querying. The entity object instances are still around, so you’ll be able to post them, but users won’t be able to see them any more from the client side of your app.
In Range Paging Auto-Post mode, your view object will post all changes to the database before retrieving a new “page” of rows. It won’t, unfortunately, commit those changes–and having posted, uncommitted changes hanging out between requests in a web application is a bad idea, for a number of reasons. So unfortunately, if you use Range Paging Auto-Post mode, you’ll need to ensure that every time the range scrolls, the transaction is subsequently committed. The most elegant way to do this is to register a RowSetListener (possibly the application module, if you modify that to implement RowSetListener) that commits the transaction whenever its rangeScrolled() method is called, but it’s probably easier to just implement this in your task flow, unless you’re going to be using Range Paging Auto-Post constantly.
Declarative View Objects and As-Needed Attribute Querying
If you’ve been exploring JDeveloper 11g, you’ve probably discovered declarative view object query mode, which allows you to specify entity objects, attributes, and query criteria on which to base your view object, which then constructs its query dynamically, at runtime. I think declarative view object query mode is great–for those queries that are simple enough to work with it, for a variety of reasons, including ease of maintenance and the inherently structured quality of declarative mode metadata. But there can be a performance reason to use them as well.
When a declarative-mode view object’s query is executed because of a request from an ADFm iterator, its SELECT clause is not, by default, constructed from all the attributes in the view object. Instead, it is constructed from only the attribute values that figure in bindings (be they list, boolean, tree, attribute, or graph bindings) that depend on the iterator.
That’s neat and efficient, when you want it. Of course, it’s not so great if your app needs access (say, for validation, calculation, or programmatic value changes) to attributes that don’t get rendered on the UI. They’re never going to get queried as part of the view object’s execution, so when they’re requested, they’re going to be faulted in at the entity level. You don’t want that to happen too often.
So, what do you do if you want to use declarative view object instances for their convenience and elegance, but also need to work with non-displayed attributes? You turn this special as-needed attribute querying off, by selecting the “Include All Attributes in Runtime-Generated Query” option in the Tuning panel of the General page of a view object’s editor. (You won’t see it above, because it only appears for declarative-mode view objects.) That way, the view object’s SELECT clause will always contain the columns needed to populate all the view object’s attributes, just like a normal-mode view object’s (static) SELECT clause does.
Next week: View links without the slowdown.