ROW_NUMBER() OVER Not Fast Enough With Large Result Set, any good solution?

Question

I use ROW_NUMBER() to do paging with my website content and when you hit the last page it timeout because the SQL Server takes too long to complete the search.

There's already an article concerning this problem but seems no perfect solution yet.

http://weblogs.asp.net/eporter/archive/2006/10/17/ROW5F00NUMBER28002900-OVER-Not-Fast-Enough-With-Large-Result-Set.aspx

When I click the last page of the StackOverflow it takes less a second to return a page, which is really fast. I'm wondering if they have a real fast database servers or just they have a solution for ROW_NUMBER() problem?

Any idea?

Answer 1

Years back, while working with Sql Server 2000, which did not have this function, we had the same issue.

We found this method, which at first look seems like the performance can be bad, but blew us out the water.

Try this out

DECLARE @Table TABLE(
        ID INT PRIMARY KEY
)

--insert some values, as many as required.

DECLARE @I INT
SET @I = 0
WHILE @I < 100000
BEGIN
    INSERT INTO @Table SELECT @I
    SET @I = @I + 1
END

DECLARE @Start INT,
        @Count INT

SELECT  @Start = 10001,
        @Count = 50

SELECT  *
FROM    (       
            SELECT  TOP (@Count)
                    *
            FROM    (
                        SELECT  TOP (@Start + @Count)
                                *
                        FROM    @Table
                        ORDER BY ID ASC
                    ) TopAsc
            ORDER BY ID DESC
        ) TopDesc
ORDER BY ID

Answer 2

The base logic of this method relies on the SET ROWCOUNT expression to both skip the unwanted rows and fetch the desired ones:

DECLARE @Sort /* the type of the sorting column */
SET ROWCOUNT @StartRow
SELECT @Sort = SortColumn FROM Table ORDER BY SortColumn
SET ROWCOUNT @PageSize
SELECT ... FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn

The issue is well covered in this CodeProject article, including scalability graphs.

TOP is supported on SQL Server 2000, but only static values. Eg no "TOP (@Var)", only "TOP 200"