How to solve performance issue when pagination in .net mvc?

Question

I'm using PagedList.MVC nuget, and it takes almost 10 seconds each time I clicked on the next button. I have seen that there are around 350,000 results in the PagedList and each time I clicked on the next button, it goes through all the results again. How should I solve this?

Here is the code.

public ActionResult Index(int? page)
        {
            List<Item> items;
            using (var db = new DBDevEntities())
            {
                items= db.items.ToList(); 
            }

            var pageNumber = page ?? 1;

            return View(items.ToPagedList(pageNumber, 25));
        }

Do not use `List` and `.ToList()` (that is loading all records into memory before you do the paging — , Jan 10 '17 at 21:18
You're loading your entire list every time you page. Cache the value of `items` into a property of your controller (or better yet, implement paging server-side). — Heretic Monkey, Jan 10 '17 at 21:19
@SoheilAlizadeh, Use what? (it needs to be `IQueryable`, not `List`) — , Jan 10 '17 at 21:21
@stephenmuecke also use `AsNoTracking() ` in your code. Ex : `db.itemsAsNoTracking().ToList(); ` Also see this [link](http://stackoverflow.com/questions/12211680/what-difference-does-asnotracking-make) — Soheil Alizadeh, Jan 10 '17 at 21:27
@stephenmuecke So if i'm using entity framework in db first mode. How should I get an IQueryble? I have tried var items= db.items; then return View(items.ToPagedList(pageNumber, 25)); there is an error The method 'Skip' is only supported for sorted input in LINQ to Entities. The method 'OrderBy' must be called before the method 'Skip'. — Wilheim, Jan 10 '17 at 22:09
`var items` is fine, you just need to add an `OrderBy()` in your query - e.g. `var items = db.items.OrderBy(x => x.someProperty)` — , Jan 10 '17 at 22:11
@stephenmuecke It worked good! Now it's much faster than before! Thanks! — Wilheim, Jan 10 '17 at 22:23

Paul Abbott · Answer 1 · 2017-01-10T21:22:37.393

4

db.items.ToList(); is loading all 350,000 records into memory, and then you are filtering on that in code. You need to do the pagination database-side using .Skip() and .Take().

Edit: Apparently PagedList.MVC takes care of this, you just need to keep it as in IQueryable and not call .ToList(). From https://github.com/TroyGoode/PagedList:

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();
    }
}

edited Jan 10 '17 at 21:22

answered Jan 10 '17 at 21:19

Paul Abbott

7,065
3
27
45

`.ToPagedList()` is using `.Skip()` and `.Take()` :) – Jan 10 '17 at 21:22
You should be careful that you don't dispose of the DbContext before running ToPagedList() though. – juunas Jan 10 '17 at 21:28
So if i'm using entity framework in db first mode. How should I get an IQueryble? I have tried var items= db.items; then return View(items.ToPagedList(pageNumber, 25)); there is an error The method 'Skip' is only supported for sorted input in LINQ to Entities. The method 'OrderBy' must be called before the method 'Skip'. @Paul Abbott – Wilheim Jan 10 '17 at 22:06

How to solve performance issue when pagination in .net mvc?

1 Answers1