[thelist] ADO cursor - best solution for count and pagethroughprocedure
Ken Schaefer
ken at adOpenStatic.com
Wed Mar 31 20:33:25 CST 2004
Hi,
I originally did these tests as part of an article to be published on
ASPToday, that never happened due to Wrox going bust. So it's not really in
a format suitable for posting to a list, and since I don't really do Classic
ASP anymore, I'm not really going to spend a few days sorting it all out.
So, you're welcome to what I have, but you'll need to work it out yourself.
I have the article, plus various pages of code, plus diagrams etc. The
following are the two pages of code used for the Access tests (I also did
additional tests using SQL Server that used sproc based paging). The MS WAST
(Web Application Stress Tool) was used to fire requests off at IIS server.
The IIS server in question was IIS 5.0 on Windows 2000. IIS 6.0 would
probably give better numbers.
====== GetRows Code ========
<%@ Language=VBScript EnableSessionState=False %>
<%
Option Explicit
Response.Buffer = True
Server.ScriptTimeout = 0
Dim objConn
Dim objRS
Dim strSQL
Dim strDB
Dim strConnect
Dim intPage
Dim objField
Dim i
Dim arrResults
Dim intTotalRecs
strDB = Request.QueryString("DB")
strConnect = GetConnString(strDB)
' Request.QueryString("Page") is between 1 & 5 (for adOpenStatic Test)
' Since we are using .Move() we need to subtract 1 to give 0 -> 4
intPage = Request.QueryString("Page") - 1
strSQL = _
"SELECT OrderID, CustomerID, EmployeeID, OrderDate " & _
"FROM Orders " & _
"ORDER BY " & GetOrderBy()
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionTimeout = 0
objConn.CommandTimeout = 0
Do While objConn.State = adStateClosed
On Error Resume Next
objConn.Open strConnect
On Error Goto 0
i = i + 1
If i > 20 then
Exit Do
End If
Loop
Set objRS = objConn.Execute(strSQL)
objRS.Move(20 * intPage)
' objRS.Move(50 * intPage)
' objRS.Move(160 * intPage)
If not objRS.EOF then
arrResults = objRS.GetRows(20)
' arrResults = objRS.GetRows(50)
' arrResults = objRS.GetRows(160)
End If
strSQL = "SELECT COUNT(*) FROM Orders"
Set objRS = objConn.Execute(strSQL)
intTotalRecs = objRS(0).Value
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
If isArray(arrResults) then
For i = 0 to UBound(arrResults, 2)
Response.Write(arrResults(0,i) & "-" & arrResults(1,i) & "-" &
arrResults(2,i) & "-" & arrResults(3,i) & "<br>")
Next
End If
Response.Write(intTotalRecs)
Function GetConnString( _
ByVal strDB _
)
Select Case strDB
Case "Access"
GetConnString = Application("AccessConnString")
Case "SQLServer"
GetConnString = Application("SQLServerConnString")
End Select
End Function
Function GetOrderBy()
Dim intTemp
Dim strOrderBy
Randomize
intTemp = Int(3 * Rnd + 1)
Select Case intTemp
Case 1
strOrderBy = "OrderID "
Case 2
strOrderBy = "CustomerID "
Case 3
strOrderBy = "OrderDate "
End Select
intTemp = Int(2 * Rnd + 1)
If intTemp = 1 then
strOrderBy = strOrderBy & " ASC"
Elseif intTemp = 2 then
strOrderBy = strOrderBy & " DESC"
End If
GetOrderBy = strOrderBy
End Function
%>
======= adOpenStatic Code =======
<%@ Language=VBScript EnableSessionState=False %>
<%
Option Explicit
Response.Buffer = True
Server.ScriptTimeout = 0
Dim objConn
Dim objRS
Dim strSQL
Dim strDB
Dim strConnect
Dim intPage
Dim objField
Dim i
Dim intTotalRecs
strDB = Request.QueryString("DB")
strConnect = GetConnString(strDB)
intPage = Request.QueryString("Page")
strSQL = _
"SELECT OrderID, CustomerID, EmployeeID, OrderDate " & _
"FROM Orders " & _
"ORDER BY " & GetOrderBy()
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionTimeout = 0
objConn.CommandTimeout = 0
Do While objConn.State = adStateClosed
On Error Resume Next
objConn.Open strConnect
On Error Goto 0
i = i + 1
If i > 20 then
Exit Do
End If
Loop
Set objRS = Server.CreateObject("ADODB.Recordset")
' Choose pagesize and adjust loop below
objRS.PageSize = 20
'objRS.PageSize = 50
'objRS.PageSize = 160
objRS.Open strSQL, objConn, adOpenStatic, adLockReadOnly, adCmdText
objRS.AbsolutePage = intPage
intTotalRecs = objRS.RecordCount
For i = 1 to 20
' For i = 1 to 50
' For i = 1 to 160
If not objRS.EOF then
Response.Write(objRS("OrderID") & "-" & objRS("CustomerID") & "-" &
objRS("EmployeeID") & "-" & objRS("OrderDate") & "<br>")
objRS.MoveNext
End If
Next
Response.Write(intTotalRecs)
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
Function GetConnString( _
ByVal strDB _
)
Cheers
Ken
----- Original Message -----
From: "Adriano Castro" <ad at netvisao.pt>
To: <thelist at lists.evolt.org>
Sent: Thursday, April 01, 2004 12:27 AM
Subject: Re: [thelist] ADO cursor - best solution for count and
pagethroughprocedure
:
: Ken,
:
: Do you think we can have a look at the code u used to perform these
: tests?
:
: AD
:
: > Ken said:
: >
: > Hi there,
: >
: > (...)
: >
: > I've done some load testing on this. Using 3 client machines, and a
: > dual-proc webserver. Two options:
: > a) using adOpenStatic + .RecordCount, and .AbsolutePage
: > b) using ForwardOnly/GetRows, separate Count(*) query.
: >
: > For each of (a) and (b), I used the Orders table in the Northwind
: > database,
: > and tried 5 pages of 20 records (100 recs total), 5 pages of 100 records
: > (500 recs total), and 5 pages of 160 records (800 total). I used a
: > rando
: > miser to mix up the column to sort by.
More information about the thelist
mailing list