adOpenStatic Logo
Navigation
Home
FAQ
Ken's Blog
Resources
Contact Ken
Copyright 2000 -



Introduction
In response to some comments regarding my original speed test I have modified the setup slightly. The new setup has the following changes:

  • Separate DB and Web servers. Data is marshalled across the network.
  • Only 200 records are selected from the master table of 2000 records, to make the test more "realistic" even though the lower number of records reduces the differences in time.

The challenge:
Often I see requests on how people can displayed a recordset in a paged manner - eg if they have 200 results from a query, how can they display these results, say, 20 records at a time with back and forward buttons.

Many of the tutorials on the web use a combination of adOpenStatic cursors, Recordset.PageSize and Recordset.AbsolutePage. One possible alternative that I was thinking about was using an adOpenForwardOnly cursor, and .GetRows instead. So which is faster?

TestBed:
The database server is a Dell Pentium II 450 MHz with 128 MB 100 MHz ECC SDRAM. It is running NT Server v4 (with SP6a) and IIS v4 with MDAC 2.6 and the v5.1 VBScript Engine. The database is SQL Server 2000 Standard Edition.

The webserver is a Dell Pentium III 800 MHz, with 256 MB 100 MHz SDRAM.
This server is running Windows 2000 Server (with SP2) and IIS v5, with MDAC 2.6 and the v5.5 VBScript Engine.

The Results
I've done four sets of tests.

  • Open the recordset with a Forwardonly cursor (using a proc), move to the appropriate record with objRS.move and gulp the necessary records into an array using .GetRows()
  • Use a proc to move the recordset to an SQL Server temp table, and then select only those records needed, ala: www.4guysfromrolla.com/webtech/062899-1.shtml
  • Used an adOpenStatic cursor, objRS.AbsolutePage and objRS.MoveNext
  • Used an adOpenStatic cursor, objRS.AbsolutePage and .GetRows

I've not reported the results for test 3 above since it was very similar, but slightly slower than test 4 (using .GetRows). All numbers are in milliseconds, divide by 1,000 to get the number of seconds.

Run Number

ForwardOnly & objRS.Move

ForwardOnly & Temp Table

Static & objRS.AbsolutePage

1

200

310

565

2

210

301

550

3

201

311

561

4

201

310

551

5

200

321

571

6

200

310

571

The Code
Each test was run 15 times, with the first 9 results discarded (to allow IIS to cache the compiled p-code for the page, and because it appeared that SQL Server was optimising the queries. Until the times had stabilised, I kept running the tests).

Each test involved querying a 2000 record table, and extracting each 10th record (200 records total). We then page this smaller results set, 20 records at a time, starting from records 1-20, and working through to 181-200. The actual code used in it's entirety can be download here. Each test also included some code to emulate the Static cursor's .RecordCount, since that is usually an integral part of paging (being able to display the total number of matching records).

The database was a simple SQL Server 2000 database with 1 table (Test). This contained two fields:

  • TestID - Int (Identity) - PK (clustered index)
  • TestText - VarChar(50), Not Null, Not indexed

The timer script used was the speedtimer available here from LearnASP.com.

The substantive code for each test was as follows:

The adOpenForwardOnly/GetRows Method
<%
' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
' We will extract 200 records from a 2000 record table
' We will then use .getRows(20) to get 20 records into a VBScript array
' as well as calculate the total number of records
' We will loop through the code 10 times, simulating the user paging
' through each page of the results, adding the time to create each
' page of results, and then display this total time
' Timer starts after this line
' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *


strSrchCriteria = "Text10"
blnMoreRecords = True
intNumRecs = 20
intPageNum = 0

Do While blnMoreRecords = True
   Set objCommand = Server.CreateObject("ADODB.Command")
   objCommand.ActiveConnection = objConn
   objCommand.CommandType = adCmdStoredProc
   objCommand.CommandText = "usp_pagingtest1"
   objCommand.Parameters.Append objCommand.CreateParameter("@SrchCriteria", adVarChar, adParamInput, 50, strSrchCriteria)
   objCommand.Parameters.Append objCommand.CreateParameter("@TotalRecs", adInteger, adParamOutput, 4)
   Set objRS = objCommand.Execute
   objRS.Move((intPageNum * intNumRecs))

   ' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
   ' Next part is important. We only get 20 records into the array by
   ' using .GetRows() inbuilt numRecs property. We do *not* get the
   ' whole 200 record recordset into an array
   ' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *


   If not objRS.EOF then
      arrResults = objRS.GetRows(intNumRecs)
   End If

   objRS.Close
   Set objRS = nothing

   intTotalRecs = objCommand.Parameters("@TotalRecs").Value

   Set objCommand = nothing

   If ((intPageNum+1) * intNumRecs) >= intTotalRecs then
      blnMoreRecords = False
   End If

   intPageNum = intPageNum + 1

Loop
%>

The stored procedure for this test is pretty simple :-)

CREATE PROC usp_pagingtest1

   @SrchCriteria   varChar(50),
   @TotalRecs   int   OUTPUT

AS

   SELECT TestID, TestText
   FROM Test
   WHERE TestText = @SrchCriteria

   SELECT @TotalRecs = @@ROWCOUNT

GO

The adOpenForwardOnly/#Temp Table Method
The code for this (in particular the Proc) is largely borrowed from the following article at 4GuysFromRolla.com www.4guysfromrolla.com/webtech/062899-1.shtml

<%
' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
' The SP will extract 200 records out of 2000 into a temp table
' It will then use the other supplied params to select 20 records from
' the temp table and return a recordset. We will put this RS into an
' array. Using the values from the temp table we can auto calculate
' the total number of records.
' We will loop through the code 10 times, simulating the user paging
' through each page of the results, adding the time to create each
' page of results, and then display this total time
' Timer starts after this line
' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *


blnMoreRecords = True
strSrchCriteria = "Text10"
intPageNum = 1
intNumRecs = 20

Do While blnMoreRecords = True

   strSQL = "usp_pagingtest2 '" & strSrchCriteria & "', " & i & ", " & numRecs
   Set objRS = Server.CreateObject("ADODB.Recordset")
   objRS.cachesize = 20
   objRS.Open strSQL, objConn, adOpenForwardOnly, adLockReadOnly, adCmdText

   If not objRS.EOF then
      arrResults = objRS.GetRows
   End If

   objRS.Close
   Set objRS = nothing

   If CInt(arrResults(2,0)) <= 0 then
      blnMoreRecords = False
   End If

   intTotalRecs = (intPageNum * intNumRecs) + CInt(arrResults(2,0))

   intPageNum = intPageNum + 1

Loop
%>

The stored procedure for this test is little more complex

CREATE PROC usp_pagingtest2

   @SrchCriteria   varchar(50),
   @Page   int,
   @RecsPerPage   int

AS

   DECLARE @FirstRec   int
   DECLARE @LastRec   int
   SET NOCOUNT ON

   CREATE TABLE #TempItems
   (
      TempID int Identity,
      TempText varchar(50),
   )

   INSERT INTO #TempItems(TempText)
   SELECT TestText FROM Test
   WHERE TestText = @SrchCriteria
   ORDER BY TestID

   SELECT @FirstRec = (@Page-1) * @RecsPerPage
   SELECT @LastRec = (@Page * @RecsPerPage + 1)

   SELECT TempID, TempText,
      MoreRecords =
      (
         SELECT COUNT(*)
         FROM #TempItems
         WHERE #TempItems.TempID >= @LastRec
      )
   FROM #TempItems
   WHERE TempID > @FirstRec
   AND TempID < @LastRec

   SET NOCOUNT OFF

GO

The adOpenStatic/GetRows Method
<%
' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
' We use an SQL statement to select 200 records from the table
' The Recordset uses an adOpenStatic cursor. We set the pagesize
' to 20 and move to first page, and gulp 20 records using the inbuilt
' numRecs property of GetRows(). We will loop through the code 10 times,
' simulating the user paging through each page of the results, adding the
' time to create each page of results, and then display this total time
' Timer starts after this line
' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *


blnMoreRecords = True
strSrchCriteria = "Text10"
intPageNum = 1
intNumRecs = 20

Do While blnMoreRecords = True

   strSQL = _
      "SELECT TestID, TestText " & _
      "FROM Test" & _
      "WHERE TestText = '" & strSrchCriteria & "'"

   Set objRS = Server.CreateObject("ADODB.Recordset")
   objRS.CacheSize = 20
   objRS.PageSize = intNumRecs
   objRS.Open strSQL, objConn, adOpenStatic, adLockReadOnly, adCmdText
   objRS.AbsolutePage = intPageNum
   intTotalRecs = objRS.RecordCount

   If not objRS.EOF then
      arrResults = objRS.GetRows(intNumRecs)    End If

   objRS.Close
   Set objRS = nothing

   If (intPageNum * intNumRecs) >= intTotalRecs then
      blnMoreRecords = False
   End If

   intPageNum = intPageNum + 1

Loop
%>

Back to Experiments Listing