How do I achieve consistent data retrieval performance?

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.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS