Tuning View Object Performance by Adjusting the Row Fetch Size

While you are editing a view object in the View Object Editor, you can specify fetch size parameters in the Tuning page to tune the performance of your program. The fetch size functionality is based on JDBC and affects how the business logic tier fetches data from the database. The fetch size parameters are stored in the view object XML file.

You can override the XML parameters in client code, or in view object code in the business logic tier. The oracle.jbo.server.ViewObjectImpl interface methods that deal with fetch size are: setFetchMode, getFetchMode, getFetchSize, setFetchSize, setMaxFetchSize, and getMaxFetchSize. You can use them on the business logic tier only. In addition, you can use setMaxFetchSize (in the ViewObject interface) from both the business logic tier and client code. See the Javadoc for more information. For an example, see the Batch Client Tutorial.

Here are the parameters you can specify in the Tuning page:

Field

Description

Fetch Mode

Select As needed to get data from the database as it is used (the default). Select All at once to get all data at once. The fetch mode controls how rows are retrieved out of the JDBC result set. After a SQL query executes, the business logic tier retrieves row data from the result set.

If you specify As needed, the result set is left open and rows are retrieved as the user navigates through the row set. If the user reaches the end of the row set, the result set is closed. This option is useful, for example, if rows are large and memory resources are low.

If you specify All at once, all rows are retrieved out of the result set, even if the user has not navigated through the row set. After all rows are retrieved, the result set is closed. This option is useful, for example, if rows are small and memory resources are not low.

When you specify All at once, you incur the data fetching "penalty" all at once. When you specify As needed, your incur the penalty incrementally.

You may want to use All at once if the number of rows in the row set is relatively small and the number of row sets open on the view object is large. This can avoid using up the JDBC result sets. If As needed is specified and too many row sets are open (and left open because they are not navigated to the end), the user may run out of result sets and get a DMLException exception.(You can set the jbo.max.cursors runtime parameter to a higher number, if needed.) On the other hand, if the result set contains many rows, All at once may not be preferred because it incurs the cost of retrieving all rows up front.

Fetch Size

Specify the number of rows to fetch at a time when the fetch mode is As needed. (This value is ignored when the fetch mode is All at once.) You can specify a number greater than zero; the default is 1.

Maximum Fetch Size

Specify the maximum number of rows to fetch. For example, if the query result set was 2000 rows, and you specified 1000 rows as the maximum fetch size, the business logic tier will only fetch 1000 rows total, and close the result set. You can specify a number -1 or greater. The default is -1 meaning fetch all rows. Zero (0) means fetch no rows in the result set. Note that this setting does not effect any JDBC statements, but is an indication to the business logic tier to stop after a certain number of rows.

Related topics
Editing a View Object

What is a View Object?

Field	Description
Fetch Mode	Select As needed to get data from the database as it is used (the default). Select All at once to get all data at once. The fetch mode controls how rows are retrieved out of the JDBC result set. After a SQL query executes, the business logic tier retrieves row data from the result set. If you specify As needed, the result set is left open and rows are retrieved as the user navigates through the row set. If the user reaches the end of the row set, the result set is closed. This option is useful, for example, if rows are large and memory resources are low. If you specify All at once, all rows are retrieved out of the result set, even if the user has not navigated through the row set. After all rows are retrieved, the result set is closed. This option is useful, for example, if rows are small and memory resources are not low. When you specify All at once, you incur the data fetching "penalty" all at once. When you specify As needed, your incur the penalty incrementally. You may want to use All at once if the number of rows in the row set is relatively small and the number of row sets open on the view object is large. This can avoid using up the JDBC result sets. If As needed is specified and too many row sets are open (and left open because they are not navigated to the end), the user may run out of result sets and get a DMLException exception.(You can set the jbo.max.cursors runtime parameter to a higher number, if needed.) On the other hand, if the result set contains many rows, All at once may not be preferred because it incurs the cost of retrieving all rows up front.
Fetch Size	Specify the number of rows to fetch at a time when the fetch mode is As needed. (This value is ignored when the fetch mode is All at once.) You can specify a number greater than zero; the default is 1.
Maximum Fetch Size	Specify the maximum number of rows to fetch. For example, if the query result set was 2000 rows, and you specified 1000 rows as the maximum fetch size, the business logic tier will only fetch 1000 rows total, and close the result set. You can specify a number -1 or greater. The default is -1 meaning fetch all rows. Zero (0) means fetch no rows in the result set. Note that this setting does not effect any JDBC statements, but is an indication to the business logic tier to stop after a certain number of rows.