Paging in SQL 2005 like a ninja

I'll be honest, I really haven't looked much at SQL Server 2005 because I really didn't have any incentive to. Sure, I've been using the Express version in production and it's super sweet, but honestly I haven't bothered looking any deeper into it. I did get the programming book Microsoft put out, and even read the new stuff, but didn't think much about it.

I realized in rewriting my forum app that the bane of my existence, in terms of performance, has always been paging results. There has never been a great way to do it in the 2000 days, but you could do OK with some nested queries and cursors and things. That's how I've been doing it for a long time, as most articles online led me in that direction. The down side is that you still can't avoid scanning the entire table, at least for the primary keys.

But porting over some data to my test forum, I saw some render times I wasn't really happy with. My goal has always been to keep render times below one-tenth of a second if at all possible, and generally I can get there, until you get into these paging situations where there are tens of thousands of records. I was seeing times as high as a third of a second, and I wasn't happy about it.

Then by chance, I came across the concept of common table expressions (CTE's), and the function ROW_NUMBER(). Combining the best of both worlds, I finally came up with something that was performing extremely well. There are a whole bunch of different articles that got me there, and Googling for certain words will easily get you there. Here's the sproc that gets pages of topics:

CREATE PROCEDURE pf_PagedTopics(
@StartRow int,
@PageSize int,
@ForumID int
)

 AS

DECLARE @Counter int
SET @Counter = (@StartRow + @PageSize)

SET ROWCOUNT @Counter;

WITH Entries AS (
SELECT ROW_NUMBER() OVER (ORDER BY IsPinned DESC, LastPostTime DESC)
AS Row, pf_Topic.TopicID, ...
FROM pf_Topic WHERE ForumID = @ForumID AND IsDeleted = 0)

SELECT TopicID, ...
FROM Entries
WHERE Row between
@StartRow and @StartRow + @PageSize

SET ROWCOUNT 0

GO

Here's a breakdown of what goes on. The ROWCOUNT is set because presumably even in the CTE it makes sense to stop reading when you have enough rows. I'm stopping with last record I should get. The "Entries" CTE is pretty straight forward, the interesting part being that the ORDER BY is at the top, and we're calling this first column in the CTE "Row." After that, you just select the columns in "Entries" without the "Row" column.

While I'm getting those sub-tenth of a second page renders I've always dreamed about, the number of reads in the trace is still a little on the high side, at 400+. If I understood more about how this worked, I'd be thrilled, but I might have to revisit this some other day. For now, here's hoping fast disks are enough!
 

5 Comments

  • Here are two blog posts I did about the new ROW_NUMBER() paging support that you might also find useful:

    http://weblogs.asp.net/scottgu/archive/2006/01/01/434314.aspx

    and

    http://weblogs.asp.net/scottgu/archive/2006/01/07/434787.aspx

    Hope this helps,

    Scott

  • That would rock except for the nasty table scan it causes :(

  • So what? What's the alternative, smarty pants? If the performance is acceptable, who cares?

  • That is exactly what we were waiting for... thanks guys

  • It works, but i discovered that instead of

    DECLARE @Counter int
    SET @Counter = (@StartRow + @PageSize)
    SET ROWCOUNT @Counter;

    it can be just simple
    SET ROWCOUNT @PageSize;
    (it looks like SET ROWCOUNT limits the main select not the one in WITH section)

    and "Row between @StartRow and @StartRow + @PageSize" can be replaced by "Row >= @StartRow"

    anyway, thx for the tip!

Comments have been disabled for this content.