Published: Wednesday, December 08, 1999
Optimizing ADO Calls Using the Set Statement
By Mike Shaffer
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:
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:
<![CDATA[
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:
<![CDATA[
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:
Optimizing ADO Calls
