Wednesday, November 5, 2008

How do I page through a recordset? Select Certain Range Limit

How do I page through a recordset?

A very common task when designing web pages is to allow users to "page" through a resultset. This means 10 or 50 or some fixed number of rows are displayed on each page, and the user can click next / previous or choose a page number from a dropdown.

Previously, this article had a total of three samples. One in straight ASP, and two with different approaches using stored procedures.

Thanks to Chris Hohmann, Anith Sen, Steve Kass, David Portas, Uri Dumant — and, most recently, Brynn and Bob Barrows — for providing me the ammunition and motivation to re-write this article. Now, it contains a total of TEN different techniques. After explaining each one, I will also show you which ones performed best in my tests (see results), and even provide you with all the samples (and the testing code) as a download.

(If you are interested in specific techniques of paging data using alphabetic breakdowns, see Article #2352.)


There are two main branches of approaching the "rows per page" problem: using a recordset in ASP, and using a stored procedure. We're going to split them up in those sections for two reasons: (1) the code that uses a recordset in ASP can also be used for Access (after removing the WITH (NOLOCK) hints); and (2) we are going to be using #include files for common functionality, and the implementation is different enough between the two branches to introduce them separately.

In Yukon, we will be able to handle this situation in new ways; TOP will be able to take an expression, and there are new ranking features that will make selecting a "region" of rows from a larger set much easier. For now, when using SQL Server 7.0 or 2000, we are stuck with less than optimal solutions.

Recordset Methods

Before we start getting into each of the approaches, let's set up. First thing we'll need is a table:

CREATE TABLE SampleCDs
(
ArtistName VARCHAR(64),
Title VARCHAR(64),
PRIMARY KEY (ArtistName, Title)
)
GO

We'll also need to populate it with data. You can see the script I used to create the table, and insert my CD collection, here (73 KB).

Next, we'll need to set up our ASP environment. Much of the functionality of the ASP pages can be handled in a common fashion, so we can take advantage of #include files. The first file we'll use is called inc.asp, and it just has the connection information and opens a connection object (as well as a single style declaration used for the HTML table):

<% Set Conn = CreateObject("ADODB.Connection") Conn.Open "Provider=SQLOLEDB.1;" & _ "Network=DBMSSOCN;" & _ "Data Source=;" & _
"Initial Catalog=;" & _
"User ID=;" & _
"Password="
%>

To run these individual pages, this should be the only file you have to modify.

The next page is called topRS.asp. It includes logic for obtaining total rowcount from the table, making sure the values passed in (for page number and number of rows per page) are valid, and setting up the dropdown we're going to use to navigate from page to page.

<% countSQL = "SELECT COUNT(*) FROM SampleCDs WITH (NOLOCK)" RowCnt = 0 set rs = conn.execute(countSQL) if not rs.eof then RowCnt = clng(rs(0)) end if if RowCnt = 0 then Response.Write "No rows found." Response.End end if PerPage = Trim(Request.QueryString("PerPage")) PageNum = Trim(Request.QueryString("PageNum")) If PerPage = "" or (len(PerPage)>0 and not isnumeric(PerPage)) Then _
PerPage = 50

If PageNum = "" or (len(PageNum)>0 and not isnumeric(PageNum)) Then _
PageNum = 1

PerPage = clng(PerPage)
PageNum = clng(PageNum)

PageCnt = RowCnt \ PerPage

if RowCnt mod PerPage <> 0 then PageCnt = PageCnt + 1
if PageNum < pagenum =" 1"> PageCnt Then PageNum = PageCnt

url = Request.ServerVariables("SCRIPT_NAME")
urlParts = split(url, "/")
pageName = urlParts(ubound(urlParts))
%>

<% response.write RowCnt & " rows found. Showing " & _ " page " & PageNum & " of " & PageCnt & "." response.write "

"
%>


Finally, we have an #include file that cleans up, called foot.asp:

<% rs.close: set rs = nothing conn.close: set conn = nothing %>

So, each of our code samples for recordset techniques will look like this:



<% ' ... code ... %>

Recordset.AbsolutePage
    The first method we'll look at is the AbsolutePage method. This is probably the one you see most often; it uses an explicit ADODB.Recordset object, and properties like PageSize. Of the recordset methods, this one performed the worst in our limited testing (see results).



    <% dataSQL = "SELECT ArtistName, Title FROM SampleCDs WITH (NOLOCK)" set rs = CreateObject("ADODB.Recordset") rs.PageSize = PerPage rs.CacheSize = PerPage ' 3 = adOpenStatic, 1 = adLockReadOnly, &H0001 = adCmdText rs.Open dataSQL, conn, 3, 1, &H0001 if not rs.eof then response.write ""

    rs.AbsolutePage = PageNum

    x = 0
    do while x < artist =" rs(0)" title =" rs(1)"> prevArtist then
    prevArtist = artist
    response.write ""
    response.write ""

    x = x + 1

    rs.movenext
    loop

    response.write "
    "
    response.write artist & "    		"
    else
    response.write "

    		"
    end if

    response.write title & "
    "
    else
    response.write "No rows found."
    response.end
    end if
    %>

    This code can be found in rsPage.asp.
Recordset.GetRows() + AbsolutePage
    Most people would probably assume that GetRows() would perform quite well, but it only performed slightly better than the AbsolutePage method described above. GetRows() converts a heavy recordset object into a lighter-weight array for local processing (see Article #2467 for more information); though, in order to reduce the size of the array, we use AbsolutePage and PageSize to move right to the relevant chunk of rows.



    <% dataSQL = "SELECT ArtistName, Title FROM SampleCDs WITH (NOLOCK)" set rs = CreateObject("ADODB.Recordset") rs.PageSize = PerPage rs.CacheSize = PerPage ' 3 = adOpenStatic, 1 = adLockReadOnly, &H0001 = adCmdText rs.Open dataSQL, conn, 3, 1, &H0001 if not rs.eof then rs.AbsolutePage = PageNum Dim gr gr = rs.GetRows(PerPage) response.write ""

    for i = 0 to perpage - 1

    artist = gr(0, i)
    title = gr(1, i)

    if artist <> prevArtist then
    prevArtist = artist
    response.write ""
    response.write ""

    next

    response.write "
    "
    response.write artist & "    		"
    else
    response.write "

    		"
    end if

    response.write title & "
    "
    else
    response.write "No rows found."
    response.end
    end if
    %>

    This code can be found in rsGetRowsPage.asp.
Recordset.Move()
    The Recordset.Move() technique was the first example I ever posted in this article. In an attempt to eliminate the need for heavy recordset objects, I decided to try the Move() method to skip the first n rows in the resultset to start at the first row for the page we are interested in.



    <% rstart = PerPage * Pagenum - (PerPage - 1) dataSQL = "SELECT ArtistName, Title FROM SampleCDs WITH (NOLOCK)" set rs = conn.execute(dataSQL) if not rs.eof then rs.move(rstart-1) response.write ""

    for x = 1 to PerPage

    if rs.eof then exit for

    artist = rs(0)
    title = rs(1)

    if artist <> prevArtist then
    prevArtist = artist
    response.write ""
    response.write ""

    rs.movenext
    next

    response.write "
    "
    response.write artist & "    		"
    else
    response.write "

    		"
    end if

    response.write title & "
    "
    else
    response.write "No rows found."
    response.end
    end if
    %>

    This code can be found in rsMove.asp.
Recordset.GetRows() + Recordset.Move()
    This method combines the effective move() technique, with GetRows() ability to accept a "chunk" as a parameter. Of the recordset technologies we tested, this performed best.



    <% dataSQL = "SELECT ArtistName, Title FROM SampleCDs WITH (NOLOCK)" set rs = conn.execute(dataSQL) if not rs.eof then Dim gr rstop = PerPage * PageNum rstart = rstop - (PerPage - 1) rs.move(rstart-1) if rstop > RowCnt - 1 then PerPage = (RowCnt + 1) - rstart
    gr = rs.GetRows(PerPage)

    response.write ""

    for i = 0 to perpage-1

    artist = gr(0, i)
    title = gr(1, i)

    if artist <> prevArtist then
    prevArtist = artist
    response.write ""
    response.write ""

    next

    response.write "
    "
    response.write artist & "    		"
    else
    response.write "

    		"
    end if

    response.write title & "
    "
    else
    response.write "No rows found."
    response.end
    end if
    %>

    This code can be found in rsGetRowsMove.asp.

Stored Procedure Methods

If you are using Access, you could probably enhance a paging solution above to use stored queries. However, stored queries are not quite as flexible and powerful as stored procedures in SQL Server. One of the main drawbacks of the recordset methods described above is that *all* the rows are requested from the database and sent over the network to the ASP page. It is only at the web server that the bulk of the resultset is discarded, and this can be quite a wasteful operation on larger tables.

I experimented with six different techniques in stored procedures to carve up a resultset into pages of user-defined row counts. A couple are my own design, one came from Chris Hohmann, and a couple were derived from conversations in the SQL Server newsgroups.

Like with the recordset methods, before we dive into the stored procedure techniques, we need to set up a few things. First will be the ASP pages. As before, we have inc.asp (for connection information) and foot.asp (for cleanup). Instead of topRS.asp, we have topSP.asp. This file is moderately different; the main difference being that we no longer need a query to determine total number of rows / pages, since we will get that from the stored procedure each time. Here is topSP.asp:

<% PerPage = Trim(Request.QueryString("PerPage")) PageNum = Trim(Request.QueryString("PageNum")) If PerPage = "" or (len(PerPage)>0 and not isnumeric(PerPage)) Then _
PerPage = 50

If PageNum = "" or (len(PageNum)>0 and not isnumeric(PageNum)) Then _
PageNum = 1

PerPage = clng(PerPage)
PageNum = clng(PageNum)

url = Request.ServerVariables("SCRIPT_NAME")
urlParts = split(url, "/")
pageName = urlParts(ubound(urlParts))
%>

<% spName = "SampleCDs_Paging_" & Mid(Left(pageName, Len(pageName)-4), 3, 32) dataSQL = "EXEC " & spName & " " & pageNum & ", " & perPage set rs = conn.execute(dataSQL) PageNum = rs(0) PageCnt = rs(1) RowCnt = rs(2) if RowCnt = 0 then response.write "No rows found." response.end end if response.write RowCnt & " rows found. Showing " & _ " page " & PageNum & " of " & PageCnt & "." response.write "

"
%>


Notice that I named the stored procedures in a manner consistent with the name of the ASP pages that call them, respectively. This way, we can use the name of the page to call the stored procedure, instead of hard-coding it into six different files.

Now, the ASP code used to call each stored procedure is identical. So for each method below, you will be asked to create an ASP file with a specific name, using the "common ASP code from above." That code is as follows:



<% set rs = rs.NextRecordset() if not rs.eof then Dim gr gr = rs.GetRows() rstop = PerPage - 1 if rstop > ubound(gr, 2) then rstop = ubound(gr, 2)

response.write ""

for i = 0 to rstop

artist = gr(0, i)
title = gr(1, i)

if artist <> prevArtist then
prevArtist = artist

response.write ""
response.write ""

next

response.write "
"
response.write artist & "		"
else
response.write "

"
end if

response.write title & "
"
else
response.write "No rows found."
response.end
end if

%>

We use GetRows() here not because it has any particular impact on any of the methods we chose, but rather because we know that this is *usually* the most efficient method of displaying a resultset. Rather than get into another comparison for the different display techniques, I chose to just process all the stored procedures using GetRows() to eliminate that as a source for discrepancy during performance testing. We don't really need to use the extended capabilities above (AbsolutePage, PageSize, Move(), and the "chunk" parameter) because the stored procedure is already reducing the resultset to exactly the size of slice we want.

User-Defined Function
    A UDF is basically an encapsulation of code that returns a table or a scalar value. In this case, I created a function that returns an integer, representing the "rank" of a given artist/title combination, alphabetically, compared to the rest of the table. In individual testing, when viewing the execution plan in Query Analyzer, this method seemed to do quite well. However, it did not fare so well when put to a heavier test (see results). The run time roughly doubled when dealing with rows near the "end" of the table; a good indicator of a well-implemented paging solution is little or no difference between obtaining the first page and the last page.

    Here is the function code:

    CREATE FUNCTION dbo.ReturnCDRank
    (
    @ArtistName VARCHAR(64),
    @Title VARCHAR(64)
    )
    RETURNS INT
    AS
    BEGIN
    RETURN
    (
    SELECT
    COUNT(*)
    FROM
    SampleCDs WITH (NOLOCK)
    WHERE
    ArtistName + '~' + Title
    <= @ArtistName + '~' + @Title ) END GO

    And here is the stored procedure code:

    CREATE PROCEDURE SampleCDs_Paging_UDF
    @pagenum INT = 1,
    @perpage INT = 50
    AS
    BEGIN
    SET NOCOUNT ON

    DECLARE
    @ubound INT,
    @lbound INT,
    @pages INT,
    @rows INT

    SELECT
    @rows = COUNT(*),
    @pages = COUNT(*) / @perpage
    FROM
    SampleCDs WITH (NOLOCK)

    IF @rows % @perpage != 0 SET @pages = @pages + 1
    IF @pagenum < pagenum =" 1"> @pages SET @pagenum = @pages

    SET @ubound = @perpage * @pagenum
    SET @lbound = @ubound - (@perpage - 1)

    SELECT
    CurrentPage = @pagenum,
    TotalPages = @pages,
    TotalRows = @rows

    -- this method uses a user-defined function
    -- to rank the rows based on how many rows
    -- exist with "lower" string values

    SELECT
    ArtistName,
    Title
    FROM
    SampleCDs WITH (NOLOCK)
    WHERE
    dbo.ReturnCDRank(ArtistName, Title)
    BETWEEN
    @lbound AND @ubound
    ORDER BY
    ArtistName,
    Title
    END
    GO

    To run this page, create a file called spUDF.asp, and include the common ASP code from above.

Subquery / COUNT
    Another method I played with is a subquery with a count. This uses a COUNT of rows in a subquery where the values are compared to the outer table. Like the UDF, the performance of this solution was unacceptable, though the run time didn't change much at either end of the result set.

    Here is the stored procedure code:

    CREATE PROCEDURE SampleCDs_Paging_Subquery
    @pagenum INT = 1,
    @perpage INT = 50
    AS
    BEGIN
    SET NOCOUNT ON

    DECLARE
    @ubound INT,
    @lbound INT,
    @pages INT,
    @rows INT

    SELECT
    @rows = COUNT(*),
    @pages = COUNT(*) / @perpage
    FROM
    SampleCDs WITH (NOLOCK)

    IF @rows % @perpage != 0 SET @pages = @pages + 1
    IF @pagenum < pagenum =" 1"> @pages SET @pagenum = @pages

    SET @ubound = @perpage * @pagenum
    SET @lbound = @ubound - (@perpage - 1)

    SELECT
    CurrentPage = @pagenum,
    TotalPages = @pages,
    TotalRows = @rows

    -- this method uses a COUNT subquery to
    -- peg a sliding range to the desired set
    -- of rows

    SELECT
    A.ArtistName,
    A.Title
    FROM
    SampleCDs A WITH (NOLOCK)
    WHERE
    (
    SELECT COUNT(*)
    FROM SampleCDs B WITH (NOLOCK)
    WHERE B.ArtistName+'~'+B.Title
    <= A.ArtistName+'~'+A.Title ) BETWEEN @lbound AND @ubound ORDER BY A.ArtistName, A.Title END GO

    To run this page, create a file called spSubquery.asp, and include the common ASP code from above.

Self-Join / COUNT
    This technique is only a little bit different from the subquery / COUNT technique, using a self-join instead to determine the ranking of the relevant rows. It performed a little better overall, and surprisingly, was better individually on later pages than earlier pages.

    Here is the stored procedure code:

    CREATE PROCEDURE SampleCDs_Paging_Join
    @pagenum INT = 1,
    @perpage INT = 50
    AS
    BEGIN
    SET NOCOUNT ON

    DECLARE
    @ubound INT,
    @lbound INT,
    @pages INT,
    @rows INT

    SELECT
    @rows = COUNT(*),
    @pages = COUNT(*) / @perpage
    FROM
    SampleCDs WITH (NOLOCK)

    IF @rows % @perpage != 0 SET @pages = @pages + 1
    IF @pagenum < pagenum =" 1"> @pages SET @pagenum = @pages

    SET @ubound = @perpage * @pagenum
    SET @lbound = @ubound - (@perpage - 1)

    SELECT
    CurrentPage = @pagenum,
    TotalPages = @pages,
    TotalRows = @rows

    -- this method uses an inner join and a
    -- having clause to move a sliding window
    -- to the desired set of rows

    SELECT
    A.ArtistName,
    A.Title
    FROM
    SampleCDs A WITH (NOLOCK)
    INNER JOIN SampleCDs B WITH (NOLOCK)
    ON
    A.ArtistName+'~'+A.Title >= B.ArtistName+'~'+B.Title
    GROUP BY
    A.ArtistName, A.Title
    HAVING
    COUNT(*) BETWEEN @lbound AND @ubound
    ORDER BY
    A.ArtistName,
    A.Title

    END
    GO

    To run this page, create a file called spJoin.asp, and include the common ASP code from above.

#Temp table
    This was the first stored procedure example I posted to this article, though it is cleaned up a bit now. The code inserts the entire base table into a #temp table with an IDENTITY column, then uses that column to determine "rank." Note that the behavior we see here is taken for granted; it is neither documented nor guaranteed to obey the ORDER BY clause when inserting the rows into the #temp table. The optimizer will insert the rows in whatever order it deems fit, which means that the values in the columns could certainly be out of order. Luckily, it just so happens that it works right so much more often than it fails, that it is virtually reliable. Still, it should be noted with an asterisk, as the behavior could change in future versions.

    Here is the stored procedure code:

    CREATE PROCEDURE SampleCDs_Paging_TempTable
    @pagenum INT = 1,
    @perpage INT = 50
    AS
    BEGIN
    SET NOCOUNT ON

    DECLARE
    @ubound INT,
    @lbound INT,
    @pages INT,
    @rows INT

    SELECT
    @rows = COUNT(*),
    @pages = COUNT(*) / @perpage
    FROM
    SampleCDs WITH (NOLOCK)

    IF @rows % @perpage != 0 SET @pages = @pages + 1
    IF @pagenum < pagenum =" 1"> @pages SET @pagenum = @pages

    SET @ubound = @perpage * @pagenum
    SET @lbound = @ubound - (@perpage - 1)

    SELECT
    CurrentPage = @pagenum,
    TotalPages = @pages,
    TotalRows = @rows

    -- this method inserts rows into a temp
    -- table, and the identity helps us rank
    -- them and return only the desired rows

    CREATE TABLE #CDList
    (
    CDID INT IDENTITY(1,1)
    PRIMARY KEY CLUSTERED,
    ArtistName VARCHAR(64),
    Title VARCHAR(64)
    )

    -- behavior not guaranteed!
    INSERT #CDList
    SELECT
    ArtistName,
    Title
    FROM
    SampleCDs WITH (NOLOCK)
    ORDER BY
    ArtistName,
    Title

    SELECT
    ArtistName,
    Title
    FROM
    #CDList
    WHERE
    CDID
    BETWEEN
    @lbound AND @ubound
    ORDER BY
    ArtistName,
    Title

    DROP TABLE #CDList
    END
    GO

    To run this page, create a file called spTempTable.asp, and include the common ASP code from above.

Dynamic SQL
    This is basically the second stored procedure method I posted to this article (and not too long ago, I might add). It uses nested TOP commands to get the "last" set of rows from a larger set that is basically the first @perpage * @pagenum rows. So, if we had 10 rows per page, and we were on page 8, we would be asking for:

    SELECT TOP 10 rows FROM (SELECT TOP 80 rows ORDER BY ASC) ORDER BY DESC

    (I'm probably not explaining that very well; my only excuse is that it's late.)

    Then we wrap another query around it so we can still present the rows in ASCENDING order. The only reason we have to use dynamic SQL here is because the TOP command cannot take a variable. (See this article for more information on dynamic SQL.)

    Here is the stored procedure code:

    CREATE PROCEDURE SampleCDs_Paging_DynamicSQL
    @pagenum INT = 1,
    @perpage INT = 50
    AS
    BEGIN
    SET NOCOUNT ON

    DECLARE
    @ubound INT,
    @lbound INT,
    @pages INT,
    @rows INT,
    @lastPageDiff INT

    SELECT
    @rows = COUNT(*),
    @pages = COUNT(*) / @perpage
    FROM
    SampleCDs WITH (NOLOCK)

    IF @rows % @perpage != 0
    SET @pages = @pages + 1
    IF @rows % @perpage != 0 AND @pagenum = @pages
    SET @lastPageDiff = @perPage - (@rows % @perpage)
    IF @pagenum < pagenum =" 1"> @pages SET @pagenum = @pages

    SET @ubound = @perpage * @pagenum
    SET @lbound = @ubound - (@perpage - 1)

    SELECT
    CurrentPage = @pagenum,
    TotalPages = @pages,
    TotalRows = @rows

    -- this method uses dynamic SQL (because TOP
    -- can't take a parameter in SQL Server 2000
    -- and lower) to retrieve a nested subquery

    DECLARE @sql NVARCHAR(1024)

    SET @sql = N'SELECT
    ArtistName,
    Title
    FROM
    (
    SELECT TOP '+RTRIM(@perpage - @lastPageDiff)
    +' ArtistName,
    Title
    FROM
    (
    SELECT TOP '+RTRIM(@perpage * @pagenum)
    +' ArtistName,
    Title
    FROM
    SampleCDs WITH (NOLOCK)
    ORDER BY
    ArtistName+''~''+Title
    ) a
    ORDER BY
    ArtistName+''~''+Title DESC
    ) b
    ORDER BY
    ArtistName+''~''+Title'

    EXEC sp_executeSQL @sql

    END
    GO

    To run this page, create a file called spTempTable.asp, and include the common ASP code from above.

RowCount
    This was Chris Hohmann's gem, and I only touched it up a little bit to fit this example. Basically, it uses SET ROWCOUNT to assign the *first* value from the current page to local variables. Then it sets ROWCOUNT again to the value representing the number of rows we want to see per page, and asks for all the rows >= the value of the local variables. Altogether genius, and it was the best performer of the day as well.

    Here is the stored procedure code:

    CREATE PROCEDURE SampleCDs_Paging_Rowcount
    @pagenum INT = 1,
    @perpage INT = 50
    AS
    BEGIN
    SET NOCOUNT ON

    DECLARE
    @ubound INT,
    @lbound INT,
    @pages INT,
    @rows INT

    SELECT
    @rows = COUNT(*),
    @pages = COUNT(*) / @perpage
    FROM
    SampleCDs WITH (NOLOCK)

    IF @rows % @perpage != 0 SET @pages = @pages + 1
    IF @pagenum < pagenum =" 1"> @pages SET @pagenum = @pages

    SET @ubound = @perpage * @pagenum
    SET @lbound = @ubound - (@perpage - 1)

    SELECT
    CurrentPage = @pagenum,
    TotalPages = @pages,
    TotalRows = @rows

    -- this method determines the string values
    -- for the first desired row, then sets the
    -- rowcount to get it, plus the next n rows

    DECLARE @aname VARCHAR(64), @title VARCHAR(64)

    SET ROWCOUNT @lbound

    SELECT
    @aname = ArtistName,
    @title = Title
    FROM
    SampleCDs WITH (NOLOCK)
    ORDER BY
    ArtistName,
    Title

    SET ROWCOUNT @perPage

    SELECT
    ArtistName,
    Title
    FROM
    SampleCDs WITH (NOLOCK)
    WHERE
    ArtistName + '~' + Title
    >= @aname + '~' + @title
    ORDER BY
    ArtistName,
    Title

    SET ROWCOUNT 0
    END
    GO

    To run this page, create a file called spRowcount.asp, and include the common ASP code from above.

Results

I used a looping script to hit each of the 9 ASP pages 20 times. 10 times where it would get the first page, and 10 times where it would get the last page (in this case, I broke the results up 20 rows at a time, and asked for the last page (64)). Here is the ASP code used in the test:

<% numberOfTries = 10 server.scripttimeout = 300000 dim pn(9) pn(0) = "rsMove" pn(1) = "rsPage" pn(2) = "rsGetRowsMove" pn(3) = "rsGetRowsPage" pn(4) = "spRowcount" pn(5) = "spDynamicSQL" pn(6) = "spSubquery" pn(7) = "spJoin" pn(8) = "spUDF" pn(9) = "spTempTable" set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP") originalClock = timer response.write originalClock & "

"

for i = 0 to 9

originalClock = timer
for x = 1 to numberOfTries
url = "http://www.aaronbertrand.com/pg/" & _
pn(i) & ".asp"
xmlhttp.open "GET", url, false
xmlhttp.send ""
result = xmlhttp.ResponseText
next
newclock = timer
response.write pn(i) & ": " & newclock & _
" (" & newclock - originalClock & ")
"
originalClock = newCLock

for x = 1 to numberOfTries
url = "http://www.aaronbertrand.com/pg/" & _
pn(i) & ".asp?PageNum=62&PerPage=20"
xmlhttp.open "GET", url, false
xmlhttp.send ""
result = xmlhttp.ResponseText
next
newclock = timer
response.write pn(i) & ": " & newclock & _
" (" & newclock - originalClock & ")
"
originalClock = newCLock
next
response.write "

" & NewClock
set xmlhttp = nothing
%>


I assign the ResponseText to a result, even though it is never used, to make sure I simulate the drawing of data down to the browser; even though the rendering is missing, it makes the performance truer than never asking for the responseText.

Okay, enough with the chit-chat. Here are the results.

MethodTime (seconds)
(Per page)		Time (seconds)
(Per page)		Average
Stored Procedure
Rowcount		0.0150000.0168750.015938
Recordset
GetRowsMove		0.0168750.0168750.016875
Recordset
Move		0.0250000.0175000.021250
Stored Procedure
DynamicSQL		0.0218750.0381250.030000
Recordset
GetRowsPage		0.0350000.0337500.034375
Recordset
Page		0.0375000.0337500.035625
Stored Procedure
TempTable		0.0506250.0443750.047500
Stored Procedure
Join		4.6987502.0181253.358438
Stored Procedure
Subquery		4.1918754.1975004.194688
Stored Procedure
UDF		3.3843756.4456254.915000

Well, that's it. Ten different approaches to the paging problem. You should test each one in your environment, rather than taking the above results as gospel. Your results may vary, depending on your hardware, network capacity, and whether you test your pages on a machine separate from the database. In this case, I tested the ASP pages living on the same machine as SQL Server, so there are likely some real-world factors not accounted for. The hardware used was a Dell workstation (dual 1.4 GHz Xeon processors, 1 GB RAM, IDE drives). Windows Server 2003 with SQL Server 2000 (8.00.818).

There will also be varying effects on the results by altering the size of the dataset. I will attempt the same tests, at a later date, on a table several orders of magnitude larger than this one.

You can find all of the files used in this article in our downloads section.

Related Articles

How do I build a query with optional parameters?
How do I calculate the median in a table?
How do I create a store locator feature?
How do I deal with MEMO, TEXT, HYPERLINK, and CURRENCY columns?
How do I deal with multiple resultsets from a stored procedure?
How do I debug my SQL statements?
How do I determine if a column exists in a given table?
How do I enable or disable connection pooling?
How do I enumerate through the DSNs on a machine?
How do I find a stored procedure containing ?
How do I get a list of Access tables and their row counts?
How do I get the latest version of the JET OLEDB drivers?
How do I handle alphabetic paging?
How do I handle BIT / BOOLEAN columns?
How do I handle error checking in a stored procedure?
How do I ignore common words in a search?
How do I present one-to-many relationships in my ASP page?
How do I prevent duplicates in a table?
How do I prevent my ASP pages from waiting for backend activity?
How do I prevent NULLs in my database from mucking up my HTML?
How do I protect my Access database (MDB file)?
How do I protect my stored procedure code?
How do I protect myself against the W32.Slammer worm?
How do I remove duplicates from a table?
How do I rename a column?
How do I retrieve a random record?
How do I return row numbers with my query?
How do I send a database query to a text file?
How do I simulate an array inside a stored procedure?
How do I solve 'Could not find installable ISAM' errors?
How do I solve 'Operation must use an updateable query' errors?
How do I temporarily disable a trigger?
How do I use a SELECT list alias in the WHERE or GROUP BY clause?
How do I use a variable in an ORDER BY clause?
Should I index my database table(s), and if so, how?
Should I store images in the database or the filesystem?
Should I use a #temp table or a @table variable?
Should I use a view, a stored procedure, or a user-defined function?
Should I use recordset iteration, or GetRows(), or GetString()?
What are all these dt_ stored procedures, and can I remove them?
What are the limitations of MS Access?
What are the limitations of MSDE?
What are the valid styles for converting datetime to string?
What datatype should I use for my character-based database columns?
What datatype should I use for numeric columns?
What does "ambiguous column name" mean?
What is this 'Multiple-step OLE DB' error?
What is wrong with 'SELECT *'?
What naming convention should I use in my database?
What should I choose for my primary key?
What should my connection string look like?
When should I use CreateObject to create my recordset objects?
Where can I get this 'Books Online' documentation?
Where do I get MSDE?
Which database platform should I use for my ASP application?
Which tool should I use: Enterprise Manager or Query Analyzer?
Why are there gaps in my IDENTITY / AUTOINCREMENT column?
Why can I not 'open a database created with a previous version...'?
Why can't I access a database or text file on another server?
Why can't I use the TOP keyword?
Why do I get 'Argument data type text is invalid for argument [...]'?
Why do I get 'Not enough space on temporary disk' errors?
Why does ASP give me ActiveX errors when connecting to a database?
Should I use COALESCE() or ISNULL()?
Where can I get basic info about using stored procedures?
Posted by Jun Hsieh at 10:16 AM
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)