by Phil Paxton
Phil Paxton has compiled a list of ten commandments all ASP developers
should religiously follow. On of the commandments is:
Thou shalt use neither a "counting" loop nor .MoveLast to count the number of records in an ADO recordset. (either set the .CursorType correctly or use a Count() in SQL)
In this article, Phil explains why that commandment is so, and the disadvantages present to using .MoveLast or a counting variable.
In the "regular" world of Visual Basic, one of the most common methods of accessing a database is with what's called DAO (Data Access Object). It's the primary reason VB "took off" in the 92-93 time frame and aside from bug fixes, the sole reason VB 3.0 was released. Essentially, it made writing Access database programs a breeze and meant your users didn't have to have Access in order to use the application (in fact, Access might slow some of those programs down). Before this new feature, Visual Basic wasn't much to look at and certainly wasn't much to write home about. Immediately after it was literally flying off of the shelves, and Microsoft research revealed roughly 80%-90% of the sales were for the sole purpose of database manipulation.
One of the problems with DAO (it's actually an ADO ancestor; in fact, if you look at the code I'm about to write, odds are, you can understand it, even if you haven't written any "real" VB code as this type of activity is very similar to ADO) is that you couldn't tell what the .RecordCount was [accurately] unless you visited the end of the recordset. So there's tons o' VB code littering the world with things like this:
Set rst = dbCurr.OpenRecordset(...)
MsgBox "The number of records is " & rst.RecordCount
i = 0
Do While Not rst.EOF()
i = i + 1
MsgBox "The number of records is " & i
When you're talking about a single user (or a small group of users) running against a relatively small database (e.g., if you have an inventory of let's say 10k items, that's definitely a small database), you're just slowing down the response time a little. And your program will likely not time out on the user.
Let's consider the web.
Suppose the architect of your app wants the user to know how many pages & records there are but your DBA (Database Administrator) says you are only allowed to open the database with certain .CursorTypes because of how expensive they can be. So you decide to employ one of those schemes I just posted.
It's like each user who opens a recordset and needs a record count opening a recipe box and counting all of the little 3x5 cards in there, individually. Every time they open a recordset, the recipe box gets opened and the count begins...one by one. Granted, the computer is essentially a stupid beast and it'll do what you tell it to do (as opposed to what you want it to do), mostly without complaint. But when you run code like this, it's putting your database on a rather short leash and yanking it around. All you need is for several dozen (let alone several hundred or several thousand) users to put your database on a short leash and all start yanking it at the same time (put yourself in the shoes of the database system, or worse, in the shoes of the DBA who then has to try and tune/optimize it).
Now, if we're going to count the recipe cards every time we open the box, would you, as a user, really want to sit there and watch the browser take a coffee break whilst the census count begins? Suppose the recipe box were smart enough to tell you what the count was any time you asked it to? Or what if it were smart enough to tell you the count of recipes with a particular ingredient in it? Come to think of it, that's a really great extension to the analogy. You open the recipe box, and examine each record, sorting out those without any tyramine ingredients (familiar to headache sufferers as things which contain a rather elegant breakdown of a key amino acid and found in nuts, chocolate, practically any food with preservatives -- e.g., cheese, pepperoni, sausage, etc.) So now you have all these users telling the recipe box, er, database, to produce a given list, then go back and count them (after the selection is done, not as you select them).
Guess what? The SQL-based databases ARE smart enough to tell you the count. And it can be done as part of your SELECT statement. And the database, which can count those recipe cards a heck of a lot faster than your program can, can deliver better performance.
The computer/application/web site/browser/whatever will only go as fast as its slowest resource. Sure, your program can count, but it's making a trip to the database each time you do a .MoveNext and a trip back saying, "done. now what?". So the database is being slowed down to the time it takes for the server to process every one of those steps. Granted, .MoveLast is better than a whole bunch of .MoveNext statements, but you're still telling the database to do a LOT of unnecessary work.
If you wanted to do addition, you can write code that does it like this:
Compute Sum of A + B (assuming natural; i.e., counting numbers):
Sum = A
Do While B > 0
Sum = Sum + 1
B = B - 1
Response.Write "A+B=" & Sum
Would you really write code like this to do addition? Wouldn't it be faster to submit this to some section of the computer which can be programmed to do addition and knows how to do it really, really fast?
The above demonstrates what you are doing when you do .MoveLast or .MoveNext [inside a loop]. You're delivering results, but not very efficiently. Think about all of your web users running a web site which operated like this....then factor [pun intended] multiplication: that's addition nested within itself. And exponentiation...that's multiplication nested within itself, which is then nested within addition. When you use SQL's Count() or an expensive .CursorType, you are tapping into the database's inherent ability, essentially telling it your intentions, and trusting the database designers to have enabled the database software to know what's the most efficient way to deliver the results.