Question

I have a GridView that accesses data from a SqlDataSource in DataSet mode. I have paging enabled and it works, but on large datasets it takes an extraordinarily long time to pull data.

It seems like the SqlDatSource is pulling all the data, and then it s being paged at the UI level. This is obviously a lousy solution. I ve looked at Tips on speeding up a SqlDataSource? and it seems to be on the same topic - it looks like I ll need to implement some paging code into my SELECT statement, I m just not sure how to do that. I m not opposed to pulling it into a SProc if I have to, but leaving the SELECT command in the SqlDataSource would be better.

我知道MySQL有LIMIT X，Y（其中X是要检索的行数，Y是偏移量）。TOP似乎不做同样的事情，我也不知道如何将分页信息从GridView获取到SqlDataSource中。

Is that the best way to do this? (And if so, where do I start?) Or is there a better way to get effective paging from a SqlDataSource in a GridView?

(I m using C# if it matters, but I don t think it should.)

谢谢。 (Xièxiè.)

Answer 1

ROW_NUMBER()是你的朋友，可以像这个例子一样使用：

DECLARE @test TABLE (LastName varchar(25),FirstName varchar(25))
INSERT INTO @test values ( Jones , Billy )
INSERT INTO @test values ( Jones , Suzie )
INSERT INTO @test values ( Jones , Beth )
INSERT INTO @test values ( Jones , Ron )
INSERT INTO @test values ( Jones , Dan )
INSERT INTO @test values ( Smith , Abby )
INSERT INTO @test values ( Smith , Debbie )
INSERT INTO @test values ( Smith , Joe )
INSERT INTO @test values ( Smith , Dan )
INSERT INTO @test values ( Brown , Matt )
INSERT INTO @test values ( Brown , Rob )

;WITH TestRank AS
(
    select
        LastName,FirstName, row_number() over(order by LastName,FirstName) AS RowNumber
        from @test
)
SELECT
    LastName,FirstName
    FROM TestRank
    WHERE RowNumber>=3 AND RowNumber<=5
    ORDER BY RowNumber

让应用程序跟踪页面上显示的内容。如果要在页面上显示10行，请返回1-11，显示1-10，如果rowcount == 11，则显示“下一个”链接。

友情链接