Improving paging performance for gridview?

Question

I am working on a small project that requires gridview paging for upto 100k records. what are the ways i can improve the performance. I tried to page using sql server with Temp table but it still is a bit on the slower side.

any idea?

Answer 1

You could add a stored proc that will return only a range of rows. Have the page keep track of what the current page you are on is, and request only the next x or previous x rows.

for example:

@firstRow   int,
@lastRow    int
select ROW_NUMBER() over (order by [MyField]) as rowNum, * 
from [MyTable] 
where rowNum between @firstRow and @lastRow 

Answer 2

Not sure Tj Kellie's idea will work. Here's an improvement:

select * from
(
select ROW_NUMBER() over (order by [MyField]) as rowNum, * 
from [MyTable] 
) 
as TableWithRows
Where Row<=20 and Row>=10

Will return rows 10 to 20. You can improve it further by adding select TOP [LastRow] before the ROW_NUMBER() function

Answer 3

Correction in query select * from ( select ROW_NUMBER() over (order by [MyField]) as rowNum, * from [MyTable] ) as TableWithRows Where RowNum<=20 and RowNum>=10