Paging through Records using a Stored Procedure

This Stored Procedure Has Been Updated!
This stored procedure has been modernized and optimized and you are encouraged to use the new script as opposed to this older one. See Efficiently Paging Through Large Result Sets in SQL Server 2000 for the new and improved version of this script. Also check out A More Efficient Method for Paging Through Large Result Sets for an even more optimized version!

As an ASP developer, I am constantly looking for new source code examples that will help make my job easier. Of course, using someone else's examples is far simpler than enduring the pain of the development process.

One of the examples I have seen on various sites involves paging through recordsets on a web page N records at a time. Most developers want to do this rather than present the entire recordset on a single page. First, this has the advantage of making faster page loads for site visitors, and secondly it makes for nicer pages.

All of the examples I have seen use client-side cursors to move to an absolute page in the recordset. While this works quite nicely, there is one disadvantage that developers don't consider. In most cases, the server where the database resides is separate from the web server. Whenever a request is made to the database server, it returns the entire recordset to the web server, which then uses the client-side cursor to only select the group of records requested for the given page. This does not seem like a problem until you consider what happens when you are working with large databases and multiple concurrent users. If you are only requesting 40 records at a time from a 100,000 record database table, and you have 5 people make the same request, you are now sending 500,000 records to the web server for only 200 records of output to the client. This can be an enormous problem!

Working with my database administrator, I developed a stored procedure for use with SQL Server that puts the burden on the SQL Server to only return the required number of records to the web server instead of the entire database table. This GREATLY reduces the load factor on the web server when making database requests. The explanation of the code follows the stored procedure. In this example, we are retrieving item pricing information from a table and returning N records.

CREATE PROCEDURE sp_PagedItems ( @Page int, @RecsPerPage int ) AS -- We don't want to return the # of rows inserted -- into our temporary table, so turn NOCOUNT ON SET NOCOUNT ON --Create a temporary table CREATE TABLE #TempItems ( ID int IDENTITY, Name varchar(50), Price currency ) -- Insert the rows from tblItems into the temp. table INSERT INTO #TempItems (Name, Price) SELECT Name,Price FROM tblItem ORDER BY Price -- Find out the first and last record we want DECLARE @FirstRec int, @LastRec int SELECT @FirstRec = (@Page - 1) * @RecsPerPage SELECT @LastRec = (@Page * @RecsPerPage + 1) -- Now, return the set of paged records, plus, an indiciation of we -- have more records or not! SELECT *, MoreRecords = ( SELECT COUNT(*) FROM #TempItems TI WHERE TI.ID >= @LastRec ) FROM #TempItems WHERE ID > @FirstRec AND ID < @LastRec -- Turn NOCOUNT back OFF SET NOCOUNT OFF

In this code, first we are declaring to integer values (@Page and @RecsPerPage) which you supply as parameters when calling the stored procedure. @Page is the page number you wish to retrieve, and @RecsPerPage is the number of records you wish to return each time the procedure is called. The next step is to create a temporary table called #TempItems. Temporary tables are voided once the stored procedure has completed and do not take up physical space on the hard drive. The key to making this stored procedure work is the fact that we are creating an auto-incrementing colum called ID. This column is used to identify the record number of each record in the table, and is an integral part of the paging code. We are then going to populate the temporary table with records from the actual tblItem table using a SELECT statement.

Next, we are calculating the first and last record numbers we need from the temporary table, based on the number of records per page and the page number we want, and saving those values into variables called @FirstRec and @LastRec.

Finally, we are returning the desired records from #TempItems (based on the value of ID). That's it! We're also returning a row that indicates how many records we have left to iterate through. This information is important, because if we want to show Next, Prev buttons, this helps us determine if, for a given page, we want to show a Next button.

To use this stored procedure from an ASP page, you could use:

<!-- This assumes you already have a recordset object explicitly created name objRS --> <% 'How many records per page do we want to show? Const iRecordsPerPage = 10 Dim currentPage 'what page are we on?? Dim bolLastPage 'are we on the last page? if len(Request.QueryString("page")) = 0 then currentPage = 1 else currentPage = CInt(Request.QueryString("page")) end if 'Show the paged results strSQL = "sp_PagedItems " & currentPage & "," & iRecordsPerPage objRS.Open strSQL, objConn 'See if we're on the last page if Not objRS.EOF then if CInt(objRS("MoreRecords")) > 0 then bolLastPage = False else bolLastPage = True end if end if %> <P> <TABLE BORDER=0 CELLSPACING=1 CELLPADDING=4 ALIGN=CENTER> <TR><TH COLSPAN=2 BGCOLOR=NAVY> <FONT SIZE=+1 COLOR=WHITE> List of Items </FONT> </TH></TR> <% Do While Not objRS.EOF %> <TR><TD ALIGN=LEFT BGCOLOR=GRAY> <%=objRS("Name")%> </TD><TD ALIGN=CENTER BGCOLOR=GRAY> <%=FormatCurrency(objRS("Price"))%> </TD></TR> <% objRS.MoveNext Loop %> </TABLE> <P> <CENTER> <% 'Only show the previous button if we are NOT on the first page if currentPage > 1 then %> <INPUT TYPE=BUTTON VALUE="<< Previous <%=iMaxRecords%> Records" ONCLICK="document.location.href='thispage.asp?page=<%=currentPage-1%>'"> &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <% end if 'Only show the next button if we are NOT on the last page if Not bolLastPage then %> <INPUT TYPE=BUTTON VALUE="Next <%=iMaxRecords%> Records >>" ONCLICK="document.location.href='thispage.asp?page=<%=currentPage+1%>'"> <% end if %> </CENTER>

This will return a recordset to the web server that contains 10 records starting at page #1.

A Major Improvement in Performance
Don't use the stored procedure discussed in this article, as there are much more efficient techniques. See A More Efficient Method for Paging Through Large Result Sets for an alternative approach that's more than an order of magnitude more efficient than the technique presented here.

Feel free to use this code for yourself. It will give you good performance and a much faster result because the workload is on the SQL Server. Good luck!

Extending the sp_PagedItems Stored Procedure

The sp_PagedItems stored procedure presented in this article is ideal for paging the results of a static database query. If, however, you want to be able to dynamically alter the SQL statement without having to completely rewrite the stored procedure to accept a dynamic WHERE clause. This method is shown in another 4Guys article: Creating an HTML Table with Orderable Columns. (Also, if you wish to implement this technique you should make sure to take a moment and read: Using Dynamic SQL Statements in Stored Procedures.)

Attachments: