Recordset pagineren, een snelheidstest

Door Ken Schaefer
25 juli 2001

De uitdaging:
Ik zie vaak vragen van mensen over hoe ze een recordset kunnen weergeven als meerdere pagina's – als ze bijvoorbeeld 200 resultaten uit een query krijgen, hoe kunnen ze deze resultaten weergeven, met bijvoorbeeld 20 records tegelijk en met voor- en achteruit buttons.

Veel van de tutorials op het web gebruiken een combinatie van adOpenStatic cursors, Recordset.PageSize en Recordset.AbsolutePage. Een mogelijk alternatief waar ik aan zat te denken was het gebruik van een adOpenForwardOnly cursor en de GetRows methode. Dus welke methode is sneller?

Testomgeving
De database server is een Dell Pentium II 450 MHz met 128 MB 100 MHz ECC SDRAM. Het draait op NT Server v4 (met SP6a) en IIS v4 met MDAC 2.6 en de v5.1 VBScript Engine. De database is SQL Server 2000 Standard Edition.

De webserver is een Dell Pentium III 800 MHz, met 256 MB 100 MHz SDRAM.
De server draait op Windows 2000 Server (with SP2) en IIS v5, met MDAC 2.6 en de v5.5 VBScript Engine.

De resultaten
Ik heb een 4-tal tests uitgevoerd.

• Open de recordset met een adOpenForwardOnly cursor (met gebruik van een Stored Procedure), verplaats naar het juiste record met de Move methode en haal de records die je nodig hebt over naar een array met de GetRows methode.
• Gebruik een Stored Procedure om de recordset te verplaatsen naar een SQL Server temp table, en selecteer dan de records die je nodig hebt, zoals: www.4guysfromrolla.com/webtech/062899-1.shtml
• Gebruik een adOpenStatic cursor, AbsolutePage en MoveNext
• Gebruik een adOpenStatic cursor, AbsolutePage en GetRows

Ik heb de resultaten van test 3 niet weergegeven, aangezien ze vrijwel hetzelfde waren, maar iets langzamer dan test 4 (gebruik van GetRows). De resultaten zijn in milliseconden. Deel het door 1000 om het aantal seconden te krijgen.

De Code
Elke test werd 15 keer uitgevoerd, waarbij de eerste 9 resultaten opzij werden gelegd (om IIS in staat te stellen de gecompileerde p-code voor de pagina te cachen en omdat het leek dat SQL Server de queries aan het optimaliseren was. Ik ben blijven testen, totdat de tijden gestabiliseerd waren).

In elke test werden records opgevraagd uit een tabel met 2000 records, en werd elk 10e record eruit eruit gehaald (200 records totaal). We pagineren daarna deze kleinere resultatenset, met 20 records tegelijkertijd, beginnend bij records 1-20, doorgaand tot 181-200. De daadwerkelijke code, zoals die in zijn geheel gebruikt is, kan hier gedownload worden. Elke test bevatte ook wat code om te proberen de RecordCount eigenschap na te bootsen, aangezien het bij pagineren gebruikelijk is het totale aantal gevonden records weer te geven.

De database was een simpele SQL Server 2000 database met 1 tabel (Test). Deze bevatte twee velden:

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

Het timer script dat werd gebruikt was de speedtimer zoals beschreven in Hoe snel is mijn script?

De code voor elke test was als volgt:

De stored procedure voor deze test is vrij simpel:
CREATE PROC usp_pagingtest1

   @SrchCriteria varChar(50),
   @TotalRecs int OUTPUT

AS

   SELECT TestID, TestText
   FROM Test
   WHERE TestText = @SrchCriteria

   SELECT @TotalRecs = @@ROWCOUNT

GO

De adOpenForwardOnly/GetRows Methode met een tijdelijke tabel
De code hiervoor (met name de Stored Procedure) is grotendeels geleend van het artikel op 4GuysFromRolla.com: www.4guysfromrolla.com/webtech/062899-1.shtml.

<%
' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
' De SP haalt 200 records uit 2000 naar een temp table.
' Het zal dan de andere gegeven parameters gebruiken om 20 records te selecteren
' van de temp tabel en een recordset retourneren. We stoppen deze recordset in een
' array. Door de waarden van de temp table te gebruiken kunnen we automatisch
' het totaal aantal records berekenen.
' We lopen 10 keer door de code, waarmee we simuleren dat de gebruiker bladert
' door de pagina's met resultaten, voegen de tijd toe die nodig is om het resultaat
' van elke pagina te creëeren en tonen dan de totale tijd.
' Timer start na deze regel
' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

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
%>

De stored procedure voor deze test is iets ingewikkelder.
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

De adOpenStatic/GetRows Methode
<%

' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
' We gebruiken een SQL statement om 200 records uit de tabel te selecteren.
' De Recordset gebruikt een adOpenStatic cursor. We zetten de paginagrootte
' op 20, verplaatsen de eerste pagina en gulpen 20 records met gebruik van de
' ingebouwde numRecs eigenschap van GetRows(). We lopen 10 keer door de
' code,waarmee we simuleren dat de gebruiker bladert door de pagina's
' met resultaten, voegen de tijd toe die nodig is om het resultaat van elke
' pagina te creëeren en tonen dan de totale tijd.
' Timer start na deze regel.
' * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

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
%>

© Ken Schaefer (vertaling copyright ASPNL)