Question:
My application is working with a fairly large database. Althoughusers will never be allowed to request all of the data, differentselection conditions may return wildly varying amounts of data. I’mworried that the performance of the data retrieval may beinconsistent, based on how much data is retrieved from a particularuser request.
Answer:
Well, you’re right to be worried about this possibility. Theperception of performance, like the perception of anything, isconditioned by expectations. If one user query completes rapidly andanother, seemingly similar query takes a much longer time, unhappyusers may not understand the perfectly good underlying reason for it.
Oracle Power Objects helps you address this potential problem bysetting the RowFetchMode property for each bound container. TheRowFetchMode property determines how your OPO application retrievesdata from a database server.
There are two basic settings for the RowFetchMode property. The”Fetch All Immediately” setting will immediately retrieve thecomplete set of data to the client whenever a query is executed. The”Fetch As Needed” setting will initially retrieve only the first 10rows of data when a query is executed. As additional rows arerequested, such as when a user scrolls past the first 10 rows in arepeater, additional rows of data are retrieved.
If you are using the “Fetch As Needed” retrieval, asking for a countof the rows for a query with the GetRowCount() method for therecordset will only return the number of rows that have beenretrieved to the client, not the total number of rows that satisfy aquery. If you want to retrieve data incrementally and still want anaccurate count of the total number of rows that will satisfy a query,you can use the “Fetch Count First” setting for the RowFetchModeproperty. This setting will get a row count returned from the serverwith the initial set of data rows, while still fetching data from theserver 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 varywildly. There may be small delays as the client applicationretrieves incremental data, but these will be less noticeable than apotential big delay when the query is executed.
There are two caveats to be aware of when using either of the “FetchAs Needed” settings. First, you cannot use this setting if you areperforming applications that require a complete set of data, such asdisplay a summary total on the screen based on the returned data.
Finally, either the CommitForm() or the CommitWork() methods will eradicatethe cursor for a query and prevent any additional rows from beingincrementally retrieved. Make sure that all rows have been retrieved beforeyou allow the user to commit any of their changes.
