Caching Remote Data

If you are using a Microsoft Jet workspace, you can use the Microsoft Jet CacheSize and CacheStart properties to store all or part of the data contained in a dynaset-type Recordset object in local memory. Local caching of records dramatically speeds up operations when moving through dynaset records in both directions, and shows significant improvements even when moving in a forward-only direction.

To use caching, specify the number of records to be stored using the CacheSize property. The range of values you can use for the CacheSize property is between 5 and 1200 records. If the size of the cache exceeds available memory, the excess records spill into a temporary disk file. Typically, you set the value of the CacheSize property to 100. To recover the cache memory, set the CacheSize property to 0. Specify the beginning record by setting the CacheStart property to the value of a bookmark using the Bookmark property. Then use the FillCache method to retrieve every value in the specified cache range and fill the cache with server data. For example, you might use the following code when you open your form:

With rst
	.CacheSize = 20
	.CacheStart = rst.Bookmark
	.FillCache
End With

This code will preload an internal buffer with 20 records of data. As the user moves through those records, they display quickly because they won’t have to be retrieved from the server.

This is more efficient than filling the cache as each record is retrieved, so if you know ahead of time that all records in the cache range will be visited, call the FillCache method every time you change the setting of the CacheStart property. Retrievals within the cache boundary occur locally, speeding display of the cached records in a datasheet or in a continuous form.

As you move through records, the data you retrieve will be cached until you move out of the defined range. Once you’ve hit the end of the range defined by the CacheSize and CacheStart properties, you need to change the CacheStart property setting to stay synchronized with the set of records you’re working with. Then, caching will continue with the new range, reusing values appropriately if the new cache range overlaps the old.

Using a cache can provide significant performance benefits. If the application requires backward scrolling within the cached region, the performance improvements will be even greater. Depending on your scenario, using a cache may be faster than using a read-only, forward-only-type Recordset object, especially if it contains Memo or Long Binary (OLE Object) fields that are referenced only occasionally.

See Also For more information about forward-only-type Recordset objects, see “Forward-Only-Type Recordsets” earlier in this chapter.

The size of the cache you use is determined by the application’s needs. For example, if you are displaying these records to the user, then you might use a cache size determined by the number of records permitted on the screen. If the code has no user interaction, then you can make a tradeoff between local memory availability, network traffic, and record size.

Notes

• One important distinction with caching ODBC data is that the cached data will include Memo fields but not Long Binary fields (such as OLE Object fields). Hence, the cache can get large if you have those types of fields in your result set. If it becomes too large, then the cache overflows onto disk. Although more expensive than in-memory cache, this is still usually better then generating more round-trips to the server. If you are using other Microsoft Jet data sources, then only a pointer to the database page containing the Memo or Long Binary field is stored in the cache, and the actual contents are only retrieved if the field itself is retrieved.
  
  
• You can’t use recordset caching with an ODBCDirect Recordset object, but you can change the default cache size by setting the CacheSize property of an ODBCDirect QueryDef object.