Paging through Records using a Stored Procedure
By Daniel Anderson
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.
|
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 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:
Daniel Anderson is a web developer for one of the largest civil engineering companies in the world. Your comments are always welcome at dcanderson@uswest.net