Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
496 views
in Technique[技术] by (71.8m points)

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

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...