Paging in a GridView using SqlDataSource
I have a GridView that is accessing data from a SqlDataSource in DataSet mode. I have paging enabled and it works, but on large datasets it takes an extremely long time to fetch the data.
The SqlDatSource seems to fetch all the data and then dump it at the UI level. This is obviously a lousy decision. Have I looked at tips for speeding up the SqlDataSource? and it seems to be one topic - it looks like I will need to inject some swap code into my SELECT, I'm just not sure how. I don't mind pulling it into SProc if I have to, but leaving the SELECT command in the SqlDataSource is better.
I know MySQL has LIMIT X, Y (where X is the number of rows to retrieve and Y is the offset). TOP doesn't seem to do the same, and I don't know how to get the swap information from the GridView to the SqlDataSource.
Is this the best way to do it? (And if so, where to start?) Or is there a better way to get efficient paging from the SqlDataSource to the GridView?
(I'm using C # if it matters, but I don't think it should.)
Thanks.
a source to share
ROW_NUMBER () is your friend, use as 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
the app keeps track of what is displayed on the page. If you want to show 10 rows on page return 1-11, display 1-10, if rowcount == 11 will display next link.
a source to share