Optimizing ADO Calls

When referencing recordset values in your code, you probably write something like this:

- continued -

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

There is a second way to write this code, by fully qualifying the recordset and field object references:

If rs.Fields("QuantityOrdered").Value < 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: Since the fields in the Recordset object are ordered with their index beginning at zero, rs(12) will get the 13th element!

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

Now, you may say that using the ordinal notation decreases code readability, and I would agree. Several readers have been anxious to point out that you can use constants to refer to recordset elements. Although this has a very slight performance penalty in terms of script size and initial interpreter processing, it represents a nice trade-off between code readability and efficiency, e.g.

const fldQuantityOrdered = 12 ... if rs(fldQuantityOrdered) < 1 then Nosale = True

This also has the added benefit of making changes to your code easier should your recordset ordinal positions change.

Remember, when it comes to optimizing your code for a high-volume website, be prepared and even anxious to try (and test) several techniques to obtain the desired effect. Remember that when it comes to code optimization, in some cases, the LEAST intuitive answer is the correct one.

Happy Programming!