CODEWITHSUNDEEP
asp.net-mvc-3entity-framework-4
Arno 2501
Arno 2501

Reputation: 9407

Search as you type lag caused by slow DB in asp.net MVC with entity framework

I'm implementing search as you type functionality for a website with mvc.

My controller accept a Searchstring parameter and returns the records accordingly.

What I do is calling my controller into a div using this javascript : 

$("#SearchField").keyup(function (event) {
    $.ajax({
        cache: false,
        url: 'FolderList?searchString=' + $("#SearchField").val(),
        success: function (data) {
            $('#FolderList').empty().html(data);
        }
    });
});

Here is my controller code : 

public ActionResult Search(string searchString, int? page)
        {

            var folders = db.Folders.AsQueryable();

            if (!String.IsNullOrEmpty(searchString))
            {
                folders = folders.Where(p => p.SearchField.ToLower().Contains(searchString.ToLower()));
            }

            int pageSize = Convert.ToInt32(System.Configuration.ConfigurationManager.AppSettings["FolderPageSize"]);
            int pageNumber = (page ?? 1);

            return View(folders.ToPagedList(pageNumber, pageSize));
        }

The problem is that the database entity that I request inside my controller (Folders) load very slowly (database issue). It makes the search as you type functionality lag a lot and it's not usable at all. That's an issue that i cannot solve at the source I must deal with it.

So my question: Is there a way to load the folder database entity in memory so there would be a long time to load the page but after every call to the controller would be quick. Or Am I wrong in my way to achieve this ? Is there another way ?

Upvotes: 3

Views: 270

Answers (1)

Eric J.
Eric J.

Reputation: 150138

If folders does not contain a huge amount of data, it is entirely reasonable to load all of the rows at application startup and store them in Cache, e.g.

Cache["folders"] = (from f in folders select f).ToList();

Then, replace 

folders = folders.Where(p => p.SearchField.ToLower().Contains(searchString.ToLower()));

with 

folders = Cache["folders"].Where(p => p.SearchField.ToLower().Contains(searchString.ToLower()));

Keep in mind that the cache will empty if the application domain recycles. If it takes a very long time to read all necessary rows to fill the cache, you may want to plan on pre-warming the cache on app domain startup / recycle.

Upvotes: 3

Related Questions