Most efficient method IYHO

[Laphan]

Firstly a note to Bob.

I know you're going to get piffed with me, but I just to ask one last time.
Which of the following methods in all of your honest opinions is the most
efficient:

a) The 'omitted Command' method, ie the one where you go straight from
connection to recordset as per Bob's example below:

set cn=createobject ...
set rs=createobject ...
parm=<some value>
cn.sproc parm, rs
'process the results
rs.close
parm=<some new value>
cn.sproc parm, rs

b) The Parameter obj/collection method, which to me seems to create more
work and confusion.

c) I have re-checked myself (??) and found that the below method will work
under OLEDB as long as I specify the right type for my parameters:

Set oCmd.ActiveConnection = oConn
oCmd.CommandText = "BuildColoursMenu '" & CStr(strStockID) & "'"
oCmd.CommandType = 4
Set oRSv = oCmd.execute

Just as an aside query and one which I have just noticed, all of the above
were used to populate a <SELECT> pop-up menu, which works fine, apart from
the fact that the pop-up doesn't have a default value. I've never noticed
it do this before, as I have checked and most of my pop-ups have been manual
affairs and for some reason these will show a default value even if no
SELECTED property is set.. Is the only way round it to set the first rec to
resp.write SELECTED??

Thanks

Laphan

[Aaron Bertrand [MVP]]

> I know you're going to get piffed with me, but I just to ask one last
time.

> Which of the following methods in all of your honest opinions is the most
> efficient:

Nobody can answer that, because it depends on YOUR environment, YOUR
hardware, YOUR network, YOUR data, YOUR users, etc.

Do you want to know which method is the most efficient?

TEST THEM.

[Bob Barrows]

Laphan wrote:

> Firstly a note to Bob.
>
> I know you're going to get piffed with me, but I just to ask one last
> time. Which of the following methods in all of your honest opinions
> is the most efficient:
>
> a) The 'omitted Command' method, ie the one where you go straight from
> connection to recordset as per Bob's example below:
>
> set cn=createobject ...
> set rs=createobject ...
> parm=<some value>
> cn.sproc parm, rs
> 'process the results
> rs.close
> parm=<some new value>
> cn.sproc parm, rs
>
> b) The Parameter obj/collection method, which to me seems to create
> more work and confusion.
>
> c) I have re-checked myself (??) and found that the below method will
> work under OLEDB as long as I specify the right type for my
> parameters (so again, I ask: why bother with the Command object and

Parameters collection?):

>
> Set oCmd.ActiveConnection = oConn
> oCmd.CommandText = "BuildColoursMenu '" & CStr(strStockID) & "'"
> oCmd.CommandType = 4
> Set oRSv = oCmd.execute
>

I was not going to reply because I've already stated my preference. But I
did want to reiterate that eventually it does come down to personal
preference in the end.

I have read a book (Bill Vaughn's "ADO Examples and Best Practices") which
stated that based on SQL Profiler traces, the
"procedure-as-connection-method" technique is the most efficient, and showed
the results of some tests showing a 30% improvement in performance by using
this technique. However, my testing, as well as others, has failed to show
such a significant increase in performance. My tests always show some
improved performance, just never as high as 30%.

I have read another excellent book: David Sceppa's "Programming ADO" which
recommends against using the "procedure-as-connection-method" technique.
However, his reasons for not liking it are more in the programming
philosophy realm: late-binding vs. early binding. He considers the technique
to be late-binding and therefore to be avoided (which is usually true:
late-binding should be avoided in strongly typed languages such as VB).
However, he offers no tests to back up his contention that the technique
will perform worse because of the late-binding, so it mainly comes down to
the philosophical point.

That said, I prefer this technique not because of any supposed gain in
performance, but because of its simplicity: I do not have to create a
Command object, or build the Parameters collection, etc. (however, I would
imagine that building a collection of 20 or more Parameters would probably
yield a substantial degradation of performance in a non-compiled language
such as vbscript) I only use a Command object when I have a SQL Server
stored procedure that uses output parameters, or when I need to read the
Return value.

At the other end of the spectrum is the dynamic sql technique (a variation
of which you are using), which advocates love because it simplifies the
debugging process - you can use Response.Write to write the statement to the
browser window, and copy and paste the result into query analyzer. I dislike
this technique because:

1. I wrote a stored procedure so I would not HAVE to use dynamic sql. I
dislike having to deal with all the delimiter issues, etc. that cause so
many of the questions to this newsgroup.
2. It does impair performance, since a plan has to be created on the
database server that may or may not be cached. Admittedly, no user is likely
to notice this impairment in performance unless you need to respond to
hundreds of requests per minute ...
3. If you don't take the necessary steps (which you have not done in the
above example), you leave yourself vulnerable to a hacker using the SQL
Injection technique to create havoc in your database.

I will reiterate, however, that I consider what you are doing to be
completely wrong-headed: why build a Parameters collection AND create a
dynamic sql statement at the same time? You are defeating the purpose of
using the Command object. If you are going to create a dynamic sql
statement, why not simply do this:

set oconn=createobject ...
oconn.open ...
sSQL = "BuildColoursMenu '" & CStr(strStockID) & "'"
'**********************************************
'uncomment the following lines if you have a problem
'response.write sSQL
'response.end
'**********************************************
Set oRSv = oconn.execute(sSQL,,1)

Since you've built a dynamic sql statement, execute it as dynamic sql.

> Just as an aside query and one which I have just noticed, all of the
> above were used to populate a <SELECT> pop-up menu, which works fine,
> apart from the fact that the pop-up doesn't have a default value.
> I've never noticed it do this before, as I have checked and most of
> my pop-ups have been manual affairs and for some reason these will
> show a default value even if no SELECTED property is set.. Is the
> only way round it to set the first rec to resp.write SELECTED??
>

It would be best to start a new thread for unrelated questions. Note that
Aaron did not even realize it was asked.

The answer is yes.

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"

[Laphan]

Many thanks Aaron/Bob for your (always) valued feedback.

I promise I won't bother you (for a while at least ;0)

Many thanks

Rgds

Laphan


Bob Barrows <reb01501@NOyahoo.SPAMcom> wrote in message
news:ON#siNU#DHA.3436@tk2msftngp13.phx.gbl...
Laphan wrote:

> Firstly a note to Bob.
>
> I know you're going to get piffed with me, but I just to ask one last
> time. Which of the following methods in all of your honest opinions
> is the most efficient:
>
> a) The 'omitted Command' method, ie the one where you go straight from
> connection to recordset as per Bob's example below:
>
> set cn=createobject ...
> set rs=createobject ...
> parm=<some value>
> cn.sproc parm, rs
> 'process the results
> rs.close
> parm=<some new value>
> cn.sproc parm, rs
>
> b) The Parameter obj/collection method, which to me seems to create
> more work and confusion.
>
> c) I have re-checked myself (??) and found that the below method will
> work under OLEDB as long as I specify the right type for my
> parameters (so again, I ask: why bother with the Command object and

Parameters collection?):

>
> Set oCmd.ActiveConnection = oConn
> oCmd.CommandText = "BuildColoursMenu '" & CStr(strStockID) & "'"
> oCmd.CommandType = 4
> Set oRSv = oCmd.execute
>

I was not going to reply because I've already stated my preference. But I
did want to reiterate that eventually it does come down to personal
preference in the end.

I have read a book (Bill Vaughn's "ADO Examples and Best Practices") which
stated that based on SQL Profiler traces, the
"procedure-as-connection-method" technique is the most efficient, and showed
the results of some tests showing a 30% improvement in performance by using
this technique. However, my testing, as well as others, has failed to show
such a significant increase in performance. My tests always show some
improved performance, just never as high as 30%.

I have read another excellent book: David Sceppa's "Programming ADO" which
recommends against using the "procedure-as-connection-method" technique.
However, his reasons for not liking it are more in the programming
philosophy realm: late-binding vs. early binding. He considers the technique
to be late-binding and therefore to be avoided (which is usually true:
late-binding should be avoided in strongly typed languages such as VB).
However, he offers no tests to back up his contention that the technique
will perform worse because of the late-binding, so it mainly comes down to
the philosophical point.

That said, I prefer this technique not because of any supposed gain in
performance, but because of its simplicity: I do not have to create a
Command object, or build the Parameters collection, etc. (however, I would
imagine that building a collection of 20 or more Parameters would probably
yield a substantial degradation of performance in a non-compiled language
such as vbscript) I only use a Command object when I have a SQL Server
stored procedure that uses output parameters, or when I need to read the
Return value.

At the other end of the spectrum is the dynamic sql technique (a variation
of which you are using), which advocates love because it simplifies the
debugging process - you can use Response.Write to write the statement to the
browser window, and copy and paste the result into query analyzer. I dislike
this technique because:

1. I wrote a stored procedure so I would not HAVE to use dynamic sql. I
dislike having to deal with all the delimiter issues, etc. that cause so
many of the questions to this newsgroup.
2. It does impair performance, since a plan has to be created on the
database server that may or may not be cached. Admittedly, no user is likely
to notice this impairment in performance unless you need to respond to
hundreds of requests per minute ...
3. If you don't take the necessary steps (which you have not done in the
above example), you leave yourself vulnerable to a hacker using the SQL
Injection technique to create havoc in your database.

I will reiterate, however, that I consider what you are doing to be
completely wrong-headed: why build a Parameters collection AND create a
dynamic sql statement at the same time? You are defeating the purpose of
using the Command object. If you are going to create a dynamic sql
statement, why not simply do this:

set oconn=createobject ...
oconn.open ...
sSQL = "BuildColoursMenu '" & CStr(strStockID) & "'"
'**********************************************
'uncomment the following lines if you have a problem
'response.write sSQL
'response.end
'**********************************************
Set oRSv = oconn.execute(sSQL,,1)

Since you've built a dynamic sql statement, execute it as dynamic sql.

> Just as an aside query and one which I have just noticed, all of the
> above were used to populate a <SELECT> pop-up menu, which works fine,
> apart from the fact that the pop-up doesn't have a default value.
> I've never noticed it do this before, as I have checked and most of
> my pop-ups have been manual affairs and for some reason these will
> show a default value even if no SELECTED property is set.. Is the
> only way round it to set the first rec to resp.write SELECTED??
>

It would be best to start a new thread for unrelated questions. Note that
Aaron did not even realize it was asked.

The answer is yes.

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"