View Object Tuning Tips for Best Performance

Contents

Overview

A view object is the ADF framework component that lets you work easily with SQL query results. View objects can be used for reading rows of data, creating and storing rows of transient data, as well as automatically coordinating inserts, updates, and deletes made by clients with your underlying business objects. How you design and use your view objects can affect their performance at runtime. This article explains how the view object relates to other key ADF Business Components, covers some basic concepts about ViewObjects, RowSets, and RowSetIterators, then proceeds to offer a number of tips to get the best performance with your ViewObjects.

UML Diagram of Key ADF Business Components Client Interfaces

Figure 1 is a little UML diagram of the most common interfaces that developers use for working with data using the ADF Business Components features of the Oracle ADF framework. These are all interfaces in the oracle.jbo package. The reason they are interfaces is that applications built using the ADF Business Components are always logically three-tier in architecture, but the developer can choose to deploy them in a physical two-tier or physical three-tier deployment (at any time!). By sticking to interfaces, the framework can implement the interfaces for you in different ways depending on whether you choose the 2-tier or 3-tier deployment, and your code doesn't have to change when you repartition the client layer from the middle-tier service layer. For a web application, the client might be a set of JSP pages. You can choose to deploy the service layer as a set of simple Java classes in the web tier, or alternative choose to deploy it as an EJB Session Bean (with either local or remote interfaces) in the EJB container.

Figure 1: UML Diagram of Most Common ADF Business Component Interfaces

Understanding a ViewObject's Default RowSet and RowSetIterator

You can see in the diagram above that a ViewObject defines the query that produces RowSets when that query is executed. It also defines the metadata describing the "shape" of a row in the result set of that query (e.g. names and datatypes of each column). A View Object can be used to produce multiple RowSet's.

Also, for any given RowSet you might want to be iterating through the rows using one or more independent "current record pointers". Indeed, any RowSet can have multiple iterators to handle this as shown in Figure 2.

In practice though, in most scenarios you will use a View Object to define a SQL query and you'll only really need to work with one "main" RowSet of results from that query at a time. You might parameterize the query using bind variables, and then set some new values for those bind variables and re-execute, but fundamentally you're just working with one main RowSet of that queries results, not many. To cater to this common usecase, the ViewObject extends the RowSet interface and it implements the RowSet methods (like executeQuery, and setWhereClauseParam for example) by delegating to a "default" RowSet instance that the view object aggregates and manages for itself.

Analogously, while multiple iterators on a RowSet is a nice feature when you need it, most of the time you don't need it. So, the RowSet interface extends the RowSetIterator interface and it implements the RowSetIterator methods (like next, previous, insertRow, etc.) by delegating to a "default" RowSetIterator instance that the RowSet aggregates and manages for itself.

This let's you conveniently work with a view object, set it's where clause parameters, execute it's query, and iterate through the rows in the default rowset all using the single, handy ViewObject interface for the most common cases.

Figure 2: A View Object's Default RowSet and its Default Iterator

Performance Tuning Tips for View Objects

Use Non-Entity-Mapped View Objects If You Don't Need Entity-Related Functionality

View objects can either be related to underlying entity objects, or not. When a view object is related to one or more underlying entity objects, there are a number of additional features that it supports. Specifically, rows in any rowset (including its default rowset, explained above):

1. Are modifiable, and changes are automatically coordinated with underlying entity objects for business logic enforcement and persistent storage of the changes made.
2. Immediately reflect pending changes made to relevant entity object attribute made through other view objects in the current application module.
3. Immediately reflect pending changes made to relevant entity object attribute made through other view objects in the current application module.
4. Pickup entity-derived attribute default values in newly created rows

In contrast, view objects that are not related to any entity object:

1. Are read-only
2. Do not reflect any pending changes made through other view objects in the current application module
3. Store all attributes in the view row cache
4. Don't have any entity-related default values in new rows.

You need to decide what kind of functionality your application requires and design the view object accordingly. There is a small amount of runtime overhead associated with the coordination between view object rows and entity object rows, so if you don't need any of the functionality offered by the "entity-mapped view object", you can slightly increase performance by using an expert-mode view object with no related entity objects.

Since it's not so obvious how to create an expert-mode view object with no related entity objects, it's worth mentioning the steps required here:

1. Create a new view object using the view object wizard, and give the new view object a name
2. Press (Next>) until you get to the Query panel of the wizard
3. Type in your SQL query into the query box
4. Click (Finish)

In the process, you skipped the panels where you would have normally selected the entity objects involved in the query, and the panel where you would have selected which entity object attributes to include. When you create an expert-mode view object that is not related to any entity object, all of its view object row attributes are SQL-derived, and the attribute names are inferred from the column names in the query you typed in. You can use SQL column aliasing in the query to alias a column to a NAME_LIKE_THIS, which will get converted into a more Java-friendly NameLikeThis.

Cache Rows Only When Your Application Requires It

All view objects, by default, support bidirectional iteration (i.e. next(), previous(), first(), last() methods, etc.). To support backwards iteration and the ability to move back to the first row again, the ADF View Object caches the view object rows.

Of course, caching rows uses more memory than not caching rows. So, if you only need to read rows from a view object in a forward-only fashion, setting your iterator to use forward-only mode will avoid view object row caching for your rowset. The setForwardOnly(true) method call on your rowset's iterator (including its default iterator, explained above) will avoid view row caching for that rowset.

If you iterate over the rowset of results from a view object mapped to entity objects, as mentioned above, the storage of the entity-mapped attributes is still delegated to the entity cache, but no view rows are cached so the partially populated entity objects in the entity object cache can be garbage collected whenever memory is needed by the VM. Partially populated entity objects are objects in the entity cache that contain the primary key and any other attributes that were read in by the view object. Other attributes of the entity object that were not read in as part of the query are not loaded into memory.

If you iterate in forward-only mode over the rowset of results from a view object that is not mapped to entity objects, then only the attribute values of the current row are in memory. Putting the information in this section and the previous section together, a corollary follows that the least-functional, highest-performance view objects are expert-mode view objects that are not mapped to entities, which are iterated using forward-only mode.

Tuning Your Query

After deciding whether your view object should be mapped to entities or not, your attention should turn to the query itself. The (Explain Plan) button on the Query panel of the view object editor allows you to see the query plan that the database query optimizer will use. If you see that it is doing a full table scan, you should consider adding indexes or providing a value for the Optimizer Hint field on the Tuning panel of the view object editor to explicitly control which query plan will be used.

A properly tuned SQL statement that uses bind variables correctly can be reused by the database without having to reparse the query each time. In simple terms, this means that if you use a query with a WHERE clause like:

part_id = ?

and reuse it 10 different times by doing:

yourVO.setWhereClauseParam(0, numberValueOne);
yourVO.executeQuery();
   :
yourVO.setWhereClauseParam(0, numberValueTen);
yourVO.executeQuery();

This approach is much preferable to changing the WHERE clause each time using literal strings like this:

/* THIS IS NOT A RECOMMENDED APPROACH */
yourVO.setWhereClause("part_id = "+numberValueOne);
yourVO.executeQuery();
   :
/* THIS IS NOT A RECOMMENDED APPROACH */
yourVO.setWhereClause("part_id = "+numberValueTen);
yourVO.executeQuery();

This causes the SQL statement to change each time, and requires the database to reparse the query from scratch on each execution. Of course, if you must change the query at runtime, you can change it to a WHERE clause that uses bind variables, and then set the values of the bind variables like this:

/* IF YOU MUST CHANGE RUNTIME WHERE CLAUSE, USE BIND VARS WHERE SENSIBLE */
yourVO.setWhereClause("part_id = ?"); /* NOTE: JDBC-style bind variable  */
yourVO.setWhereClauseParam(0,numberValueOne);
yourVO.executeQuery();
   :
/* IF YOU MUST CHANGE RUNTIME WHERE CLAUSE, USE BIND VARS WHERE SENSIBLE */
yourVO.setWhereClause("warehouse_id = ?"); /* NOTE: JDBC-style bind variable */
yourVO.setWhereClauseParam(0,numberValueTen);
yourVO.executeQuery();

Use Oracle-Style Bind Variables and Data Types When You Can

When using the Oracle JDBC driver, Oracle-style bind variables are faster than JDBC-style bind variables. Oracle-style bind variables look like this:

part_id = :1

instead of using the JDBC-style question mark (?) bind variable indicator. The reason that using Oracle-style bind variables is faster is that it avoids the conversion from JDBC-style to Oracle-style which the Oracle JDBC driver does as the first step in parsing a query statement.

By default, view objects use Oracle-style bind variables when your project is setup to use the Oracle "SQL Flavor". You can decide which bind variable style you like by checking or unchecking the Use ? Style Parameters checkbox on the Query panel of the view object editor.

Prefer Oracle Domain Types to Native Java Types for Number and Date

Using attribute types like Number and Date in the oracle.jbo.domain package offer better performance than other native Java number or date types. This is because these datatypes are designed to keep information retrieved from the database in their native, internal format to avoid unnecessary datatype conversions. When reverse-engineering entity objects from database tables, these are the default types used by the ADF Business Components design time wizards.

Allow View Objects to Reuse JDBC Prepared Statements

The Pooling and Scalability tab of the Configuration Editor allows you to set parameters related to the application module pool. By leaving the Disconnect Application Module Upon Managed Release checkbox unchecked, ADF will reuse prepared statements across stateful releases of the same application module for the same session. This is equivalent to setting the value of false for the jbo.doconnectionpooling configuration property. The default of this property is false, so by default the application module instances are pooled, but not the JDBC connections as well. If you do connection pooling in addition to application module pooling, then the JDBC PreparedStatement objects that get created to execute your view objects' queries cannot be reused because they become invalid when their connection is "pulled out" from under them.

By unchecking both the Reset Non-Transactional State Upon Unmanaged Release and the Support Dynamic JDBC Credentials checkboxes on this panel, you allow ADF to reuse prepared statements across multiple different clients. This is equivalent to setting the value of false for the jbo.ampool.resetnontransactionalstate and the jbo.ampool.dynamicjdbccredentials configuration properties. The default of these properties is true, so by default prepared statements are cleaned up when one client session checks the application module instance back into the pool in stateless mode.

Set Your Fetch Options Correctly

The Fetch Mode controls whether the View Object will eagerly fetch all rows from the RowSet. By default, view objects fetch rows only as they are needed. You can set the fetch mode to All at Once. If you want them fetched all at once.

The Fetch Size controls how many rows at a time will be returned from the database in each round trip. The default is 1. If you are fetching any more than one row, you will gain efficiency by setting this number higher. The higher the number, the larger the client-side buffer the JDBC driver needs to allocate, so don't just set this number arbitrarily.

The Maximum Fetch Size limits the number of rows that your view object will fetch from the database. If your query will never return more than one row, it is to your advantage to set its Maximum Fetch Size to 1.

Avoid Spill-to-Disk Functionality If You Don't Require It

One of the most frequent performance-related questions we get on the Oracle Technet discussion forum is a question like, "After I query about a 1000 rows in a view object, my application gets very, very slow. What's happening?"

For example, imagine a view object that queried a table containing all Oracle employees. If a user entered no query criteria (and the application did not add or enforce any of its own), then the user would begin to page through about 42,000 rows of results. If the user asks to see the "last" row in the result set, quickly the amount of rows being cached crosses a threshold that causes the "view row spill to disk" functionality to kick in. It's clear that caching rows by doing round-trips to the database is much, much slower than caching them in memory, so if your app is frequently causing this feature to kick in, you probably go need to study the application to see why it is caching so many rows. Perhaps you don't even mean to be caching them and can use tips mentioned earlier in this article to read the rows in a Forward-Only way. This article explains how this mechanism works, and how to configure or disable it.

The new Range Paging feature in JDeveloper 9.0.4 will help developers who really do have to allow users to page through this many rows by only ever caching one page-sized range of rows at a time, letting the database return us only the rows needed to show on that page. Another thought is that perhaps you should design your application to prevent the user from doing unbounded queries over tables with thousands and thousands of rows. The application could enforce that at least some (hopefully indexed!) query criteria should be provided.

Avoid Unnecessary Runtime Creation of View Objects

As a last thought about performance and view objects, for some reason many developers tend to want to do things dynamically even when there is no business requirement to do so. For example, if your application issues a query against a well-known table, then you should design the query as a view object at design time instead of using the createViewObjectFromQueryStmt() API at runtime overhead. When you design the view object, your SQL statements are neatly encapsulated, can be easily performance tuned during development, and the overhead of describing the query happens during designtime so you don't pay any runtime overhead for this. When you use the createViewObjectFromQueryStmt() API, your query is buried in code, it's more complicated to proactively tune your SQL, and you incur runtime overhead each time the statement is executed to describe the "shape" of the query results (since the query statement could theoretically change on each invocation). Only create queries dynamically if you must discover the table to be queried at runtime. Most other requirements can be solved using a design-time created view object with a mostly-static SQL statement, in combination with runtime API's to set bind variables, or alter a WHERE clause if you cannot make use of a fixed set of bind variables.