Note: This page has been superseded - click here to view the new page.

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 tests were run on a Dell Pentium 450 MHz with 128 MB 100 MHz ECC SDRAM.
The server 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 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.

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, pulling twenty records at a time, starting from records 1-20, and moving all the way through to 1981-2000. 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 (Text). 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
<%
blnMoreRecords = True
numRecs = 20
i = 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("@TotalRecs", adInteger, adParamOutput, 4)
   Set objRS = objCommand.Execute
   objRS.Move((i * numRecs))

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

   objRS.Close
   Set objRS = nothing

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

   Set objCommand = nothing

   If ((i+1) * numRecs) >= intTotalRecs then
      blnMoreRecords = False
   End If

Loop
%>

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

CREATE PROC usp_pagingtest1

   @TotalRecs   int   OUTPUT

AS

      SELECT @TotalRecs = COUNT(TestID) FROM Test

      SELECT TestID, TestText FROM Test

GO

The adOpenForwardOnly/GetRows 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

<%
blnMoreRecords = True
numRecs = 20
i = 0

Do While blnMoreRecords = True

   strSQL = "usp_pagingtest2 " & i & ", " & numRecs
   Set objRS = Server.CreateObject("ADODB.Recordset")
   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 = (i * 20) + CInt(arrResults(2,0))

Loop
%>

The stored procedure for this test is little more complex

CREATE PROC usp_pagingtest2

   @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 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
<%
blnMoreRecords = True
numRecs = 20
i = 0

Do While blnMoreRecords = True

   strSQL = "SELECT TestID, TestText FROM Test"

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

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

   objRS.Close
   Set objRS = nothing

   If (i * numRecords) >= intTotalRecs then
      blnMoreRecords = False
   End If

   i = i + 1

Loop
%>

Back to Experiments Listing