Optimizing ADO Calls Using the Set Statement

In an earlier article on 4Guys (Optimizing ADO Calls), I detailed a method of using ordinal references to particular columns in a Recordset to increase the efficiency with which Recordset elements are obtained. For example, when referencing recordset values in your code, you probably write something like this:

- continued -

If rs("QuantityOrdered") < 1 then NoSale = True

If you refer to recordset values in your code quite a bit (and most of us do), there is a way to improve performance tremendously with very little effort. Here's the secret: when referring to recordset elements, use the ordinal form. This removes the requirement for a pseudo-dictionary lookup on the fieldname. That would mean our sample above would look like this (assuming that the field QuantityOrdered was the 13th field in our recordset):

If rs(12) < 1 then NoSale = True

(Note: I recommend using a Const fldName = N, where N is the ordinal value of the column you're interested in, and then refer to the recordset as rs(fldName) as opposed to rs(N). This leads to cleaner code which is easier to read... no magic numbers!)

Yet another method of referring to recordset fields is available. This method is probably the most powerful when it comes to optimizing for speed with larger recordsets. By SETting a variable to a field reference, you avoid a table lookup and separate assignment operation altogether. For example, consider this (pseudo-)code based on our examples above:

const fldQuantityOrdered = 12 Dim objQuantityOrdered ... set rs = [open a recordset] set objQuantityOrdered = rs(fldQuantityOrdered) do until rs.eof ... if objQuantityOrdered < 1 then NoSale = True ... rs.movenext loop ...

Notice that we only have to do the assignment once for objQuantityOrdered, and from that point on it is always set to the current value of rs(fldQuantityOrdered).

Since objQuantityOrdered is an actual object, refering to a specific column of a Recordset object, you can use all of the properties and methods of the Field object. For example, you could do:

const fldQuantityOrdered = 12 Dim objQuantityOrdered ... set rs = [open a recordset] set objQuantityOrdered = rs(fldQuantityOrdered) Response.Write objQuantityOrdered.Name

To learn more about the Field object, read the technical documentation.

Here are some sample benchmarks for three runs using each method (not done under a controlled environment, but close enough to prove the point!):

	rs("fieldname")	rs(x)	'set method'
Results: (in seconds)	2.967	1.650	0.586
	2.824	1.611	0.602
	2.893	1.613	0.594
Average:	2.895	1.625	0.594

From the impromptu benchmarks above, you can see that the method 1 (which I find most people currently using) is the slowest. Method 2 is a very nice alternative, especially for pople who want a 'quick fix' for existing code without having to make many changes. Method 3, obviously the best in terms of execution speed, is very well suited to any of your new development. Overall, method 3 is a very good technique for you to keep in your arsenal.

Happy Programming!

Related Articles: