[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