Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL
Expertise: Beginner
Mar 21, 1997



Building the Right Environment to Support AI, Machine Learning and Deep Learning

How do I achieve consistent data retrieval performance?

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 user request.

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.

DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date