How to avoid DB call during pagination

Question

I am getting around 200K rows from database. Everytime I click on pagination it getting records from DB. How to avoid that? Caching??

public ActionResult Demo(int? page, string sortOrder, string recordSize)
{

    int pageSize = Convert.ToInt32(recordSize = string.IsNullOrEmpty(recordSize) ? "20" : recordSize);
    int pageNumber = (page ?? 1);

    var customers = DBHelper.GetCustomers();
    return View(customers.ToPagedList(pageNumber, pageSize));
}

I have edited your title. Please do not include information about a language used in a question title unless it wouldn't make sense without it. Tags serve this purpose. Also see, ["Should questions include “tags” in their titles?"](http://meta.stackoverflow.com/q/19190/193440), where the consensus is "no, they should not — chridam, Jun 17 '14 at 13:31
It seems everytime you get all customers and then do pagination. That's incorrect way of doing it. You should pass the paging info (page size, current page, etc.) to backend and return only the data you need to display — AD.Net, Jun 17 '14 at 13:33
You shouldn't read everything each time but we can't guess. What DBHelper.GetCustomers() is? How it's implemented? How ToPagedList() is implemented? Code you show doesn't include where problem may be — Adriano Repetti, Jun 17 '14 at 13:33
possible duplicate of [How do I do pagination in ASP.NET MVC?](http://stackoverflow.com/questions/446196/how-do-i-do-pagination-in-asp-net-mvc) — Tetsujin no Oni, Jun 17 '14 at 13:34
`startIndex` and `count` are preferred over `pageSize` and `pageNumber`. (as seen in Linq methods `.Take()` and `.Count()`) — BanksySan, Jun 17 '14 at 13:36
Does the Back End database support paging? If not it will send all data every time and your Front End code will discard what it doesn't need. — Bit, Jun 17 '14 at 13:36

score 4 · Answer 1 · answered Jun 17 '14 at 13:51

You should never pull back all rows to cache or use. That is simply too much data and you are going to take massive performance hits in doing so. You should try to only ever pull back the data you actually want to use.

If you are using LinqToSql or EntityFrameWork or similar data access, you can use linq to retrieve the page you want. This would be the most efficient solution because you will not pull back all 200k rows.

var customers = Customers.Skip(pageIndex * pageSize).Take(pageSize);

Caching could improve retrieval speeds for the second time around, but your initial speed problem is going to be with pulling back all 200k rows from the database with each request. Yes, you could pull them all back, cache them, and use them, but this is not an ideal approach. Ideal caching would be to cache each page with a dependency on the previous page so that you can properly invalidate your cache.

http://stackoverflow.com/questions/82644/can-you-use-microsoft-entity-framework-with-oracle — Josh, Jun 17 '14 at 14:54

How to avoid DB call during pagination

1 Answers1