My application is working with a fairly large database. Although
users will never be allowed to request all of the data, different
selection conditions may return wildly varying amounts of data. I'm
worried that the performance of the data retrieval may be
inconsistent, based on how much data is retrieved from a particular
Well, you're right to be worried about this possibility. The
perception of performance, like the perception of anything, is
conditioned by expectations. If one user query completes rapidly and
another, seemingly similar query takes a much longer time, unhappy
users may not understand the perfectly good underlying reason for it.
Oracle Power Objects helps you address this potential problem by
setting the RowFetchMode property for each bound container. The
RowFetchMode property determines how your OPO application retrieves
data from a database server.
There are two basic settings for the RowFetchMode property. The
"Fetch All Immediately" setting will immediately retrieve the
complete set of data to the client whenever a query is executed. The
"Fetch As Needed" setting will initially retrieve only the first 10
rows of data when a query is executed. As additional rows are
requested, such as when a user scrolls past the first 10 rows in a
repeater, additional rows of data are retrieved.
If you are using the "Fetch As Needed" retrieval, asking for a count
of the rows for a query with the GetRowCount() method for the
recordset will only return the number of rows that have been
retrieved to the client, not the total number of rows that satisfy a
query. If you want to retrieve data incrementally and still want an
accurate count of the total number of rows that will satisfy a query,
you can use the "Fetch Count First" setting for the RowFetchMode
property. This setting will get a row count returned from the server
with the initial set of data rows, while still fetching data from the
server 10 rows at a time as needed.
By using either of the "Fetch As Needed" or "Fetch Count First"
setting for the RowFetchMode property, you can even out performance,
since the initial amount of data returned from a query will not vary
wildly. There may be small delays as the client application
retrieves incremental data, but these will be less noticeable than a
potential big delay when the query is executed.
There are two caveats to be aware of when using either of the "Fetch
As Needed" settings. First, you cannot use this setting if you are
performing applications that require a complete set of data, such as
display a summary total on the screen based on the returned data.
Finally, either the CommitForm() or the CommitWork() methods will eradicate
the cursor for a query and prevent any additional rows from being
incrementally retrieved. Make sure that all rows have been retrieved before
you allow the user to commit any of their changes.