Oh. You were referring to index based access to the fields collection of the recordset. You're right - I was confused, but this is a forum about SQL Server and not ASP, so forgive me for vectoring off about the database. :)

Anyway, as I understand it, access to the recordset's field collection is supposed to be marginally faster using an integer index as opposed to the name, which has to be hashed in order to perform the name-based lookup.

You are correct that access by an index is a bit more "fragile" since anybody who messes with the query could change the order of the columns in the resultset and break your index based code. Of course, if they change the name of a column (or alias) that will break your named based code as well.

The fact is that code that is written to refer to or manipulate query results in a recordset is tightly coupled to that query, so any changes to the query results will have an effect on the code, and there is little you can do about that.


Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Just one observation if I may. Using index-based access to recordsets makes your code a lot less readable.

Total = rs(12) * rs(17) * (1 - rs(3))
OR
Total = rs("Quantity") * rs("UnitPrice") * (1 - rs("DiscountPercent"))

which would you rather be looking at if you opened up someone else's code?

If you really insist on using index-based methods, I would strongly suggest you take the trouble to define appropriate constants to give others (and yourself when you come back to this code in the future) a chance, example
Const cFieldDiscountPercent=3, cFieldQuantity=12, cFieldUnitPrice=17
...
Total = rs(cFieldQuantity) * rs(cFieldUnitPrice) * (1 - rs(cFieldDiscountPercent))

rgds
Phil

Yes, Phil is right on that.

I could not read my own code after a while, when used that approach. From the code readablity perspective I would hate to do that. But I wasn't knowing that it[rs(1)] has a better performance than name referencing[rs("FName"]. Quiet interesting.

Cheers!

_________________________
- Vijay G
Strive for Perfection

Two points, all of these performance issues are only going to matter if your server is handling very large numbers of queries.
Secondly with the issue of using the ordinal number versus the field name you can always define your own constants to refer to the fields, e.g. and then use if you want the best of both readability and speed.

--

Joe

--

Joe (Co-author Beginning XML, 3rd edition)

The index based access to collections in general is marginally better performing than name based. But this is really a micro-optimization. You'll gain far better return on your programming effort by making sure your queries are as efficient as possible and your tables are appropriately structured with the appropriate indexes, etc. - that's where the real performance gains are to be made. One poorly performing query will completely overwhelm the microseconds you might gain by applying your efforts to making your recordset access as efficient as you can. The time would be much better spent analyzing the query and/or database design to remove the inefficiencies there.

I would agree that using constants makes for much more readable (i.e maintainable) code. We use them for just about everything; we try to avoid "magic" numbers in all our code, preferring to use constants in their place. That way, if, say, a query needs to change, then rearranging the field access is a matter of renumbering the constants, all defined in one place, instead of searching for numbers which can be hard to find.

There are an awful lot of microseconds in a bug caused by missing a field reference, after all...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

One technique I use that (to me at least) argues FOR the index based reference to fields is when I'm building a data tier method that populates a class with DB values:

"SELECT x, y, z FROM entityTable"
...
MyObject.x = objRecordSet(0)
MyObject.y = objRecordSet(1)
MyObject.z = objRecordSet(2)

Admittedly, this results in a little extra work if the query changes, but it seldom does apart from additions to the end which are quick.
Readability is not hurt nearly as much because of the class property names that match to the column indexes.

Ok, enough of my rambling, as Jeff pointed out, this is a SQL thread.

Before I even keep reading some of the previous posts I stopped at this one:
  If you will be using this method, then you are asking for trouble by doing a select * from table.

Declare all of your fields in both and avoid the later headache for your self or the next developer.



Sal