ADO - ADO Recordset CacheSize Property

The CacheSize property in ADO (ActiveX Data Objects) specifies the number of records that an ADO Recordset object keeps in its local cache. Instead of requesting every record individually from the data source, ADO can retrieve a group of records at once and store them temporarily in memory. When the application moves through the records, many of the required records can then be accessed from this cache, which can reduce the number of requests sent to the database.

Purpose of CacheSize

When an application works with a large Recordset, repeatedly communicating with the database can increase network traffic and affect performance. The CacheSize property allows ADO to fetch records in groups.

For example, suppose a Recordset contains 1,000 records and the cache size is set to 50. ADO can retrieve approximately 50 records at a time. When the application moves through these records, it can use the records already available in the local cache. When it reaches the end of the cached records, ADO can retrieve the next group.

The cache therefore acts as a temporary storage area between the application and the underlying data source.

Syntax

The property can be specified as follows:

recordset.CacheSize = value

Here, recordset represents the ADO Recordset object, while value specifies the number of records that should be stored in the cache.

For example:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM Students", conn

rs.CacheSize = 50

In this example, the cache size is set to 50 records.

How CacheSize Works

Consider a database containing 500 student records. If the application processes these records sequentially and the cache size is set to 50, ADO can retrieve records in groups rather than requiring a separate database operation for every record.

The general process is:

  1. The application opens the Recordset.

  2. ADO obtains a group of records from the data source.

  3. These records are temporarily maintained in the client-side cache.

  4. The application reads or navigates through the cached records.

  5. When additional records are required, ADO retrieves another group from the data source.

  6. The process continues until the required records have been processed.

This can be particularly useful when the application navigates through a Recordset sequentially.

Example

Consider the following example:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.Open "SELECT StudentID, StudentName FROM Students", conn

rs.CacheSize = 20

Do Until rs.EOF
    Debug.Print rs.Fields("StudentID").Value
    Debug.Print rs.Fields("StudentName").Value

    rs.MoveNext
Loop

rs.Close
Set rs = Nothing

Here, the application retrieves student information and moves through the Recordset using MoveNext. A cache size of 20 tells ADO to use a cache containing approximately 20 records for its fetching operations.

Choosing an Appropriate Cache Size

The appropriate cache size depends on the application and the amount of data being processed.

A small cache size generally requires less memory but may result in more frequent requests to the data source.

A larger cache size can reduce the frequency of requests, particularly when processing records sequentially, but it can require more memory and may retrieve records that the application never actually uses.

For example, an application processing a small number of records may not benefit from an extremely large cache. Conversely, an application that sequentially processes thousands of records may benefit from an appropriately sized cache.

CacheSize and Performance

The main reason for using CacheSize is performance optimization. In applications where the database is accessed through a network, communication between the application and database server can be relatively expensive.

Suppose an application needs to process 1,000 records. If it repeatedly requests small amounts of data from a remote server, network communication can become a significant part of the processing time. Fetching records in groups can reduce this communication overhead.

However, CacheSize should not automatically be set to a very large value. A large cache does not guarantee better performance because it can increase memory usage and cause unnecessary records to be retrieved.

CacheSize and Recordset Navigation

CacheSize is particularly relevant when working with Recordset navigation methods such as:

rs.MoveFirst
rs.MoveNext
rs.MovePrevious
rs.MoveLast

When the application navigates through records, cached records can be used where appropriate. This makes the property useful for applications that process Recordsets sequentially.

The actual behavior can also depend on the cursor type, cursor location, provider, and data source being used.

Changing CacheSize

The cache size can be changed by assigning another value to the property.

For example:

rs.CacheSize = 100

This changes the requested cache size to 100 records.

The exact effect of changing the property can depend on the current state of the Recordset and the underlying provider. Therefore, applications should consider the characteristics of the provider rather than assuming identical behavior across all data sources.

Advantages of CacheSize

The important advantages include:

  1. It can reduce repeated communication with the database.

  2. It can improve performance when navigating through records.

  3. It allows records to be fetched in groups.

  4. It can be useful for applications working with remote databases.

  5. It provides a way to control the approximate amount of record caching used by a Recordset.

Limitations

CacheSize does not mean that the entire Recordset is permanently stored in memory. It controls the number of records ADO attempts to maintain in its cache for fetching purposes.

Its effectiveness also depends on the type of cursor, provider, connection, and data source. Some providers may handle caching differently, and not every Recordset operation necessarily benefits equally from changing this property.

Another important consideration is memory consumption. Increasing the cache size means that more records may be maintained locally, so setting an unnecessarily large value can consume additional resources without providing a corresponding performance improvement.

CacheSize vs. Recordset Size

These two concepts should not be confused.

Recordset size refers to the number of records available in the Recordset.

CacheSize refers to the number of records ADO attempts to keep in its local cache for fetching and navigation.

For example, a Recordset could contain 10,000 records while its CacheSize is set to 100. This does not mean that the Recordset contains only 100 records. The Recordset can still represent all 10,000 records, while ADO manages fetching them through the cache.

Practical Example

Suppose a reporting application retrieves 5,000 customer records from a remote database and processes them one by one. Instead of repeatedly communicating with the database for every individual record, the application can use an appropriate cache size so that records are retrieved in groups.

For example:

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.CursorLocation = adUseClient
rs.Open "SELECT CustomerID, CustomerName FROM Customers", conn

rs.CacheSize = 100

Do While Not rs.EOF
    Debug.Print rs.Fields("CustomerID").Value
    Debug.Print rs.Fields("CustomerName").Value

    rs.MoveNext
Loop

rs.Close
Set rs = Nothing

In this example, the application processes customer records sequentially, while a cache size of 100 is specified.

Conclusion

The ADO Recordset CacheSize property provides control over how many records ADO attempts to keep in its local cache when working with a Recordset. Its primary purpose is to reduce unnecessary data-source communication and potentially improve performance during Recordset navigation. A suitable cache size should be selected according to the application's workload, available memory, network conditions, cursor configuration, and provider behavior. It is therefore an important performance-related property when developing ADO applications that work with large or remotely stored datasets.