Caching a list in ASP.NET MVC 5

Question

I have a thousands of users in my MVC APP and I use sort, paging and searching in my table of users (by this tutorial), but it takes a too long to load, sort or search.

So I have list of users

List<ApplicationUser> appUsers = _context.Users.ToList();

and I have list of models

List<RegisterViewModel> models = new List<RegisterViewModel>();
foreach (var au in appUsers)
{
    RegisterViewModel rvm = new RegisterViewModel(au);
    rvm.RoleName = UserManager.GetRoles(au.Id).First();
    models.Add(rvm); //new RegisterViewModel(au));
}

And in models I search, or sort.

What is the simplest way to cache this list of models and how can I use cached list instead of creating new list?

Here is my complete code of ActionResult Index()

[OutputCache(Duration = 120, VaryByParam = "*")]
[AuthLog(Roles = "SuperAdmin")]
public ActionResult Index(string sortOrder, string currentFilter, string searchString, int? page)
{
    ViewBag.CurrentSort = sortOrder;
    ViewBag.NameSortParm = String.IsNullOrEmpty(sortOrder) ? "Name_desc" : "";
    ViewBag.EmailSortParm = sortOrder == "Email" ? "Email_desc" : "Email";

    if (searchString != null)
        page = 1;
    else
        searchString = currentFilter;

    ViewBag.CurrentFilter = searchString;

    List<ApplicationUser> appUsers = _context.Users.ToList();

    List<RegisterViewModel> models = new List<RegisterViewModel>();
    foreach (var au in appUsers)
    {
        RegisterViewModel rvm = new RegisterViewModel(au);
        rvm.RoleName = UserManager.GetRoles(au.Id).First();
        models.Add(rvm); //new RegisterViewModel(au));
    }

    if (!String.IsNullOrEmpty(searchString))
        models = models.Where(s => s.Name.ToUpper().Contains(searchString.ToUpper()) || s.Email.ToUpper().Contains(searchString.ToUpper())).ToList();

    switch (sortOrder)
    {
        case "Name_desc":
            models = models.OrderByDescending(x => x.Name).ToList();
            break;
        case "Email_desc":
            models = models.OrderByDescending(x => x.Email).ToList();
            break;
        case "Email":
            models = models.OrderBy(x => x.Email).ToList();
            break;
        default:
            models = models.OrderBy(x => x.Name).ToList();
            break;
    }
    int pageSize = 20;
    int pageNumber = (page ?? 1);

    ViewBag.Title = "Použivatelia";
    return View(models.AsEnumerable().ToPagedList(pageNumber, pageSize));
}

I would first investigate why it takes a long time to search and sort. Assuming you are using a database as the store you might be able to fix this with an index or 2, an indexed view, or something else. If the total user list is incredibly long OR change frequently an in memory store is probably not the best way to go. Even with millions of records in a data table, a fine grained query and proper indexes should return results relatively fast. Also, maybe its not even the database that's slow but something else you are loading. You will not be able to tell without some profiling. — Igor
– Igor, Commented Mar 16, 2016 at 12:19

Daniel van Heerden · Accepted Answer · 2016-03-16 12:22:21Z

4

I think you need to do your sorting and filtering in your database rather than memory.

Not sure what your -_context is but assume it's an ORM of some sort. Problem is you calling ToList before any sorting or filtering which means everything is done in memory by your code. If you use your ORM capabilities you can probably avoid the need for caching by simply allow it to generate more effective SQL queries.

answered Mar 16, 2016 at 12:22

Daniel van Heerden

8361 gold badge7 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

NightOwl888 Over a year ago

I agree. Note that the tutorial that the OP referenced does not call ToList inline as in the OP's code. Keeping a whole table in memory is probably not going to be faster than filtering the data before it is fetched.

Kicker Over a year ago

I dont have a problem only with sort or search in my list, but also with my initial load. Initial load takes about 70 seconds --> it is really bad.

Daniel van Heerden Over a year ago

All the more reason to make sure you filter the data you initially load instead of loading it all in one go

maguy · Accepted Answer · 2017-12-19 21:30:05Z

0

You need to be doing paging and sorting against the database and not pulling back thousands of rows and then doing paging or sorting against that rows.

When you call .ToList() it is signally to the database you want all the records back and then it pulls back the thousands in your table and only then are you applying your sorting and paging to these that have been brought back into memory.

Instead of the query only pulling back 10 sorted records based on the page size and page number you are pulling back say 4000 and then doing a sort/paging.

See article about PagedList and sorting

answered Dec 19, 2017 at 21:30

maguy

1,7191 gold badge18 silver badges27 bronze badges

Collectives™ on Stack Overflow

Caching a list in ASP.NET MVC 5

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related