ROW_NUMBER() OVER 对于大的结果集不够快，有什么好的解决方案吗?

问题描述：

我使用 ROW_NUMBER() 对我的网站内容进行分页，当您点击最后一页时它会超时，因为 SQL Server 需要很长时间才能完成搜索.

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

当我单击 StackOverflow 的最后一页时，返回页面所需的时间不到一秒钟，这真的很快.我想知道他们是否有真正快速的数据库服务器，或者他们有 ROW_NUMBER() 问题的解决方案?

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?

有什么想法吗?

答

多年前，在使用没有此功能的 Sql Server 2000 时，我们遇到了同样的问题.

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.

试试这个

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

ROW_NUMBER() OVER 对于大的结果集不够快，有什么好的解决方案吗?

相关推荐