Efficient paging in Web Application

Question

I'm new to paging in web applications. I searched the web and found lots of tutorial on paging for web site(Create new website in VS). I cant use the current method of enable paging in vs which can be done easily because its not efficient & retrieve the whole table of data from database.

Default paging—Can be implemented by just checking the Enable Paging option in the data Web control's smart tag. However, whenever viewing a page of data, the ObjectDataSource retrieves all of the records, even though only a subset of them is displayed in the page.

Custom paging—Improves the performance of default paging by retrieving only those records from the database that must be displayed for the particular page of data requested by the user. However, custom paging involves a bit more effort to implement than default paging.

I'm looking for a custom paging for web application, hope you guys can help me with it. I found 2 links which i think might be custom paging but i'm not sure which part of the code say so, so it would be nice if you can tell me which part of the code actually make it efficient Thanks!

The 2 links are http://www.codeproject.com/Articles/170921/MvcContrib-Grid-Paging-and-Searching-in-ASP-NET-MV and http://blogs.msdn.com/b/codefx/archive/2009/09/07/how-to-implement-insert-edit-delete-paging-and-sorting-functions-in-an-asp-net-gridview-control.aspx?CommentPosted=true#commentmessage

Thanks again!

Answer 1

I'm not convinced either of your links demonstrate efficent paging

link 1 - the LINQ example In order for this to be effificent I would expect to see something in the form

var myDataSource = data.Select(x => x.Parameter = "input").Skip(1).Take(10);

The principle is that the skip and take methods just retrieve the page of data that you want

link 2 - the SQL Example

Again not convinced - I would expect to see something in the SQL that uses ROW_OVER() or some other evidence of the SQL only bringing back the page of data that you want. This link gives an example of using ROW_OVER with SQL Server 2005 (2008 might have improved - i don't know TBH - someone else might and i would be interested).

Generally

I you have ASP.Net 3.5 or higher I would use the LINQ example - it's a lot more straight forward than trying to do it in SQL

This link gives a better example using LINQ with the Take and Skip operators to do efficient paging with a ListView. It also addresses the issue of having to get a total count of your records in order to display the number of pages which is a common requirement.

Also - this SO question gives some very good examples of efficient paging so I do recommend you read that.

LINQ Caution

As the commenter below pointed out - you need to ensure that skip and take are executing on the DB not the entire dataset being realised, brought back and the Skip and Take executing on the client. Profiler is your friend here. Also is worth knowing which operators realise the query and fire it off to the database.

var myDataSource = data.Select(x => x.Parameter = "input").Skip(1).Take(10);

above probably OK

var myDataSource = data.Select(x => x.Parameter = "input")
                                  .ToList().Skip(1).Take(10);

Ooops no good. The ToList() method will cause the SQL to be fired off to the database before Skip and Take has done it's work.