Best way to access field values using ADO.

[John Beschler]

Is there any difference, performance-wise, between these
two methods?


Method 1:

szSQL = "SELECT fld1, fld2, fld3 FROM tbl1"
Set objRS = objConn.Execute(szSQL)
....
szFld1 = objRS("fld1")
szFld2 = objRS("fld2")
szFld3 = objRS("fld3")


Method 2:

Const cFld1 = 0
Const cFld2 = 1
Const cFld3 = 2
szSQL = "SELECT fld1, fld2, fld3 FROM tbl1"
Set objRS = objConn.Execute(szSQL)
....
szFld1 = objRS(cFld1)
szFld2 = objRS(cFld2)
szFld3 = objRS(cFld3)


I've read many places to avoid the first method because
ADO must do a lookup to retrieve the ordinal positions of
the fields anyway. However, in the second method, doesn't
it still have to do a lookup to get the value for the
constants? I guess the difference is the first method has
to query the database for the ordinal position where the
second method just has to look in memory. Is this correct?

Thanks,
John

[Bob Barrows]

John Beschler wrote:

> Is there any difference, performance-wise, between these
> two methods?
>

Yes, but unless you're looping, you are unlikely to notice the difference.

Oh! Which is faster? As a general rule, it is always faster to reference
items in a collection by their index number vs their name, so Method 2 will
be faster.

However, you haven't mentioned the fastest method: using Field objects


Dim oFld1, oFld2, oFld3
Const cFld1 = 0
Const cFld2 = 1
Const cFld3 = 2
szSQL = "SELECT fld1, fld2, fld3 FROM tbl1"
Set objRS = objConn.Execute(szSQL)
....
SET oFld1= objRS(cFld1)
SET oFld1 = objRS(cFld2)
SET oFld1 = objRS(cFld3)
'then, in a loop, reference the field objects' value property

I would not use this method unless I was looping through a lot of records,
something which I am unlikely to be doing in asp except via a GetRows array.

> I've read many places to avoid the first method because
> ADO must do a lookup to retrieve the ordinal positions of
> the fields anyway. However, in the second method, doesn't
> it still have to do a lookup to get the value for the
> constants?

No, the benefit of using constants is that they are resolved at
compile-time. When the compiler compiles this code, it substitutes the value
of the constant for every occurrence of the constant in the code. So when
the code is executed at run-time, the code that runs is

szFld1 = objRS(0)

not

szFld1 = objRS(cFld1)

HTH,
Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[John Beschler]

Ya learn something new everyday.

I'd never heard of field objects. Thanks for the pointer.

I am trying to use GetRows in any new stuff that I do;
however, when maintaining existing code it seems to me to
be easier to change the field references from rs
("somefield") to rs(cConstant) than recoding the whole
thing using getrows.

Thanks again. You guys are so smart!

John

>-----Original Message-----
>John Beschler wrote:

>> Is there any difference, performance-wise, between these
>> two methods?
>>

>
>Yes, but unless you're looping, you are unlikely to

notice the difference.

>
>Oh! Which is faster? As a general rule, it is always

faster to reference

>items in a collection by their index number vs their

name, so Method 2 will

>be faster.
>
>However, you haven't mentioned the fastest method: using

Field objects

>
>
>Dim oFld1, oFld2, oFld3
>Const cFld1 = 0
>Const cFld2 = 1
>Const cFld3 = 2
>szSQL = "SELECT fld1, fld2, fld3 FROM tbl1"
>Set objRS = objConn.Execute(szSQL)
>....
>SET oFld1= objRS(cFld1)
>SET oFld1 = objRS(cFld2)
>SET oFld1 = objRS(cFld3)
>'then, in a loop, reference the field objects' value

property

>
>I would not use this method unless I was looping through

a lot of records,

>something which I am unlikely to be doing in asp except

via a GetRows array.

>

>> I've read many places to avoid the first method because
>> ADO must do a lookup to retrieve the ordinal positions

of

>> the fields anyway. However, in the second method,

doesn't

>> it still have to do a lookup to get the value for the
>> constants?

>
>No, the benefit of using constants is that they are

resolved at

>compile-time. When the compiler compiles this code, it

substitutes the value

>of the constant for every occurrence of the constant in

the code. So when

>the code is executed at run-time, the code that runs is
>
>szFld1 = objRS(0)
>
>not
>
>szFld1 = objRS(cFld1)
>
>HTH,
>Bob Barrows
>
>--
>Microsoft MVP -- ASP/ASP.NET
>Please reply to the newsgroup. The email account listed

in my From

>header is my spam trap, so I don't check it very often.

You will get a

>quicker response by posting to the newsgroup.
>
>
>.
>

[Aaron Bertrand - MVP]

> I am trying to use GetRows in any new stuff that I do;

> however, when maintaining existing code it seems to me to
> be easier to change the field references from rs
> ("somefield") to rs(cConstant) than recoding the whole
> thing using getrows.

As Bob suggested, this modification is probably for nothing; you are
unlikely to see any performance gains here. So I don't see why it's worth
the effort...

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Bob Barrows]

Aaron Bertrand - MVP wrote:

>> I am trying to use GetRows in any new stuff that I do;
>> however, when maintaining existing code it seems to me to
>> be easier to change the field references from rs
>> ("somefield") to rs(cConstant) than recoding the whole
>> thing using getrows.

>
> As Bob suggested, this modification is probably for nothing; you are
> unlikely to see any performance gains here. So I don't see why it's
> worth the effort...

Exactly. Change the way you code your new applications. Don't bother messing
with the old ones.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Aaron Bertrand - MVP]

> Exactly. Change the way you code your new applications. Don't bother
messing

> with the old ones.

Though, if he's in there fixing other things, changing to GetRows() would be
a much more valuable transition than adding those CONST declarations and
cetera.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[John Beschler]

OK, Now I'm really confused. Are you saying there's no
discernable difference between rs("Somefieldname") and rs
(0)?


What started this whole thing, is I did a search on our
intranet web site for "SELECT *" and there's a bunch of
them. I was going to go through and start eliminating
those when I have some spare time. In the process, I
thought I'd fix the Field references as well.

Am I wasting my time?

>-----Original Message-----

>> Exactly. Change the way you code your new applications.

Don't bother

>messing

>> with the old ones.

>
>Though, if he's in there fixing other things, changing to

GetRows() would be

>a much more valuable transition than adding those CONST

declarations and

>cetera.
>
>--
>Aaron Bertrand
>SQL Server MVP
>[url]http://www.aspfaq.com/[/url]
>
>
>.
>

[Bob Barrows]

John Beschler wrote:

> OK, Now I'm really confused. Are you saying there's no
> discernable difference between rs("Somefieldname") and rs
> (0)?
>

Not unless you are looping, and even then, it will take quite a few loops,
and quite a few field value reads in that loop before you will detect the
difference. You can test it yourself and see. You might want to create a VBA
routine to test it so you can use the GetTickCount API method to get a very
accurate time measurement.

Bob Barrows


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Aaron Bertrand [MVP]]

> OK, Now I'm really confused. Are you saying there's no

> discernable difference between rs("Somefieldname") and rs
> (0)?

I'd be surprised if a human could time the difference.

> What started this whole thing, is I did a search on our
> intranet web site for "SELECT *" and there's a bunch of
> them.

This is a different issue altogether! SELECT * is definitely a higher
performance burden (over naming explicit columns) than the difference
between rs("name") and rs(ordinal). And this certainly wouldn't be wasted
effort.

Even better would be to move all your ad hoc SELECT statements into stored
procedures / stored queries.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[John Beschler]

>Even better would be to move all your ad hoc SELECT

statements into stored

>procedures / stored queries.

I have actually seen some very significant performance
improvement when moving adhoc SQL into a store procedure
(we are running SQL 2000), so I understand the benefits
there; however, do you recommend a stored prcedure for
every function on the web site?

For example: Let's say on one page I need Name, address,
Phonenumber. SO I code a SP to return those three fields.

Set objRS = objConn.Execute MySP MyParams....

On another page I may need only Name, and Phonenumber. Do
I write another SP for that page?

Set objRS = objConn.Execute MyOtherSP MyOtherParams

I guess from a maintenance standpoint that would be
better, cinse there would be no worries that changing a
stored procedure for one page would break it for another.

What are your thoughts on this?

Thanks,
John

>
>--
>Aaron Bertrand
>SQL Server MVP
>[url]http://www.aspfaq.com/[/url]
>
>
>.
>

[Aaron Bertrand - MVP]

> (we are running SQL 2000), so I understand the benefits

> there; however, do you recommend a stored prcedure for
> every function on the web site?

We control *all* data access through stored procedures; it is simply a
policy. Why would you want to allow any ad hoc queries? Keep your data
access consistent; use stored procedures everywhere. Yes, for all functions
in a web site. If you are accessing your data, you owe it to yourself to
access it as properly, consistently and efficiently as possible.

> For example: Let's say on one page I need Name, address,
> Phonenumber. SO I code a SP to return those three fields.
>
> Set objRS = objConn.Execute MySP MyParams....
>
> On another page I may need only Name, and Phonenumber. Do
> I write another SP for that page?

This is a judgment call, and depends more on how much data is coming back
'wasted'... is this stored procedure returning one row, page 1 of 10, or the
whole table? Typically, we have a single stored procedure that returns all
attributes of a single entity (as opposed to all entities - usually when
returning all entities we only need the internal and external identifier,
e.g. the primary key and the label). If we get a couple of extra columns on
certain pages, it's really not that big a deal.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]