Efficient Custom Paging in ASP.NET 2.0 while sorting

I've got an web app in ASP.NET 2.0 in which I need to do paging. My method of data access is to pull a DataSet out of a database call, then convert that to a List<Foo> (where Foo is my type I'm pulling out of the DB) and bind my GridView to it. My rea开发者_StackOverflow社区son for this is that I didn't want to be having to use string indexers on DataTables all through my application, and that I could separate the display logic from the database by implementing display logic as properties on my classes. This also means I'm doing sorting in .NET instead of SQL.

To implement paging, then, I need to pull all Foo out of the database, sort the list, then take what I want out of the full list to display:

List<Foo> myFoo = MyDB.GetFoos();
myFoo.Sort(new Foo.FooComparer());
List<Foo> toDisplay = new List<Foo>();
for (int i = pageIndex * pageSize; i < (pageIndex + 1) * pageSize && i < myFoo.Count; i++)
{
  toDisplay.Add(myFoo[i]);
}
//bind grid

With enough elements, this becomes a source of delay; on my development machine connecting to the test database, it takes almost 0.5 seconds to bind one grid on the screen when pulling 5000 records from the DB.

To solve this problem, am I going to have to move all my display logic to SQL so the sorting and paging can take place there, or is there a better way?

Also, does Linq to SQL solve this? If I'm sorting by a custom property implemented in my .NET class and then using .Skip(pageIndex * pageSize).Take(pageSize), will it convert that to SQL as noted in this question?

---

Yes - I would recommend you move your record selection to SQL (sorting and paging) - the classic way to perform paging in SQL is to use a CTE. I'll find you a good example and update my answer. There's a good example here http://softscenario.blogspot.com/2007/11/sql-2005-server-side-paging-using-cte.html - I googled for "sql paging cte".

---

Linq to SQL will use the Row_Number approach as described in the article and in general it will have performant paging queries to the database.

However there are limits to the amount of data that SQL can page for you and still be performant.

If you have a table with millions or rows in it, the paging functions need to limit the amount of data queried and subsequently paged with the Row_Number approach.

Let's just say you want to page this query :

Select column1, column2, column3 from table1 where column1 > 100

Now let's say that returns 1,000,000 rows. SQL Server still has to run its paging routine over a million rows. That will take a few seconds to page out the result set of the initial query. And it has to do this for every query.

To make sure the performance is maintained, you need to limit the amount of records returned that SQL will page.

Select TOP 10000 column1, column2, column3 from table1 where column1 > 100

Now, even though 1 million records match the query, only 10000 will be paged and this will speed things up to sub second response. In this scenario, the user should be notified that the query that they ran to the database was too broad and they need to narrow the search criteria becauase not all possible results were results.