To read the article online, visit http://www.4GuysFromRolla.com/webtech/053100-1.shtml

Increasing Performance Using .CacheSize

By Jason Miller


One of the most commonly ignored properties of the ADO RecordSet object is .CacheSize. This innocent sounding property defaults to 1 -- which is fabulous if you’re only getting one record. In actuality, you’re probably going to be pulling more than one record from the database, so having a cache size of 1 is a performance hazard.

The .CacheSize property is the control for how many database records the database selects into its buffer before giving any of them over to you. This improves your script performance by reducing the number of exchanges between your script and the database. Microsoft has technical information on the .CacheSize property at http://msdn.microsoft.com/library/psdk/dasdk/mdap952d.htm.

Ideally, for a generally well constructed web page, the .CacheSize should be set to the number of records you’re going to be grabbing from the database. Given that you probably don’t know that in advance (and running a count(*) selection would double your database queries, thus negating this benefit), guess. I've never run into a web page -- except for a very explicit database dump -- that used more than 50 records at any one time, but for general use, I keep my .CacheSize at 10. The performance measuring in Professional ADO 2.5 Programming (Wrox Press) realized a 33% performance boost using a .CacheSize of 10 (page 421). [With .CacheSize of 1, 10 and 100, they were able to serve 752, 1003, and 1033 pages, respectively.]

If you’re a visual or kinetic learner, get a couple of cans, one of which should be full of beans (or other discrete material), the other empty, and a spoon. The empty can is your ADO RecordSet. The full can is your database. Now, we’re going to Select * from Beans -- so start spooning those beans from the database into the ADO RecordSet one by one. That was the default .CacheSize of 1. Try it again, but spoon them 10 at a time (symbolizing the .CacheSize of 10). Better, yes?

The obvious question at this point is "Why shouldn’t I just set my .CacheSize for 1 Billion Records?" (with an evil laugh). This is an easy question to answer if you’ve still got your can of beans. Try using your spoon to get a billion (otherwise known as all) of the beans from the database to the RecordSet at the same time. The spoon (the system resources used to move from database to RecordSet) just can’t handle it and you end up with a mess. Ideally your system has more resources than just a spoon, but in peak usage times, each user may only end up with a spoonful of system resources. Extensive stress testing should be able to help you determine how big your spoon -- er, .CacheSize -- can be.

That's all I have to say. If you're interest has been piqued, you can see an example of the .CacheSize property in a VB app at http://msdn.microsoft.com/library/psdk/dasdk/mdae50ag.htm. Hopefully I've been persuasive enough concerning the benefits of the .CacheSize that you're going over your old code and fixing it as opposed to reading this, since I most certainly cannot think of a decent conclusion.

  • Jason Miller is an Intranet Web Application Developer in Portland, OR -- though his degree was in Public Relations. In his spare time, he works out by flying off the handle, pushing the limits, carrying things to far, running amok, and shaking things up a bit.


    Related Articles

  • Sample Chapter of Professional Active Server Pages 3.0, covering the Recordset object


  • Article Information
    Article Title: Increasing Performance Using .CacheSize
    Article Author: Jason Miller
    Published Date: Wednesday, May 31, 2000
    Article URL: http://www.4GuysFromRolla.com/webtech/053100-1.shtml


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers