A little while ago, Scott Guthrie talked about record paging in his post "Paging through lots of data efficiently (and in an Ajax way) with ASP.NET 2.0". The important part of the article doesn't have much to do with ASP.NET or Ajax, but rather the new ranking capabilities found in SQL Server 2005. If you've done data paging before, or just think about it for a second, that shouldn't be much of a surprise. Without question, "efficiently" paging through data has to happen directly on the database.

The alternative to paging data at the database is to page it in ASP.NET. The DataGrid and GridView controls make it easy to page data in ASP.NET, but there's a price to be paid - performance. I know a lot of people are put off by micro optimizations (which you should be), but in my experience, paging even a bit of data under moderate load can have significant performance implications. Instead of simply returning the 10 rows from the database, you need to return all of the rows and let ASP.NET figure our which are the right ones to display. If your data is wide (a lot of columns) or you have a lot of rows, your site will suffer significant performance issues.  You should only use the built-in paging when you know you have very few records - say under a thousand - oh, and make sure to optimize that viewstate!

Before SQL Server 2005 came out, paging was frustratingly difficult. Ideally you'd be able to rely on an auto-genarated id and simply pick all records where the ID is between 1-10, 11-20, 21-30...But that only works if you can guarantee that there are no holes in your IDs, which likely you can't. One solution, the one I prefer, is to create a temp table (or a table data type), that has an auto id in it. Something like:

DECLARE @records TABLE(RowId INT IDENTITY(1,1), TableId INT)

INSERT INTO @records
   SELECT ProductId
   FROM Products
   WHERE Price <= @MaxPrice
      AND Price >= @MinPrice
      AND Category = @CategoryId
   ORDER BY ProductId

--get the total number of matching products
SELECT COUNT(*) FROM @records

--get the data
SELECT p.ProductId, [Name], Price, ImageUrl
   FROM Products p INNER JOIN @records r ON p.ProductId = r.TableId
   WHERE p.RowId > 0 AND p.RowId < 11 --these would be passed in


Although just a sample, the above code shows how you can create a temp table and create a series of id's with no holes. The temp table is kept extremely narrow (2 small columns) and short (the filtering is applied upfront), and is joined back against the main table. It ends up working very well. But it's a serious pain in the ass - especially when you consider how basic this is.

Since the dawn of SQL Server, people have been going about banging their heads on the best way to do this, begging Microsoft to implement a good solution. Disappointed by SQL Server 7 and 2000, the team behind 2005 finally decided to help out. With the new ranking functions, we end up with:

WITH PagedProducts AS
   (
      SELECT ROW_NUMBER() OVER (ORDER BY ProductId) AS RowNumber, ProductId, [Name], Price, ImageUrl
      FROM Products
      WHERE Price <= @MaxPrice
         AND Price >= @MinPrice
         AND Category = @CategoryId

   )
SELECT ProductId, [Name], Price, ImageUrl
FROM PagedProducts
WHERE RowNumber BETWEEN 1 AND 10

It's a step in the right direction, but I consider the above code overly complicated for the common task we are trying to accomplish. Why did the SQL team decide not to implement the oft-begged for MySQL LIMIT keyword? Even if LIMIT simply abstracts away the above code, it's embarrassingly simple:

SELECT ProductId, [Name], Price, ImageUrl FROM Products LIMIT 1,10

The ranking functions are meant to do more than just paging and that's great if you want to do more than just paging. But I'm convinced that paging is so fundamentally necessary to most applications, it deserves it's own straightforward keyword.

If you need to page results, MySQL's LIMIT keyword continues to be more handy than anything the .NET or SQL teams have managed to do.

---

Posted Mon, Apr 17 2006 5:00 AM by karl