Paging with PagedList, is it efficient?

Question

I have been trying to implement paging for quite a while now and I found this tutorial for paging with MVC: ASP.NET MVC Paging Done Perfectly

Now, in this solution, I query the database for the entire set of clients and then I return a paged list of clients instead of a normal list.

I find this disturbing, because I only plan to show 10 or 20 entries per page, and my database will easily have over a million of them. Thus, querying the entire database each time I want to show the Index page seems to be a poor solution at best.

If I am understanding something wrong, please feel free to cut me right now, but for me this solution is anything but perfect.

Have I misunderstood something? Is there a more efficient solution or library out there for pagination with MVC?

Answer 1

Naturally paging will require knowledge of the total result count in order for the logic to determine how many pages there are etc. However instead of bringing down all the results just build your query to the Database to return the paged amount (e.g 30) and as well as the count of all the results.

For example, if you were using Entity Framework, or LINQ2SQL you could do something like this

IQueryable<Result> allResults = MyRepository.RetrieveAll();

var resultGroup = allResults.OrderByDescending(r => r.DatePosted)
                                               .Skip(60)
                                               .Take(30)
                                               .GroupBy(p => new {Total = allResults.Count()})
                                               .First();

var results = new ResultObject
{
    ResultCount = resultGroup.Key.Total,
    Results = resultGrouping.Select(r => r)
};

Because we haven't done a .ToList() on our result set until we have finalised what we want, we haven't brought the results into memory. This is done when we call the .First() on our result set.

Finally our Object that we end up with (ResultObject) can be used to then do the paging later on. As we have the count, we already know what page we are on (3 as we skipped 60, with 30 per page) and we have the results to display.

Further Reading and Information

How To: Page through Query Results

Server Side Paging with Entity Frame

Answer 2

If you go to the github page of the PagedList addon you can see that if you have a method returning an IQueryable<T> then the PagedList magic can work on that without returning every item from the database. If you cannot control what the query from the database returns to you then you have to rely on other methods.

The example from that page is this

public class ProductController : Controller
{
    public object Index(int? page)
    {
        var products = MyProductDataSource.FindAllProducts(); //returns IQueryable<Product> representing an unknown number of products. a thousand maybe?

        var pageNumber = page ?? 1; // if no page was specified in the querystring, default to the first page (1)
        var onePageOfProducts = products.ToPagedList(pageNumber, 25); // will only contain 25 products max because of the pageSize

        ViewBag.OnePageOfProducts = onePageOfProducts;
        return View();
    }
}

Answer 3

The example on github illustrates that it is using an IQueryable which is then utilised by ToPagedList(), which implies that the code is pretty optimised and will not in itself return all records...

Looking at the code of class PagedList

// superset is the IQueryable.
TotalItemCount = superset == null ? 0 : superset.Count();

// add items to internal list
if (superset != null && TotalItemCount > 0)
    Subset.AddRange(pageNumber == 1
    ? superset.Skip(0).Take(pageSize).ToList()
    : superset.Skip((pageNumber - 1) * pageSize).Take(pageSize).ToList()

So as you can see it already uses the recommended server side paging methods of skip and take and then preforms a ToList().

However if you were not working with an IQueryable, i.e. an IEnumerable then the following code is used:

/// <summary>
/// Initializes a new instance of the <see cref="PagedList{T}"/> class that divides the supplied superset into subsets the size of the supplied pageSize. The instance then only containes the objects contained in the subset specified by index.
/// </summary>
/// <param name="superset">The collection of objects to be divided into subsets. If the collection implements <see cref="IQueryable{T}"/>, it will be treated as such.</param>
/// <param name="pageNumber">The one-based index of the subset of objects to be contained by this instance.</param>
/// <param name="pageSize">The maximum size of any individual subset.</param>
/// <exception cref="ArgumentOutOfRangeException">The specified index cannot be less than zero.</exception>
/// <exception cref="ArgumentOutOfRangeException">The specified page size cannot be less than one.</exception>
public PagedList(IEnumerable<T> superset, int pageNumber, int pageSize)
        : this(superset.AsQueryable<T>(), pageNumber, pageSize)
    {
    }

The issue is that depending upon the filtering utilised to get the IEnumerable in the first place could contain all records, so use an IQueryable where possible for optimal performance of PagedList.

Answer 4

The linked tutorial looks odd, because it uses a List<Client>. This will indeed bring all the clients into memory and then page through that. Instead, you should look for methods that use IQueryable<T>, specifically Skip and Take, so paging should look like

IQueryable<Client> clients = repo.GetClients();          // lazy load - does nothing
List<Client> paged = clients.Skip(20).Take(10).ToList(); // execute final SQL

Depending on what mappers you use, you will find similar methods in EF, NHibernate, Linq-to-SQL etc

Answer 5

You have three ways to implement pagination in your application:

• Force your Repository to return the DTOs with minimal amount of data back to the client, and use some of the jquery plug-ins providing the pagination by themselves. This is a simple way, but sometimes (as in your case) this isn't an option. So you have to implement server-side pagination
• Cache whole collection, and return the needed page with LINQ extension. I think that many of the repositories and ORM does this internally (didn't work with Entity Framework, can't say for sure). Problem with this solution is that you have to sync cache and db, and you have to get server with enough memory to store all of your data (or go cloud, or something). As in other answers, you can skip not-needed data with lazy IEnumerable work, so you don't need to cache the collection..
• Implement pagination on the DB-side. If you are using SQL, you can use ROW_NUMBER construction, it works either in MS SQL or in Oracle or in MySQL (not ROW_NUMBER itself actually, only analog). If you have NoSQL solution, then you have to check documentation for it.