//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   Alerts
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Other Articles :: How do I page through a recordset?


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=<server>;" & _ 
        "Initial Catalog=<databasename>;" & _ 
        "User ID=<username>;" & _ 
        "Password=<password>" 
%> 
<style> 
.n { border-top:1px solid #999999 } 
</style>
 
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 < 1 Then PageNum = 1 
    if PageNum > PageCnt Then PageNum = PageCnt 
 
    url = Request.ServerVariables("SCRIPT_NAME") 
    urlParts = split(url, "/") 
    pageName = urlParts(ubound(urlParts)) 
%> 
<script> 
function go(p) 

    if (p!='current') 
    window.location.href = "<%=pageName%>?" 
        +"PerPage=<%=PerPage%>&" 
        +"PageNum="+p; 

</script> 
<% 
    response.write RowCnt & " rows found. Showing " & _ 
        " page " & PageNum & " of " & PageCnt & "." 
 
    response.write " <select onchange='go(this.value);'>" 
 
for i = 1 to PageCnt  
        link = i: s = "" 
        if i = PageNum then link = "current": s=" SELECTED" 
        response.write "<option value=" & link & s & ">" 
        response.write "Page " & i 
next  
Response.Write "</select><p>" 
%>
 
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: 
 
<!--#include file=inc.asp--> 
<!--#include file=topRS.asp--> 
<% 
    ' ... code ... 
%> 
<!--#include file=foot.asp-->
 
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). 
     
    <!--#include file=inc.asp--> 
    <!--#include file=topRS.asp--> 
    <% 
        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 "<table border=0 cellpadding=5>" 
     
            rs.AbsolutePage = PageNum 
     
            x = 0 
            do while x < PerPage and not rs.eof 
                 
                artist = rs(0)  
                title = rs(1) 
     
                if artist <> prevArtist then 
                    prevArtist = artist  
                    response.write "<tr><td class=n>" 
                    response.write artist & "</td>" 
                    response.write "<td class=n>" 
                else 
                    response.write "<tr><td> </td><td>" 
                end if  
     
                response.write title & "</td></tr>" 
     
                x = x + 1 
     
                rs.movenext  
            loop  
     
            response.write "</table>" 
        else 
            response.write "No rows found." 
            response.end 
        end if 
    %> 
    <!--#include file=foot.asp-->
     
    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. 
     
    <!--#include file=inc.asp--> 
    <!--#include file=topRS.asp--> 
    <% 
        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 "<table border=0 cellpadding=5>" 
     
            for i = 0 to perpage - 1 
                 
                artist = gr(0, i) 
                title = gr(1, i) 
     
                if artist <> prevArtist then 
                    prevArtist = artist  
                    response.write "<tr><td class=n>" 
                    response.write artist & "</td>" 
                    response.write "<td class=n>" 
                else 
                    response.write "<tr><td> </td><td>" 
                end if  
     
                response.write title & "</td></tr>" 
     
            next  
     
            response.write "</table>" 
        else 
            response.write "No rows found." 
            response.end 
        end if 
    %> 
    <!--#include file=foot.asp-->
     
    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.  
     
    <!--#include file=inc.asp--> 
    <!--#include file=topRS.asp--> 
    <% 
    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 "<table border=0 cellpadding=5>" 
     
            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 "<tr><td class=n>" 
                    response.write artist & "</td>" 
                    response.write "<td class=n>" 
                else 
                    response.write "<tr><td> </td><td>" 
                end if  
     
                response.write title & "</td></tr>" 
     
                rs.movenext  
            next  
     
            response.write "</table>" 
        else 
            response.write "No rows found." 
            response.end 
        end if 
    %> 
    <!--#include file=foot.asp-->
     
    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. 
     
    <!--#include file=inc.asp--> 
    <!--#include file=topRS.asp--> 
    <% 
        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 "<table border=0 cellpadding=5>" 
     
            for i = 0 to perpage-1 
                 
                artist = gr(0, i) 
                title = gr(1, i) 
     
                if artist <> prevArtist then 
                    prevArtist = artist  
                    response.write "<tr><td class=n>" 
                    response.write artist & "</td>" 
                    response.write "<td class=n>" 
                else 
                    response.write "<tr><td> </td><td>" 
                end if  
     
                response.write title & "</td></tr>" 
     
            next  
     
            response.write "</table>" 
        else 
            response.write "No rows found." 
            response.end 
        end if 
    %> 
    <!--#include file=foot.asp-->
     
    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)) 
%> 
<script> 
function go(p) 

    if (p!='current') 
    window.location.href = "<%=pageName%>?" 
        +"PerPage=<%=PerPage%>&" 
        +"PageNum="+p; 

</script> 
<% 
    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 " <select onchange='go(this.value);'>" 
 
for i = 1 to PageCnt  
        link = i: s = "" 
        if i = PageNum then link = "current": s=" SELECTED" 
        response.write "<option value=" & link & s & ">" 
        response.write "Page " & i 
next  
Response.Write "</select><p>" 
%>
 
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: 
 
<!--#include file=inc.asp--> 
<!--#include file=topSP.asp--> 
<% 
    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 "<table border=0 cellpadding=5>" 
 
        for i = 0 to rstop 
             
            artist = gr(0, i) 
            title = gr(1, i) 
 
            if artist <> prevArtist then 
                prevArtist = artist  
                response.write "<tr><td class=n>" 
                response.write artist & "</td>" 
                response.write "<td class=n>" 
            else 
                response.write "<tr><td> </td><td>" 
            end if  
 
            response.write title & "</td></tr>" 
 
        next  
 
        response.write "</table>" 
    else 
        response.write "No rows found." 
        response.end 
    end if 
 
%> 
<!--#include file=foot.asp-->
 
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 < 1 SET @pagenum = 1 
        IF @pagenum > @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 < 1 SET @pagenum = 1 
        IF @pagenum > @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 < 1 SET @pagenum = 1 
        IF @pagenum > @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 < 1 SET @pagenum = 1 
        IF @pagenum > @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 < 1 SET @pagenum = 1  
        IF @pagenum > @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 < 1 SET @pagenum = 1 
        IF @pagenum > @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 & "<P>" 
 
    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 & ")<br>" 
        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 & ")<br>" 
        originalClock = newCLock 
    next  
    response.write "<P>" & 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 <text>?
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?

 

 


Created: 7/9/2000 | Last Updated: 9/14/2007 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (62)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...