Working with the CacheSize Property

When dealing with

Cursors
, especially those created by ODBC, it is important to control how much data is cached by ODBC in client memory. ASP assists in this area considerably, as the actual ADO processes are executed on the Web server. As such, cached data is stored in the server's memory, as opposed to the actual end-user or client machine. However, this can also have a downside as we scale up our server. If our default
Recordset
cache is large, and our server incurs a large volume of traffic, we can fast run into resource issues.

The

CacheSize
property is used to control how many records our underlying ODBC driver keeps in its memory buffer, and how many to retrieve at one time into local memory. For example if the
CacheSize
is set to
10
, the ODBC driver retrieves the first ten records into a local cache as soon as a
Recordset
object is opened. As we navigate through the
Recordset
object, the ODBC driver then retrieves data from the data source and puts it into the cache as required, for example as soon as we move past the last record in the cache, it retrieves the next ten records from the data source.

Typically, for a read-only forward-scrolling

Recordset
, we only want to cache one record at a time, and
CacheSize
is set to
1
. Because we don't need to move backward, and we're not changing the data, caching records on the client isn't efficient. Bringing them into memory as they are read is all that is needed.

However, when data is being updated, or we create a

Recordset
that supports forward and backward navigation, a larger
CacheSize
may be appropriate. In the case of updates, when the cache size is
1
, the recordset will need to go back to the server for each changed record. With a recordset that allows backward as well as forward navigation, it has to go back to the server to retrieve the same data many times, especially as we move upwards (
MovePrevious
) in the recordset.

The value of the

CacheSize
property can be adjusted during the life of the
Recordset
object, but changing this value only affects the number of records in the cache after subsequent retrievals from the data source. To force the cache size to be adjusted immediately, we can
Resync
the
Recordset
. However, we cannot set the cache size to
0
—if we do an error will result.

© 1997 by Wrox Press. All rights reserved.