Wednesday, January 04, 2006

Paged recordsets from SQL Server for web pages

I've worked on a few web apps where I displayed a paged list of data, but without storing all of the rows in the view state or as a session variable. I prefer to let SQL Server do the heavy lifting for these kinds of situations. What I want to do is to get a set of filtered data from SQL Server (2000 or better, my friend) with arbitrary row numbers assigned so that I can ask for all rows between X and Y.

There are a few ways to do this, one way I like to do it is with a stored procedure that generates the data and sends back only what I need. This eliminates most of the storage requirements on the web side of things. Using a sample table, I'll write a procedure that lets me grab the data by row numbers.

create table Employee (
  RecordID integer identity(1,1),
  LastName varchar(30),
  FirstName varchar(20),
  IsDriver char(1)
)

Assume about 1000 or so records, with about 50% of them with the IsDriver field set to 'Y'. Here's a procedure for getting a set of data with row numbers included.

CREATE procedure QueryByRow
  @LastName varchar(20),
  @StartRecord int,
  @EndRecord int
AS
DECLARE @MatchCount int

SELECT @MatchCount =
(
  SELECT COUNT(RecordID)
  FROM EMPLOYEES
  WHERE IsDriver='Y' AND LastName LIKE @LastName
)

DECLARE @tmp TABLE(ID int identity(1,1), RecordID int, LastName varchar(20), FirstName varchar(20))

SET NOCOUNT ON

INSERT @tmp(RecordID, LastName, FirstName)
SELECT RecordID, LastName, FirstName
FROM EMPLOYEES
WHERE IsDriver='Y' AND LastName LIKE @LastName
ORDER BY LastName, FirstName

SET NOCOUNT OFF

SELECT @MatchCount AS Count, t.RecordID, t.LastName, t.FirstName
FROM @tmp t
WHERE t.ID BETWEEN @StartRecord AND @EndRecord

This procedure only allows us to filter by last name, but it can be easily extended to do other filtering or even change the sort order. You would start off by calling the procedure with the RecordCount to get your first page by the starting and ending row numbers. You will get back the result set, with the total number of records as the first column. That information you would store as a session variable or in the viewstate. Now that you know the number of records, you can then get any arbitrary set of those records by calling the procedure again, but with different starting and ending row numbers.

EXECUTE QueryByRow '%', 1, 25

Count       RecordID    LastName             FirstName            
----------- ----------- -------------------- --------------------
494         1           Able                John
494         36          Baker               John
494         69          Charles             John
....
494         6           Dexter              John


If you were showing 25 records per page, and you wanted to display page 3, you would do this:

EXECUTE QueryByRow '%', 0, 51, 75

That would return something like this:

Count       RecordID    LastName             FirstName            
----------- ----------- -------------------- --------------------
494         2           Marro                John
494         46          Martinez             John
494         79          Mitchell             John
....
494         16          Schwede              John


This would bring back just the 25 rows that you would need. There is, of course, no free lunch. The drawback is that you are executing the same query against the data every time you call this procedure. You have to weigh the performance of that versus the performance of retrieving a full set of data from the SQL Server to the web server and having the web server filter and persist the data. Depending on your data and the load on your server, SQL Server will have the result set cached in memory and each additional call the procedure will be running against data already in memory.

The use of a table variable allows to build the set in memory and create the row numbers. I have seen other examples that used temporary tables and self joins on the table, but table variables seem to place the least demand on the sever resources. This method will work with both SQL Server 2000 and 2005 and is not dependant on any version of ASP.Net.

The other drawback is that other processes could be editing the table between calls to the procedure. This is why the "Count" field is returned with each row. Should that value change between calls to the procedure then you know the data was edited in some way and you would have to make sure that your code could display a different number of records than it expected.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.