Paging in GridView using SqlDataSource

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 http://stackoverflow.com/questions/685980/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.

I know MySQL has LIMIT X,Y (where X is number of rows to retrieve and Y is offset). TOP doesn't seem to do the same thing, and I don't know how to get the paging information from the GridView into the SqlDataSource either.

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.)

Thanks.

Answer 1

ROW_NUMBER() is your friend, use like in this example:

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

have the application keep track of what is showing on a page. If you want to show 10 rows on a page return 1-11, display 1-10, if rowcount==11 display "next" link.