Paging tons of data in GridView
These days I was working on a web app dealing with
large amount of data.
When it comes to
display these data we use
GridView and enable pagination in one
click.
First thing to know is by default ASP.NET 2.0
GridView default paging is very poor in
terms of performance because it first gets all data from db,
and then trims the results to render the current page. And
it does the same when you change to next page and the
others!
Note that it was the same problem with ASP.NET
1.x DataGrid, then you had to implement custom paging to
make things smart, that means getting only data that you
need to display if you have multiple pages in your grid.
With ASP.NET 2.0 you can very easily use
custom paging, without writing a line of
code behind!
Scott Mitchell
wrote a very good article on how to implement this using the
SQL Server 2005 new
ROW_NUMBER() keyword and configuring your
Object Data Source properly :
Custom Paging in ASP.NET 2.0 with SQL Server 2005
Problem is my current app is using SQL Server 2000...
But
chance is Greg Hamilton wrote a very nice tutorial to do the
same in SQL Server 2000 using the
SET ROWCOUNT command :
A More Efficient Method for Paging Through Large Result
Sets in SQL Server 2000
One more thing on the same idea of performance,
many people, when creating primary key/foreign key
relationships for joigning tables forget that while an index
is automatically created on the primary key column, an
index for a foreign key IS NOT automatically
created, and must be created manually if needed. That could help
if your paging's query use JOIN clauses.
And the
final touch, now that we have Top efficient SQL work, we can
move the GridView inside an
ASP.NET AJAX UpdatePanel and let users
navigate through data without PostBack!
Quick and Smart !