PagedList Search suffering from inefficiency

Question

I'm trying to achieve Search functionality on more than 10 thousand records. This is suffering from speed issues while using PagedList.

public ActionResult CrmBlogGroupType(int? page, bool? Name, bool? AuthorTitle, bool?    Description, string search, int? PageSize, string type)
{
    try
    {
    if (type==null)
    {
        type = "A";
    }
    IEnumerable<Usp_getBlogSetPosts_Result> _objBlogSet = _dataLayer.GetBlogSet(type);
    //The above _objBlogSet has around 10 thousand records

    ViewBag.CurrentPage = page;

    ViewBag.Name = Name ==null?false:Name;
    ViewBag.AuthorTitle = AuthorTitle == null ? false : AuthorTitle;
    ViewBag.Description = Description == null ? false : Description;

    ViewBag.Search = search;
    ViewBag.type = type;

    if (Name == true && AuthorTitle == false && Description == false)
    {
        _objBlogSet = _objBlogSet.Where(p => p.author_name.ToLower().Contains(search.ToLower())).ToPagedList(page ?? 1, PageSize ?? 10);
    } 

    return View(_objBlogSet);

    catch (Exception ex)
    {
        throw ex;
    }
}

Answer 1

I'm assuming you are using Troy Goode's Paged List (https://github.com/TroyGoode/PagedList)

Instead of working with IEnumerable, try to work with IQueryable. That way, pagination is done on the server side and performance will be better.

Answer 2

It all depends on what happens in _dataLayer.GetBlogSet(). Your current code most likely pulls in the entire table and filters on that data in-memory.

That method should return an IQueryable<Usp_getBlogSetPosts_Result>, so the ToPagedList()'s internal Skip() and Take() on that collection will be translated to SQL queries.