ASP Speed Tricks

[Shailesh Humbad]

I just posted an article I wrote called ASP Speed Tricks. It covers
techniques to optimize output of database data in HTML, for
both simple tables and complex tables. More advanced ASP authors might
be interested in the complex table optimizations. Please check it out at:

http://www.somacon.com/aspdocs/

Hope you enjoy,
Shailesh

Please reply at http://www.somacon.com/contact.php

[Chris Hohmann]

"Shailesh Humbad" <hu******@hotma il.com> wrote in message
news:IQ******** **************@ twister.columbu s.rr.com...

I just posted an article I wrote called ASP Speed Tricks. It covers
techniques to optimize output of database data in HTML, for
both simple tables and complex tables. More advanced ASP authors might be interested in the complex table optimizations. Please check it out at:
http://www.somacon.com/aspdocs/

Hope you enjoy,
Shailesh

Please reply at http://www.somacon.com/contact.php

Taking into account the excellent points made in this thread, here's
some code in defense of GetString

<%
Dim sql,cn,rs,timeG S,timeRS
Dim fld0,fld1,fld2, fld3
sql = "tblData"

timeGS=Timer
Response.Write "<pre>"
Set cn = CreateObject("A DODB.Connection ")
cn.Open gConn '<-- Your OLEDB DSN-Less Connection String Here
Set rs = cn.Execute(sql, ,&H2)
Do While Not rs.EOF
Response.Write rs.GetString(2, 30,vbTab,vbCRLF )
Loop
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
Response.Write "</pre>"
timeGS = Timer - timeGS

timeRS = Timer
Set cn = CreateObject("A DODB.Connection ")
cn.Open gConn '<-- Your OLEDB DSN-Less Connection String Here
Set rs = cn.Execute(sql, ,&H2)
Set fld0 = rs(0)
Set fld1 = rs(1)
Set fld2 = rs(2)
Set fld3 = rs(3)
Response.Write "<pre>"
Do While Not rs.EOF
Response.Write rs(0)
Response.Write vbTab
Response.Write rs(1)
Response.Write vbTab
Response.Write rs(2)
Response.Write vbTab
Response.Write rs(3)
Response.Write vbTab
Response.Write vbCRLF
rs.MoveNext
Loop
Response.Write "</pre>"
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
timeRS = Timer - timeRS

Response.Clear
Response.Write "<br>GetStr ing: " & timeGS
Response.Write "<br>Record set: " & timeRS
%>

Results:
GetString: 2.324219
Recordset: 4.566406

The GetString method ran nearly twice as fast (1.965x) as the Recordset
method in the 20,000 record scenario.

Notes:
1. The key thing to note is that you can use the rows parameter of the
GetString method to "burst" output in chunks. A little empirical testing
showed that in this scenario, 30 rows at a time seemed optimal. I'm sure
there's some black magic formula that defines the ideal size to make
best use of the Response.Write optimizations available in VBScript, I
just have no idea what that formula is. If someone could provide insight
into this, I'd be much obliged. Sorry, no such optimization exists in
JScript (early bound vs. late bound).
2. I let the Recordset method go second to insure it had the benefit of
connection pooling. But it's a small point and not worth quibbling over.
3. There are ways to apply additional formatting at the cell level when
using the GetString method. Namely, use set the DataFormat property of
each field using the StdDataFormat object available via the
MSSTDFMT.DLL. However, I'm of the opinion that formatting should be
handles via stylesheets, i.e.. declare a class attribute for the
embodying table/block tag and take care of formatting in an external
CSS.
4. Your complex table example was a little difficult to follow, however
if I understand it correctly you wanted to present both detailed data
and summary/aggregate data in the same listing. One solution that you
did not investigate would be to return a result set which is the union
of the detail data and the summary/aggregate data. This has the
following consequences. First it eliminates the redundant data
associated with an joined result set. Secondly, it keeps the data
processing where it belongs, namely at the database. Thirdly, is does
not change the nature of what's returned (a result set is a result set
is a result set). As such, all the same benefits/optimizations of the
GetString method continue to apply.
5. I would have liked to have used sp as connection, but in this case
that would actually represent an additional translation layer. Sorry
Bob.
6. Not that is should matter, since the recordset is being called in
exactly the same way in both scenarios, but for completeness here's what
I used to represent tblData. Note, Numbers is a table which contains
numbers from 1 to 8000 (inclusive). The database was SQL Server 2000.

CREATE TABLE [dbo].[Numbers]( [Number] [smallint] IDENTITY (1, 1) NOT
NULL)

CREATE VIEW dbo.tblData
AS
SELECT TOP 20000
N1.Number AS Field1,
N2.Number AS Field2,
CAST('FOO' AS TEXT(50)) AS Field3,
CAST('BAR' AS TEXT(50)) AS Field4
FROM
dbo.Numbers N1 CROSS JOIN
dbo.Numbers N2

HTH
-Chris

[Chris Hohmann]

"Chris Hohmann" <hohmannATyahoo DOTcom> wrote in message
news:%2******** *******@TK2MSFT NGP11.phx.gbl.. .

5. I would have liked to have used sp as connection, but in this case
that would actually represent an additional translation layer. Sorry
Bob.

That should read:
5. I would have liked to have used sp as connection method...

[David C Holley]

I had read somewhere that one of the techniques to optimize performance
is to reduce the number of response.write statements. If I recall
correctly, there is a performance hit (however minor) for each statement
which can add up if there's a large number of statements. As such, I
build my HTML and place it into a variable which at the end of the
script is written using a single response.write statement.

So the example provided would become...

strHTML = ""

Do While Not rs.EOF
strHTML = strHTML & rs(0)
strHTML = strHTML & vbTab
strHTML = strHTML & rs(1)
strHTML = strHTML & vbTab
strHTML = strHTML & rs(2)
strHTML = strHTML & vbTab
strHTML = strHTML & rs(3)
strHTML = strHTML & vbTab
strHTML = strHTML & vbCRLF
rs.MoveNext response.write strHTML

Granted I've never actually performed any tests to confirm the
optimization benefits of this, however I'm comfortable in assuming that
there is a benefit.

David H

Chris Hohmann wrote: "Shailesh Humbad" <hu******@hotma il.com> wrote in message
news:IQ******** **************@ twister.columbu s.rr.com...

I just posted an article I wrote called ASP Speed Tricks. It covers
techniques to optimize output of database data in HTML, for
both simple tables and complex tables. More advanced ASP authors

might

be interested in the complex table optimizations. Please check it out

at:

http://www.somacon.com/aspdocs/

Hope you enjoy,
Shailesh

Please reply at http://www.somacon.com/contact.php

Taking into account the excellent points made in this thread, here's
some code in defense of GetString

<%
Dim sql,cn,rs,timeG S,timeRS
Dim fld0,fld1,fld2, fld3
sql = "tblData"

timeGS=Timer
Response.Write "<pre>"
Set cn = CreateObject("A DODB.Connection ")
cn.Open gConn '<-- Your OLEDB DSN-Less Connection String Here
Set rs = cn.Execute(sql, ,&H2)
Do While Not rs.EOF
Response.Write rs.GetString(2, 30,vbTab,vbCRLF )
Loop
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
Response.Write "</pre>"
timeGS = Timer - timeGS

timeRS = Timer
Set cn = CreateObject("A DODB.Connection ")
cn.Open gConn '<-- Your OLEDB DSN-Less Connection String Here
Set rs = cn.Execute(sql, ,&H2)
Set fld0 = rs(0)
Set fld1 = rs(1)
Set fld2 = rs(2)
Set fld3 = rs(3)
Response.Write "<pre>"
Do While Not rs.EOF
Response.Write rs(0)
Response.Write vbTab
Response.Write rs(1)
Response.Write vbTab
Response.Write rs(2)
Response.Write vbTab
Response.Write rs(3)
Response.Write vbTab
Response.Write vbCRLF
rs.MoveNext
Loop
Response.Write "</pre>"
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
timeRS = Timer - timeRS

Response.Clear
Response.Write "<br>GetStr ing: " & timeGS
Response.Write "<br>Record set: " & timeRS
%>

Results:
GetString: 2.324219
Recordset: 4.566406

The GetString method ran nearly twice as fast (1.965x) as the Recordset
method in the 20,000 record scenario.

Notes:
1. The key thing to note is that you can use the rows parameter of the
GetString method to "burst" output in chunks. A little empirical testing
showed that in this scenario, 30 rows at a time seemed optimal. I'm sure
there's some black magic formula that defines the ideal size to make
best use of the Response.Write optimizations available in VBScript, I
just have no idea what that formula is. If someone could provide insight
into this, I'd be much obliged. Sorry, no such optimization exists in
JScript (early bound vs. late bound).
2. I let the Recordset method go second to insure it had the benefit of
connection pooling. But it's a small point and not worth quibbling over.
3. There are ways to apply additional formatting at the cell level when
using the GetString method. Namely, use set the DataFormat property of
each field using the StdDataFormat object available via the
MSSTDFMT.DLL. However, I'm of the opinion that formatting should be
handles via stylesheets, i.e.. declare a class attribute for the
embodying table/block tag and take care of formatting in an external
CSS.
4. Your complex table example was a little difficult to follow, however
if I understand it correctly you wanted to present both detailed data
and summary/aggregate data in the same listing. One solution that you
did not investigate would be to return a result set which is the union
of the detail data and the summary/aggregate data. This has the
following consequences. First it eliminates the redundant data
associated with an joined result set. Secondly, it keeps the data
processing where it belongs, namely at the database. Thirdly, is does
not change the nature of what's returned (a result set is a result set
is a result set). As such, all the same benefits/optimizations of the
GetString method continue to apply.
5. I would have liked to have used sp as connection, but in this case
that would actually represent an additional translation layer. Sorry
Bob.
6. Not that is should matter, since the recordset is being called in
exactly the same way in both scenarios, but for completeness here's what
I used to represent tblData. Note, Numbers is a table which contains
numbers from 1 to 8000 (inclusive). The database was SQL Server 2000.

CREATE TABLE [dbo].[Numbers]( [Number] [smallint] IDENTITY (1, 1) NOT
NULL)

CREATE VIEW dbo.tblData
AS
SELECT TOP 20000
N1.Number AS Field1,
N2.Number AS Field2,
CAST('FOO' AS TEXT(50)) AS Field3,
CAST('BAR' AS TEXT(50)) AS Field4
FROM
dbo.Numbers N1 CROSS JOIN
dbo.Numbers N2

HTH
-Chris

[Shailesh Humbad]

Mr. Holley, you need to read my article. That is exactly the naive
method that is obscenely slow. I have to say it again, it is OBSCENELY
slow.

http://www.somacon.com/aspdocs/

David C Holley wrote:

I had read somewhere that one of the techniques to optimize performance
is to reduce the number of response.write statements. If I recall
correctly, there is a performance hit (however minor) for each statement
which can add up if there's a large number of statements. As such, I
build my HTML and place it into a variable which at the end of the
script is written using a single response.write statement.

So the example provided would become...

strHTML = ""

> Do While Not rs.EOF
> strHTML = strHTML & rs(0)
> strHTML = strHTML & vbTab
> strHTML = strHTML & rs(1)
> strHTML = strHTML & vbTab
> strHTML = strHTML & rs(2)
> strHTML = strHTML & vbTab
> strHTML = strHTML & rs(3)
> strHTML = strHTML & vbTab
> strHTML = strHTML & vbCRLF
> rs.MoveNext

response.write strHTML

Granted I've never actually performed any tests to confirm the
optimization benefits of this, however I'm comfortable in assuming that
there is a benefit.

David H

Chris Hohmann wrote:

"Shailesh Humbad" <hu******@hotma il.com> wrote in message
news:IQ******** **************@ twister.columbu s.rr.com...

I just posted an article I wrote called ASP Speed Tricks. It covers
techniques to optimize output of database data in HTML, for
both simple tables and complex tables. More advanced ASP authors

might

be interested in the complex table optimizations. Please check it out

at:

http://www.somacon.com/aspdocs/

Hope you enjoy,
Shailesh

Please reply at http://www.somacon.com/contact.php

Taking into account the excellent points made in this thread, here's
some code in defense of GetString

<%
Dim sql,cn,rs,timeG S,timeRS
Dim fld0,fld1,fld2, fld3
sql = "tblData"

timeGS=Timer
Response.Write "<pre>"
Set cn = CreateObject("A DODB.Connection ")
cn.Open gConn '<-- Your OLEDB DSN-Less Connection String Here
Set rs = cn.Execute(sql, ,&H2)
Do While Not rs.EOF
Response.Write rs.GetString(2, 30,vbTab,vbCRLF )
Loop
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
Response.Write "</pre>"
timeGS = Timer - timeGS

timeRS = Timer
Set cn = CreateObject("A DODB.Connection ")
cn.Open gConn '<-- Your OLEDB DSN-Less Connection String Here
Set rs = cn.Execute(sql, ,&H2)
Set fld0 = rs(0)
Set fld1 = rs(1)
Set fld2 = rs(2)
Set fld3 = rs(3)
Response.Write "<pre>"
Do While Not rs.EOF
Response.Write rs(0)
Response.Write vbTab
Response.Write rs(1)
Response.Write vbTab
Response.Write rs(2)
Response.Write vbTab
Response.Write rs(3)
Response.Write vbTab
Response.Write vbCRLF
rs.MoveNext
Loop
Response.Write "</pre>"
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
timeRS = Timer - timeRS

Response.Clear
Response.Write "<br>GetStr ing: " & timeGS
Response.Write "<br>Record set: " & timeRS
%>

Results:
GetString: 2.324219
Recordset: 4.566406

The GetString method ran nearly twice as fast (1.965x) as the Recordset
method in the 20,000 record scenario.

Notes:
1. The key thing to note is that you can use the rows parameter of the
GetString method to "burst" output in chunks. A little empirical testing
showed that in this scenario, 30 rows at a time seemed optimal. I'm sure
there's some black magic formula that defines the ideal size to make
best use of the Response.Write optimizations available in VBScript, I
just have no idea what that formula is. If someone could provide insight
into this, I'd be much obliged. Sorry, no such optimization exists in
JScript (early bound vs. late bound).
2. I let the Recordset method go second to insure it had the benefit of
connection pooling. But it's a small point and not worth quibbling over.
3. There are ways to apply additional formatting at the cell level when
using the GetString method. Namely, use set the DataFormat property of
each field using the StdDataFormat object available via the
MSSTDFMT.DLL. However, I'm of the opinion that formatting should be
handles via stylesheets, i.e.. declare a class attribute for the
embodying table/block tag and take care of formatting in an external
CSS.
4. Your complex table example was a little difficult to follow, however
if I understand it correctly you wanted to present both detailed data
and summary/aggregate data in the same listing. One solution that you
did not investigate would be to return a result set which is the union
of the detail data and the summary/aggregate data. This has the
following consequences. First it eliminates the redundant data
associated with an joined result set. Secondly, it keeps the data
processing where it belongs, namely at the database. Thirdly, is does
not change the nature of what's returned (a result set is a result set
is a result set). As such, all the same benefits/optimizations of the
GetString method continue to apply.
5. I would have liked to have used sp as connection, but in this case
that would actually represent an additional translation layer. Sorry
Bob.
6. Not that is should matter, since the recordset is being called in
exactly the same way in both scenarios, but for completeness here's what
I used to represent tblData. Note, Numbers is a table which contains
numbers from 1 to 8000 (inclusive). The database was SQL Server 2000.

CREATE TABLE [dbo].[Numbers]( [Number] [smallint] IDENTITY (1, 1) NOT
NULL)

CREATE VIEW dbo.tblData
AS
SELECT TOP 20000
N1.Number AS Field1,
N2.Number AS Field2,
CAST('FOO' AS TEXT(50)) AS Field3,
CAST('BAR' AS TEXT(50)) AS Field4
FROM
dbo.Numbers N1 CROSS JOIN
dbo.Numbers N2

HTH
-Chris

[Shailesh Humbad]

I have taken the suggestions from the thread into account and revised
the article. If you have the interest, please check it out at:

http://www.somacon.com/aspdocs/

Mr. Hohmann, I tested your GetString technique and found it was indeed
faster. It was not 2x like your example, because your example did not
use the field references.

I tested Mr. Nierop's suggestion of using OLE DB, and found it was
about 10% faster for simple table output.

I also tried Mr. Barrows' suggestion of using "Set .ActiveConnecti on =
objCN" instead of ".ActiveConnect ion = objCN". Running this in a loop,
the Set call actually turned out to be twice as slow, but both methods
were relatively fast. I think my testing procedure of looping may be
wrong. In any case, my impression from the book I'm reading is that the
property will use the existing connection if it is set to a Connection
object that is already opened. Do you have a test that shows a new
connection being made when Set is not used?

Trying Mr. Barrows' suggestion of using a stored procedure was also not
fruitful, and it actually ended up taking longer than using the
recordset to create the query each time. I created a query called
"dummyquery " in the database, and executed it using "objCN.dummyque ry
objField1, objRS2". This ran in about 5 seconds, as opposed to 4
seconds for "objRS.Open "SELECT ...", and 1 second for the prepared
command "objRS.Open objCmd". Any ideas?

I revised the complex tables introduction, and it should be more
understandable now. The UNION clause can not be used because it simply
concatenates the results of the sub-queries. I want to execute a
secondary query for each row of a primary query. UNION also requires
that the number of fields in each combined sub-query be the same.

Again, thanks for all your comments.

Shailesh

Reply at:
http://www.somacon.com/contact.php

Chris Hohmann wrote:

"Shailesh Humbad" <hu******@hotma il.com> wrote in message
news:IQ******** **************@ twister.columbu s.rr.com...

I just posted an article I wrote called ASP Speed Tricks. It covers
techniques to optimize output of database data in HTML, for
both simple tables and complex tables. More advanced ASP authors

might

be interested in the complex table optimizations. Please check it out

at:

http://www.somacon.com/aspdocs/

Hope you enjoy,
Shailesh

Please reply at http://www.somacon.com/contact.php

Taking into account the excellent points made in this thread, here's
some code in defense of GetString

<%
Dim sql,cn,rs,timeG S,timeRS
Dim fld0,fld1,fld2, fld3
sql = "tblData"

timeGS=Timer
Response.Write "<pre>"
Set cn = CreateObject("A DODB.Connection ")
cn.Open gConn '<-- Your OLEDB DSN-Less Connection String Here
Set rs = cn.Execute(sql, ,&H2)
Do While Not rs.EOF
Response.Write rs.GetString(2, 30,vbTab,vbCRLF )
Loop
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
Response.Write "</pre>"
timeGS = Timer - timeGS

timeRS = Timer
Set cn = CreateObject("A DODB.Connection ")
cn.Open gConn '<-- Your OLEDB DSN-Less Connection String Here
Set rs = cn.Execute(sql, ,&H2)
Set fld0 = rs(0)
Set fld1 = rs(1)
Set fld2 = rs(2)
Set fld3 = rs(3)
Response.Write "<pre>"
Do While Not rs.EOF
Response.Write rs(0)
Response.Write vbTab
Response.Write rs(1)
Response.Write vbTab
Response.Write rs(2)
Response.Write vbTab
Response.Write rs(3)
Response.Write vbTab
Response.Write vbCRLF
rs.MoveNext
Loop
Response.Write "</pre>"
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
timeRS = Timer - timeRS

Response.Clear
Response.Write "<br>GetStr ing: " & timeGS
Response.Write "<br>Record set: " & timeRS
%>

Results:
GetString: 2.324219
Recordset: 4.566406

The GetString method ran nearly twice as fast (1.965x) as the Recordset
method in the 20,000 record scenario.

Notes:
1. The key thing to note is that you can use the rows parameter of the
GetString method to "burst" output in chunks. A little empirical testing
showed that in this scenario, 30 rows at a time seemed optimal. I'm sure
there's some black magic formula that defines the ideal size to make
best use of the Response.Write optimizations available in VBScript, I
just have no idea what that formula is. If someone could provide insight
into this, I'd be much obliged. Sorry, no such optimization exists in
JScript (early bound vs. late bound).
2. I let the Recordset method go second to insure it had the benefit of
connection pooling. But it's a small point and not worth quibbling over.
3. There are ways to apply additional formatting at the cell level when
using the GetString method. Namely, use set the DataFormat property of
each field using the StdDataFormat object available via the
MSSTDFMT.DLL. However, I'm of the opinion that formatting should be
handles via stylesheets, i.e.. declare a class attribute for the
embodying table/block tag and take care of formatting in an external
CSS.
4. Your complex table example was a little difficult to follow, however
if I understand it correctly you wanted to present both detailed data
and summary/aggregate data in the same listing. One solution that you
did not investigate would be to return a result set which is the union
of the detail data and the summary/aggregate data. This has the
following consequences. First it eliminates the redundant data
associated with an joined result set. Secondly, it keeps the data
processing where it belongs, namely at the database. Thirdly, is does
not change the nature of what's returned (a result set is a result set
is a result set). As such, all the same benefits/optimizations of the
GetString method continue to apply.
5. I would have liked to have used sp as connection, but in this case
that would actually represent an additional translation layer. Sorry
Bob.
6. Not that is should matter, since the recordset is being called in
exactly the same way in both scenarios, but for completeness here's what
I used to represent tblData. Note, Numbers is a table which contains
numbers from 1 to 8000 (inclusive). The database was SQL Server 2000.

CREATE TABLE [dbo].[Numbers]( [Number] [smallint] IDENTITY (1, 1) NOT
NULL)

CREATE VIEW dbo.tblData
AS
SELECT TOP 20000
N1.Number AS Field1,
N2.Number AS Field2,
CAST('FOO' AS TEXT(50)) AS Field3,
CAST('BAR' AS TEXT(50)) AS Field4
FROM
dbo.Numbers N1 CROSS JOIN
dbo.Numbers N2

HTH
-Chris

[Chris Hohmann]

Responses inline:
"Shailesh Humbad" <hu******@hotma il.com> wrote in message
news:8R******** **************@ twister.columbu s.rr.com...

I have taken the suggestions from the thread into account and revised
the article. If you have the interest, please check it out at:

http://www.somacon.com/aspdocs/

Mr. Hohmann, I tested your GetString technique and found it was indeed
faster. It was not 2x like your example, because your example did not
use the field references.
Whoops. I went through the trouble of declaring the field references but
did not use them. I revised my code and got results similar to the ones
posted in your revised article. Although the wording of the results
should be as follows:

GetString is about 25-67% faster than "Optimized Looping".

OR

"Optimized Looping" is about 20-40% slower than GetString.
I revised the complex tables introduction, and it should be more
understandable now. The UNION clause can not be used because it simply concatenates the results of the sub-queries. I want to execute a
secondary query for each row of a primary query. UNION also requires
that the number of fields in each combined sub-query be the same.
Your ComplexTable3 example generates a summary line for every record in
tblData. I think what you intended was to print a summary line for each
distinct value in Field1. I've modified your code accordingly and also
presented the union query solution I discussed in my prior post. My
results show that the union query solution runs 14 times faster than the
ComplexTable3 solution.

<%
Function timeGR
Dim sql,cn,rs,arr,j ,jMax
timeGR=Timer
Set cn = CreateObject("A DODB.Connection ")
Set rs = CreateObject("A DODB.Recordset" )
cn.Open gConn
cn.spDataComple x rs
arr = rs.GetRows
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
jMax = Ubound(arr,2)
Response.Write "<table class='datatabl e'>"
For j = 0 To jMax
If arr(1,j)<0 Then
Response.Write "<tr><th colspan='4'>"
Response.Write Abs(arr(1,j))
Response.Write "</th></tr>"
End If
Response.write "<tr><td>"
Response.write arr(0,j)
Response.write "</td><td>"
Response.write arr(1,j)
Response.write "</td><td>"
Response.write arr(2,j)
Response.write "</td><td>"
Response.write arr(3,j)
Response.write "</td></tr>"
Next
Response.Write "</table>"
timeGR = Timer - timeGR
End Function

Function timeRS
Dim StartTime, EndTime

StartTime = Timer

Dim objCN ' ADO Connection object
Dim objRS ' ADO Recordset object
Dim strsql ' SQL query string
Dim objRS2 ' Another ADO Recordset object
Dim objField0, objField1, objField2, objField3

' Create a connection object
Set objCN = Server.CreateOb ject("ADODB.Con nection")

' Connect to the data source
objCN.Connectio nString = gConn
objCN.Open

' Create the a recordset object, and initialize it
Set objRS = Server.CreateOb ject("ADODB.Rec ordSet")
With objRS
.CursorType = adOpenForwardOn ly
.LockType = adLockReadOnly
.CursorLocation = adUseServer
Set .ActiveConnecti on = objCN
End With
' Create the second recordset object, and initialize it
Set objRS2 = Server.CreateOb ject("ADODB.Rec ordSet")
With objRS2
.CursorType = adOpenForwardOn ly
.LockType = adLockReadOnly
.CursorLocation = adUseServer
Set .ActiveConnecti on = objCN
End With

' Execute the SQL query
objRS.Open "SELECT Field1,Field2,F ield3,Field4 FROM tblData"

' Set up field references after opening recordset
Set objField0 = objRS(0)
Set objField1 = objRS(1)
Set objField2 = objRS(2)
Set objField3 = objRS(3)
' Write out the results in a table
Response.write "<table class="&Chr(34) &"datatable"&Ch r(34)&">"
Field1=-1
Do While Not objRS.EOF
' Use the pre-prepared Recordset object to issue the dummy query
If objRS(0) <> Field1 Then
Response.write "<tr><th colspan=4>"
objRS2.Open "SELECT COUNT(*) FROM tblData WHERE Field1="&objRS( 0)
Response.write objRS2(0)
objRS2.Close
Response.write "</th></tr>"
Field1 = objRS(0)
End If
Response.write "<tr><td>"
Response.write objField0
Response.write "</td><td>"
Response.write objField1
Response.write "</td><td>"
Response.write objField2
Response.write "</td><td>"
Response.write objField3
Response.write "</td></tr>"
Response.write vbCrLf
objRS.MoveNext

Loop
Response.write "</table>"

objRS.Close
objCN.Close
Set objCN = Nothing
Set objRS = Nothing
Set objRS2 = Nothing

EndTime = Timer
timeRS = EndTime-StartTime
End Function

Dim o
o = "<br>Record set: " & timeRS & "<br>GetRow s: " & timeGR
Response.Clear
Response.Write o
%>

Here are the revised/new definitions of the database objects I used to
simulate your examples. As before, the database is Microsoft SQL Server
2000 and the Numbers table contains numbers from 1 to 8000 (inclusive):

CREATE TABLE [dbo].[Numbers]( [Number] [smallint] IDENTITY (1, 1) NOT
NULL)

CREATE VIEW dbo.tblData
AS
SELECT TOP 20000
N % 100 AS Field1,
N AS Field2,
CAST('FOO' AS TEXT(50)) AS Field3,
CAST('BAR' AS TEXT(50)) AS Field4
FROM
(
SELECT
N1.Number*200-1+N2.Number AS N
FROM
dbo.Numbers N1 CROSS JOIN
dbo.Numbers N2
WHERE
N1.Number <= 100 AND
N2.Number <=200
) AS Numbers20000
ORDER BY Field1

CREATE PROCEDURE spDataComplex
AS
SELECT
Field1,
Field2,
Field3,
Field4
FROM
tblData
UNION ALL
SELECT
Field1,
0 - Count(*),
'',
''
FROM
tblData
GROUP BY
Field1
ORDER BY
Field1, Field2
Again, thanks for all your comments.

You're welcome

-Chris

[Shailesh Humbad]

It's all about adjusting paradigms :) I've adjusted mine half a dozen
times already through the feedback from this thread.

I have been using Access, or more precisely Jet. But I just installed
MSDE (SQL Server 2000 Desktop Edition) and tested it too. In these
tests, I am modifying a few lines in my script called complextable3.a sp.

Here are the test results for Jet and ODBC:

' TEST 1: Prepared Command Object pointing to stored Query using Jet
' objCmd.CommandT ext = "dummyquery "
' .CommandType = adCmdStoredProc , .Prepared = True
' **2.1 seconds**
objParam.Value = objRS(0)
objRS2.Open objCmd

TEST 2: Same as TEST 1, but using ODBC
** 1.1 seconds **

' TEST 3: Prepared Command Object as text query using Jet
' objCmd.CommandT ext = "SELECT COUNT(*) FROM tblData WHERE Field1=?",
' .CommandType = adCmdText, .Prepared = True
' **2.1 seconds**
objParam.Value = objRS(0)
objRS2.Open objCmd

TEST 4: Same as TEST 3, but using ODBC
** 1.1 seconds**

-----------------------------------------------------

' TEST 5: objCN.Execute of stored procedure named "dummyquery " using Jet
' 3.4 seconds
Set objRS2 = objCN.Execute(" EXECUTE dummyquery "&objRS(0))

' TEST 6: Same as TEST 5, but using ODBC
** 5.5 seconds **

' TEST 7: stored procedure as function call using Jet
' ** 3.4 seconds **
objCN.dummyquer y objRS2, objRS(0)

' TEST 8: Same as TEST 7 but using Jet
' ** 5.5 seconds **

-----------------------------------------------------
SQL Server TEST 1:
' objCmd.CommandT ext = "dummyquery "
' .CommandType = adCmdStoredProc , .Prepared = True
' **5 to 25 seconds**
objParam.Value = objRS(0)
objRS2.Open objCmd

SQL Server TEST 2:
' objCmd.CommandT ext = "SELECT COUNT(*) FROM tblData WHERE Field1=?",
' .CommandType = adCmdText, .Prepared = True
' **5 to 25 seconds**
objParam.Value = objRS(0)
objRS2.Open objCmd

SQL Server TEST 3:
' **5.4 seconds**
Set objRS2 = objCN.Execute(" EXECUTE dummyquery "&objRS(0))

SQL Server TEST 4:
' **5.4 seconds**
objCN.dummyquer y objRS2, objRS(0)
Surprising results? First, with Jet or ODBC, the results show that
using a prepared command object is the fastest method, with either the
Native Jet OLE DB provider or the ODBC Provider. The ODBC provider is
*twice as fast* as the Jet provider in this case, clocking in at 1.1
seconds! Furthermore, there is no difference between defining the query
in ASP versus saving the query in the database.

Using the connection object to execute the identical stored query was
*slower* using either of two techniques, and either Provider. In this
case, there was also no difference between defining the query in ASP
versus executing the query saved in the database.

All of the SQL Server tests were slower in comparision to Jet or ODBC.
Worse, using a command object defined in ASP was unreliable, taking
anywhere from 5-25 seconds, and even timing out sometimes. This is for
either a ASP-defined query or a Stored Procedure. On the other hand,
using the connection object to execute either a stored procedure or an
ASP-defined query was reliably about 5 seconds. However, this is slower
than Jet or ODBC.

I'm not sure that these results mean anything, because I have tested
just a single scenario. But the results do debunk the conventional
wisdom that SQL Server > Jet > ODBC always. In fact, ODBC can sometimes
be faster than both Jet and SQL Server. It's very likely that this is a
fluke of the test, so take the results with a grain of salt. I really
wish I could explain *why* the results are the way they are, but it
seems like that would be impossible without looking at some source code.

Shailesh

Bob Barrows wrote:

Shailesh Humbad wrote:

I also tried Mr. Barrows' suggestion of using "Set .ActiveConnecti on =
objCN" instead of ".ActiveConnect ion = objCN". Running this in a
loop, the Set call actually turned out to be twice as slow, but both
methods were relatively fast. I think my testing procedure of
looping may be wrong. In any case, my impression from the book I'm
reading is that the property will use the existing connection if it
is set to a Connection object that is already opened. Do you have a
test that shows a new connection being made when Set is not used?

I just ran some tests myself and it seems as if I have been the victim of
either obsolete or originally incorrect information.

This code:
cn.Open
rs.ActiveConnec tion=cn
cn.Close
rs.Open sSQL,,,,adcmdte xt

results in this error: The connection cannot be used to perform this
operation. It is either closed or invalid in this context.

So obviously a new connection is not being spawned. Sorry about that. I must
adjust some of my paradigms.

I don't know why the Set keyword slows things down. It shouldn't ...

Trying Mr. Barrows' suggestion of using a stored procedure was also
not fruitful, and it actually ended up taking longer than using the
recordset to create the query each time. I created a query called
"dummyquery " in the database, and executed it using "objCN.dummyque ry
objField1, objRS2". This ran in about 5 seconds, as opposed to 4
seconds for "objRS.Open "SELECT ...", and 1 second for the prepared
command "objRS.Open objCmd". Any ideas?

This is the opposite of the results I got from my tests. Are you using
Access or SQL Server?

Bob Barrows

[Shailesh Humbad]

I figured it out. SQL Server was slower on the sub query because it was
being performed using the same connection object as the main query. By
creating a second connection object, and using a prepared query on the
second connection object, the time to run the script goes down to 1.2
seconds, which is comparable to what was being acheived with ODBC.
Maybe there's an unwritten rule for SQL Server optimization under ADO,
that each opened recordset should have it's own connection for maximum
speed?

Here's the code containing all the test cases. The optimized case is
uncommented out. For this particular sub query, which returns only a
single value, it might run even faster by using an output return
parameter on the sub query, rather than creating a full record set.

<%@ Language=VBScri pt %>
<% Option Explicit %>
<html>
<head>
<style type="text/css">\
/* set the table layout to fixed width */
table.datatable { table-layout: fixed; width: 400px; }
table.datatable td { width: 100px; }
</style>
</head>
<body>
<%
Dim StartTime, EndTime

StartTime = Timer

Dim objCN ' ADO Connection object
Dim objRS ' ADO Recordset object
Dim strsql ' SQL query string
Dim objRS2 ' Another ADO Recordset object
Dim objCmd ' ADO Command object
Dim objParam ' ADO Parameter object
Dim objField0, objField1, objField2, objField3
Dim objCN2

' Create a connection object
Set objCN = Server.CreateOb ject("ADODB.Con nection")
Set objCN2 = Server.CreateOb ject("ADODB.Con nection")

' Connect to the data source
'objCN.Connecti onString = "Provider=Micro soft.Jet.OLEDB. 4.0;"&_
' "Data Source=C:\Inetp ub\wwwroot\data \test.mdb;"&_
' "Persist Security Info=False"
'objCN.Connecti onString = "DSN=datasource "
'objCN.Connecti onString = "DSN=datasource sql;User ID=;Password=;"
objCN.Connectio nString = "Provider=SQLOL EDB.1;"&_
"User ID=;Data Source=localhos t;"&_
"Initial Catalog=sqltest ;Password=;"
objCN.Open
objCN2.Connecti onString = "Provider=SQLOL EDB.1;"&_
"User ID=;Data Source=localhos t;"&_
"Initial Catalog=sqltest ;Password=;"
objCN2.Open

' Create the a recordset object, and initialize it
Set objRS = Server.CreateOb ject("ADODB.Rec ordSet")
With objRS
.CursorType = adOpenForwardOn ly
.LockType = adLockReadOnly
.ActiveConnecti on = objCN
.CursorLocation = adUseServer
.Source = "SELECT Field1,Field2,F ield3,Field4 FROM tblData"
End With
' Create the second recordset object, and initialize it
Set objRS2 = Server.CreateOb ject("ADODB.Rec ordSet")
With objRS2
.CursorType = adOpenForwardOn ly
.LockType = adLockReadOnly
.ActiveConnecti on = objCN2
.CursorLocation = adUseServer
End With
' Create command object
Set objCmd = Server.CreateOb ject("ADODB.Com mand")
With objCmd
.ActiveConnecti on = objCN2
.Prepared = True
.CommandType = adCmdText
.CommandText = "SELECT COUNT(*) FROM tblData WHERE Field1=?"
'.CommandType = adCmdStoredProc
'.CommandText = "dummyquery "
End With
' Set up Parameter object
Set objParam = objCmd.CreatePa rameter("Param1 ",adInteger,adP aramInput,4)
' Append the parameter to the Command object's parameters collection
objCmd.Paramete rs.Append objParam

' Execute the SQL query
objRS.Open

' Set up field references after opening recordset
Set objField0 = objRS(0)
Set objField1 = objRS(1)
Set objField2 = objRS(2)
Set objField3 = objRS(3)

' Write out the results in a table
Response.write "<table class="&Chr(34) &"datatable"&Ch r(34)&">"
Do While Not objRS.EOF
Response.write "<tr><td>"
Response.write objField0
Response.write "</td><td>"
Response.write objField1
Response.write "</td><td>"
Response.write objField2
Response.write "</td><td>"
Response.write objField3
Response.write "</td></tr>"
Response.write vbCrLf

Response.write "<tr><th colspan=4>"

' Prepared Command Object
objParam.Value = objRS(0)
objRS2.Open objCmd

'objRS2.Open "SELECT COUNT(*) FROM tblData"&_
" WHERE Field1="&objRS( 0)

'Set objRS2 = objCN.Execute(" EXECUTE dummyquery "&objRS(0))
'Set objRS2 = objCN2.Execute( "{call "&_
" dummyquery('"&o bjRS(0)&"')}")
'Set objRS2 = objCN.Execute(" SELECT COUNT(*) FROM tblData"&_
" WHERE Field1="&objRS( 0))
'objCN.dummyque ry objRS(0), objRS2

Response.write objRS2(0)
objRS2.Close
Response.write "</th></tr>"

objRS.MoveNext
Loop
Response.write "</table>"

objRS.Close
objCN.Close
Set objCN = Nothing
objCN2.Close
Set objCN2 = Nothing
Set objRS = Nothing
Set objRS2 = Nothing
Set objCmd = Nothing

EndTime = Timer
Response.write "<p>process ing took "&(EndTime-StartTime)&" seconds<p>&nbsp ;"
%>
</body>
</html>