C# Entity Framework + Linq - how do you speed up a slow query?

Question

This is a question related to C# MVC2 Jqgrid - what is the correct way to do server side paging? where I asked and found how to improve the performance for a query on a table with 2000 or so rows. The performance was improved from 10 seconds to 1 second.

Now I'm trying to perform the exact same query where the table has 20,000 rows - and the query takes 30 seconds. How can I improve it further? 20 thousand rows is still not a huge number at all.

Some possible ideas I have are:

• It can be improved by de-normalizing to remove joins and sums
• Make a view and query that instead of querying and joining the table
• Don't query the whole table, make the user select some filter first (eg an A | B | C .. etc filter)
• Add more indexes to the tables
• Something else?

This is the MVC action that takes 30 seconds for 20 thousand rows: (the parameters are supplied by jqgrid, where sidx = which sort column, and sord = the sort order)

public ActionResult GetProductList(int page, int rows, string sidx, string sord, 
string searchOper, string searchField, string searchString)
{
    if (sidx == "Id") { sidx = "ProductCode"; }
    var pagedData = _productService.GetPaged(sidx, sord, page, rows);
    var model = (from p in pagedData.Page<Product>()
            select new
            {
                p.Id, p.ProductCode, p.ProductDescription,
                Barcode = p.Barcode ?? string.Empty, 
                UnitOfMeasure = p.UnitOfMeasure != null ? p.UnitOfMeasure.Name : "",
                p.PackSize, 
                AllocatedQty = p.WarehouseProducts.Sum(wp => wp.AllocatedQuantity),
                QtyOnHand = p.WarehouseProducts.Sum(wp => wp.OnHandQuantity)
            });

    var jsonData = new
    {
        Total = pagedData.TotalPages, Page = pagedData.PageNumber,
        Records = pagedData.RecordCount, Rows = model
    };

    return Json(jsonData, JsonRequestBehavior.AllowGet);
}

ProductService.GetPaged() calls ProductRepository.GetPaged which calls genericRepository.GetPaged() which does this:

public ListPage GetPaged(string sidx, string sord, int page, int rows)
{
    var list = GetQuery().OrderBy(sidx + " " + sord);
    int totalRecords = list.Count();

    var listPage = new ListPage
    {
        TotalPages = (totalRecords + rows - 1) / rows,
        PageNumber = page,
        RecordCount = totalRecords,
    };

    listPage.SetPageData(list
        .Skip((page > 0 ? page - 1 : 0) * rows)
        .Take(rows).AsQueryable());

    return listPage;
}

The .OrderBy() clause uses LinqExtensions so that I can pass in a string instead of a predicate - could this be slowing it down?

And finally ListPage is just a class for conveniently wrapping up the properties that jqgrid needs for paging:

public class ListPage
{
    private IQueryable _data;
    public int TotalPages { get; set; }
    public int PageNumber { get; set; }
    public int RecordCount { get; set; }

    public void SetPageData<T>(IQueryable<T> data) 
    {
        _data = data;
    }

    public IQueryable<T> Page<T>()
    {
        return (IQueryable<T>)_data;
    }
}

GetQuery is:

public IQueryable<T> GetQuery()
{
    return ObjectSet.AsQueryable();
}

The custom .OrderBy method consists of these two methods:

public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, 
    string ordering, params object[] values)
{
    return (IQueryable<T>)OrderBy((IQueryable)source, ordering, values);
}

public static IQueryable OrderBy(this IQueryable source, string ordering, 
    params object[] values)
{
    if (source == null) throw new ArgumentNullException("source");
    if (ordering == null) throw new ArgumentNullException("ordering");
    ParameterExpression[] parameters = new ParameterExpression[] {
        Expression.Parameter(source.ElementType, "") };
    ExpressionParser parser = new ExpressionParser(parameters, ordering, values);
    IEnumerable<DynamicOrdering> orderings = parser.ParseOrdering();
    Expression queryExpr = source.Expression;
    string methodAsc = "OrderBy";
    string methodDesc = "OrderByDescending";
    foreach (DynamicOrdering o in orderings)
    {
        queryExpr = Expression.Call(
            typeof(Queryable), o.Ascending ? methodAsc : methodDesc,
            new Type[] { source.ElementType, o.Selector.Type },
            queryExpr, Expression.Quote(Expression.Lambda(o.Selector, parameters)));
        methodAsc = "ThenBy";
        methodDesc = "ThenByDescending";
    }
    return source.Provider.CreateQuery(queryExpr);
}

Answer 1

The bit that concerns me is:

.Take(rows).AsQueryable()

the fact that you needed to add AsQueryable() suggests to me that it is currently IEnumerable<T>, meaning you could be doing the paging at the wrong end of the query (bringing back way too much data over the network). Without GetQuery() and the custom OrderBy() it is hard to be sure - but as always, the first thing to do is to profile the query via a trace. See what query is executed and what data is returned. EFProf might make this easy, but a SQL trace is probably sufficient.