PROPERTY:

cacheSize

DESCRIPTION:

Indicates the number of result rows that are cached locally in memory.

PROPERTY OF:

ADOQuery

ADOTable

ADOStoredProc

ADODataShape

ADOPersist

DEFAULT VALUE:

1

DESCRIPTION:

Use the cacheSize property to control how many rows to retrieve at one time into local memory from the provider. 

For example, if the cacheSize property is 10, after first opening the ADORowset object, the provider retrieves the first 10 rows into local memory.

As you move through the ADORowset object, the provider returns the data from the local memory buffer.

As soon as you move past the last row in the cache, the provider retrieves the next 10 rows from the data source into the cache.

The value of cacheSize can be adjusted during the life of the ADORowset object,

but changing this value only affects the number of rows in the cache after subsequent retrievals from the data source.

Changing the property value alone will not change the current contents of the cache.

If there are fewer rows to retrieve than cacheSize property specifies, the provider returns the remaining rows and no error occurs.

Rows retrieved from the cache don't reflect concurrent changes that other users made to the source data.

To force an update of all the cached data, use the refresh method of ADORowset object.

A cacheSize property setting of zero is not allowed and returns an error.

ADO uses this property to determine the number of rows to fetch and cache.

While you are within the range of cached rows, ADO just returns data from the cache.

When you scroll out of range of the cached rows, ADO releases the cache and fetches the next cacheSize number of rows. 

Unfortunately, there isn't a single optimal cacheSize that is appropriate for all applications.

Test your application with different cacheSize values, and use the one that results in the best performance.

If cacheSize is set to a value greater than one, the navigation methods (next(), first(), last(), goto()) 

may result in navigation to a deleted record, if deletion occurs after the records were retrieved. 

After the initial fetch, subsequent deletions will not be reflected in your data cache until you attempt to access 

a data value from a deleted row. However, setting cacheSize to one eliminates this issue since deleted rows cannot be fetched.

Setting cacheSize to a higher value can, in many cases, greatly improve the performance and scalability of your application.

This property is based on the "Maximum Open Rows" provider-specific property (in the dynamic properties collection).

You cannot set cacheSize to a value greater than "Maximum Open Rows".

To modify the number of rows which can be opened by the provider, set "Maximum Open Rows".

The "Maximum Open Rows" property, which is supported by all the providers, determines how many rows can be active at any one time.

"Maximum Open Rows" property impacts performance because of the way it interacts with ADO and the cacheSize property.

ADO Recordset objects do not release open rows held in memory until after they request more data from the data provider. 

This means that all data requests performed after the first request will only be able to retrieve "Maximum Open Rows" minus cacheSize rows. 

For example, if you created an application that iterated in sequence through each recordset row in a cache and you set the "Maximum Open Rows"

value to 10 and the cacheSize value to 8, performance would be affected in the following way: 

On the first request for data, ADO will request eight rows from the provider. 

After the application iterates through those eight rows, ADO will initiate its next request for eight rows. 

However, because "Maximum Open Rows" is set to 10 and ADO still has eight rows open, the provider will only return two rows. 

When the two rows are returned, ADO will release the first two rows in its local cache, returning the number held in memory to eight.