Tuning Your View Objects
By Steve Muench
What you don't know can hurt your application's performance.
A view object is the Oracle Application Developer Framework (ADF) component that lets you work easily with SQL query results. View objects can be used to read rows of data; to create and hold rows of transient data; and to automatically insert, update, and delete changes made in your underlying business objects. The way you design and use your Oracle ADF view objects, however, can significantly affect their performance at runtime. In this column, I'll explore some design tips to improve the performance of your Oracle ADF applications.
When to Use Entities
View objects do not have to be related to underlying entity objects. When a view object is related to one or more entity objects, the changes you make to its rows of data are transactional. The view object coordinates with its underlying entity objects to enforce business rules and to save modifications to data permanently. Entity-based view objects have several other special characteristics:
View objects that are not related to any entity object are read-only. Unlike entity-based view objects, they do not pick up entity-derived default values, do not reflect pending changes to entity object attributes, and do not reflect updated reference information when you modify foreign key attribute values.
If parts of your application do not need the functionality provided by entity-based view objects, you can improve performance by using a read-only view object instead. For example, view objects that perform SQL-based validation or display a list of valid selections are good candidates to be read-only.
Tuning Your Queries
The next place to look for performance improvements is in the query itself. The Explain Plan button on the Query panel of the view object editor lets you see the query plan that the database will use when executing a query. If you see that a query is doing a full table scan, consider adding indexes or providing a value for the Query Optimizer Hint field on the Tuning panel of the view object editor to explicitly control which query plan to use. When using master/detail queries with Oracle ADF view links, test the detail query for the current master by using the Explain Plan button on the View Link SQL panel of the view link editor.
Using bind variables can also improve performance. A properly tuned SQL statement that uses bind variables correctly can be reused by the database without the need to reparse the query each time. This means that if you build a query at design time that contains a WHERE clause containing a bind variable, such as
part_id = :0 /* "colon zero" */
you can reuse the query 10 times by writing
vo.setWhereClauseParam(0, id1); vo.executeQuery(); vo.setWhereClauseParam(0, id10); vo.executeQuery();
This approach leads to better performance than when you change the WHERE clause each time, using literal strings, as in
/* NOT RECOMMENDED!! */ vo.setWhereClause("part_id = " + id1); vo.executeQuery(); vo.setWhereClause("part_id = " + id10); vo.executeQuery();
This code causes the SQL statement to change each time, forcing the database to reparse the query from scratch for each execution. If you need to change the query at runtime, you can still use bind variables in your WHERE clause and set their values by using this code:
/* USE BIND VARS WHERE SENSIBLE */ vo.setWhereClause("part_id = :0"); vo.setWhereClauseParam(0, id1); vo.executeQuery();
Setting Your Tuning Options
The Tuning panel of the view object editor lets you set options that can dramatically affect your query's performance. The Retrieve from the Database section controls how Oracle ADF will retrieve rows from the database server. The options for the fetch mode are All Rows , At Most One Row , and No Rows . Most view objects will stick with the default All Rows option, which will be retrieved "as needed" or "all at once," depending on the option you choose. For view objects in which the WHERE clause expects to retrieve a single row, set the option to At Most One Row for best performance. This way the framework knows not to expect any more rows, so it will skip its normal test for that situation. Also, if you use the view object only to create new rows, set the option to No Rows , so that no query will ever be performed.
The fetch size controls how many rows will be returned in each round-trip to and from the database. By default, the framework fetches rows in batches of one row at a time. If you will normally fetch more than one row, you can set the in Batches of value to gain performance efficiencies. As you increase this value, however, you also increase the client-side buffer requirements, so you shouldn't just set this number arbitrarily high. If you are displaying results n rows at a time in the user interface, it makes sense to set the fetch size to at least n + 1, so that each page of results can be retrieved in a single round-trip to and from the database.
Creating View Objects at Design Time
As a final thought, it's important to understand the overhead associated with creating view objects at runtime (as opposed to design time). In general, avoid this unless you have a compelling business requirement. If your application issues a query against a table whose name you know at design time and the list of columns to retrieve is fixed, you should create the view object at design time. If you do so, your SQL statements will be neatly encapsulated, they will be easily tunable during development, and they will incur no runtime overhead to discover the structure and datatypes of the resulting rows.
In contrast, when you use the createViewObjectFromQueryStmt() API in the ApplicationModule interface at runtime, your queries will be buried in code, they will be more difficult to tune, and they will incur a performance penalty each time the view object is created. Because the dynamic query can theoretically be different for each execution, each query will require an extra database round-trip to discover the "shape" of the query results on the fly. For best performance, create queries dynamically only if you will not know the name of the table to query until runtime. You can meet most other requirements by using a view object created at design time, in combination with runtime APIs to either set bind variables in a fixed WHERE clause or, alternatively, to add an extra WHERE clause with optional bind variables.
With these additional performance tips in your toolbox, you're ready to make sure your first (or next!) Oracle ADF application interacts with the database in the most efficient way possible.
Steve Muench is a consulting product manager for Oracle JDeveloper and an Oracle ACE. In his more than 16 years at Oracle, he has developed and supported Oracle tools and XML technologies and continues to evangelize them. Muench shares tips and tricks on OTN and in his Dive into BC4J and ADF Weblog .