Increasing Performance Using
By Jason Miller
One of the most commonly ignored properties of the ADO RecordSet object is
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
.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
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
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
ADO 2.5 Programming (Wrox Press) realized a 33% performance boost using a
.CacheSize of 10 (page 421).
.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
Hopefully I've been persuasive enough concerning the benefits of the
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.