In an earlier article of mine, Custom Paging in ASP.NET 2.0 with SQL Server 2005, I wrote about SQL Server 2005's new ROW_NUMBER() function, which makes efficiently returning a paged result in SQL Server 2005 relatively easy (blog entry). While the SQL Server 2005 technique is neat, not everyone has migrated to SQL Server 2005, and a number of 4Guys readers had asked for an article that examined efficiently paging results with SQL Server 2000. So I wrote the horribly mis-titled article, Efficiently Paging Through Large Results Sets in SQL Server 2000, which showcased the technique I've always used for implementing custom paging in ASP.NET 1.x.
In a word, it is the “wrong” way, as my technique, while more efficient than simply grabbing all of the records blindly as in default paging, is still more than an order of magnitude worse in performance than an improved version. My slow approach used a stored procedure that created a table variable into which the entire contents of the table to be paged through were dumped. This created a “synthetic ID” that could be used to correctly grab just the correct page of data. The inefficiency stemmed from the fact that all of the table's data was being poured into this table variable, which could be prohibitively slow for result sets with as little as tens of thousands of records.
After publishing the article I quickly started receiving emails from folks helpfully pointing out that my technique could be vastly improved upon by using SET ROWCOUNT to reduce the number of records that needed to be injected into the table variable. Using SET ROWCOUNT, you can instruct SQL Server to stop processing a query after a certain number of records have been processed. To retrieve the mth page of data when showing n records per page, one could simply grab the first m*n records and then return the last n records in that subset. When m is sufficiently small enough (that is, when retrieving small page numbers... 1, 2, 10, 15, etc.), m*n is likely to be much smaller than the total number of records in the table, thereby greatly improving performance. (It's a tad embarassing that I've overlooked ROWCOUNT for all these years, seeing as I've written an article on it in the past...)
I was planning on writing an article on this improvement when I got an even more clever suggestion from Greg Hamilton. Greg showed how the table variable could be dispensed of altogether. Rather than move all m*n records into a table variable, just compute the ID of record (m-1)*n+1. This is the ID of the record at the start of page m. Then, use SET ROWCOUNT to just get the next n records after (and including) that ID. The clever part was in efficiently finding the ID value of the (m-1)*n+1 record. Greg did this by using a query like so:
DECLARE @first_id int
SET ROWCOUNT @startRowIndex
SELECT @first_id = ID_Field FROM Table
Such a query assigns the last value of ID_Field in the result set to @first_id. Since the SET ROWCOUNT limits the result set size to the starting row index, the last record returned in the first record of the page to retrieve. Once this value has been obtained, the following query returns the appropriate page of data:
SET ROWCOUNT @recordsPerPage
SELECT ... FROM Table
WHERE ID_Field >= @first_id
Pretty slick! For more on Greg's technique, along with some performance comparisons, check out his article, A More Efficient Method for Paging Through Result Sets.
Learning that my approach that I've used for years had much to be improved upon imparts three important lessons:
- Never assume that your approach/code/algorithm is optimal. There may be (and likely is!) some way in which it can be optimized or better-tuned.
- Due to the breadth of technologies and concepts a web developer must be familiarized with (ASP.NET, SQL, client/server model, client-side script, etc., etc.), it's easy to overlook an important aspect on one area that can greatly affect performance or other areas of usability.
- No matter how much you may know (or think you know :-), there's always new things to learn. I guess this is part of what makes this job so fun - there's always something new to learn!