3

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!

Crab Bucket
  • 6,219
  • 8
  • 38
  • 73
TransformBinary
  • 268
  • 3
  • 17
  • The solution is actually going to depend a LOT on what your database back end is: name and version. For example, MySQL has the LIMIT command which is fantastic for paging operations. Paging is certainly possible with MS SQL, but there were radical feature changes between 2005 and 2008 that changed the approach. Also, you should post what all of the features are that you need. For example, will you allow them to sort based on clicking column headers or are you simply emitting X number of records with a paging control on the bottom – NotMe Feb 03 '12 at 19:01

1 Answers1

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.

Community
  • 1
  • 1
Crab Bucket
  • 6,219
  • 8
  • 38
  • 73
  • 3
    If you're going to use Linq's skip/take method then I'd take a look at the SQL that's actually being generated to ensure that it's actually doing it reasonably efficiently (as opposed to say fetching them all and only skipping them client side). – Servy Feb 03 '12 at 19:20
  • What I was going to say is that you do need to ensure that the Skip and Take execute on the database and you aren't inadvertly ToList or something then realising them - good point - i will edit – Crab Bucket Feb 03 '12 at 19:23