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(
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, ...
WHERE Row between
@StartRow and @StartRow + @PageSize
SET ROWCOUNT 0
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!