DAO Recordset: Caching Multiple Records for Performance
| Overview | How Do I | FAQ | Sample | | ODBC Driver List
This article discusses the mechanism by which you can use a configurable buffer to cache multiple records in a recordset.
****Note ****The recordset caching mechanism described here applies only to ODBC data sources. It has no effect or benefit with non-ODBC databases.
Topics covered include:
When should you use record caching?
Configuring the record cache
Filling the record cache
Normally, records are retrieved from the database one at a time. To improve the performance of operations such as seeking and scrolling in recordsets based on ODBC data sources, you can cause DAO to cache multiple records. When you request a record, the database engine looks for it first in the cache. If the record is not in the cache, the database engine gets the record from the server. This is marginally slower at the time the cache is filled, but faster for operations that navigate through the records later. Use of this mechanism is limited in several ways, however (besides its limitation to ODBC); see When Should You Use Record Caching?.
For an understanding of the DAO data caching mechanism that underlies MFC objects, see the following topics in DAO Help:
CacheSize, CacheStart Properties
FillCache Method
When Should You Use Record Caching?
Is record caching right for your application? This depends on several factors:
Are you using a remote data source? Caching is really useful only for remote data — ODBC data sources.
Are you using dynaset-type recordsets? Caching is for use with these recordsets only.
What kind of performance do you need to optimize? If your application makes intensive use of scrolling, seeking, finding, or other methods of positioning the current record, you probably should cache records.
Keep in mind that caching requires storage space and that it can take extra time to fill the cache. Records retrieved from the cache also don't reflect changes made by other users in the database.
Configuring the Record Cache
To use MFC's record caching, you need to do two things:
Set the record position at which the cache is to start.
Specify the first of the records to be cached by giving its bookmark in a call to . You can obtain a record's bookmark by moving to the record and calling . GetBookmark returns a value that you can store in a variable. Use the variable in your SetCacheStart call.
Set the size of the cache, in records.
How big should you make your cache? This depends on your needs and what you are optimizing for. If you expect your users to perform long scrolls or seeks, moving through many records at a time, you probably need a larger cache.
A typical case might be an application that, for example, displays 25 records at a time. For such an application, a cache of 75 records might be a good choice. This would allow quick response for both scrolling up and down and paging up and down.
Filling the Record Cache
DAO fills the cache as you request that records be retrieved from the data source. But you can explicitly fill all or part of the cache at any time by calling .
When you call FillCache, you can specify either or both of the following:
The bookmark of the record at which you want to start filling the cache. If you omit the lBookmark parameter to FillCache, the cache is filled starting with the record specified in DAO's CacheStart property. You can set that value with .
The number of records you want to put into the cache. This can be less than the cache size you specified with a call to . If you omit the lSize parameter to FillCache, the number of records defaults to DAO's CacheSize property value, which you can set with SetCacheSize.
For more information, see the member function in the Class Library Reference. For information about the DAO caching mechanism, see the following topics in DAO Help:
CacheSize, CacheStart Properties
FillCache Method
See Also DAO: Where Is..., DAO Recordset