Cache Size

This was one topic that I just completely missed in the performance chapter, and boy was that a mistake, as the cache size could have a big impact on performance. The CacheSize is a property of the recordset and identifies how many records are fetched in one go and stored in a local cache on the client. By default this is 1, so with a large table you might fetch each record individually.

This could be quite time consuming if moving through the records. For example, image a table with one thousand rows. Creating a recordset on this table, and moving through all one thousand rows would result in one thousand fetches, whereas a cache size of 100 would only perform 10 fetches, each time fetching 100 records. Using this example, let's look at some figures:

Notice that using a larger cache size is much faster, especially for server based cursors. That's because server based cursors fetch the data as it's needed, so with a cache size of 1, each record is fetched individually as it is accessed. Moving the cache size to 100 allows ADO to fetch 100 records, and the records can be accessed from the local cache. Once the end of the cache is reached, the next one hundred records are fetched. For a cache size of 1000, only one fetch is required.

For client side cursors, all of the data is fetched up front, and processed by the client, so the cache size has less of an impact, although it still improves performance.

You should experiment with suitable values to find one that gives the best performance. Don't always go for the largest as this may not be the best overall, since a large cache size means more memory is used in the client, and ADO has to perform more memory management. For example, using a table with ten thousand records gives very different results to the table shown above:

This shows that for server based cursors, selecting a very large cache size is worse than a smaller one. So test for suitable values.

So the conclusion is that if fetching records sequentially from a recordset, you are better off increasing the cache size.