Speeding up a linq entitiy query

Question

I currently have a query which not takes a long time and sometimes crashes because of the amount of data in the database.

Can someone notice anything i can do to help speed it up?

public IList<Report> GetReport(CmsEntities context, long manufacturerId, long? regionId, long? vehicleTypeId)
        {
            var now = DateTime.Now;
            var today = new DateTime(now.Year, now.Month, 1);
            var date1monthago = today.AddMonths(-1);
            var date2monthago = today.AddMonths(-2);
            var date3monthago = today.AddMonths(-3);
            var date4monthago = today.AddMonths(-4);
            var date5monthago = today.AddMonths(-5);
            var date6monthago = today.AddMonths(-6);
            today = TimeManager.EndOfDay(new DateTime(now.AddMonths(-1).Year, today.AddMonths(-1).Month, DateTime.DaysInMonth(now.Year, today.AddMonths(-1).Month)));             
            var query = from item in context.Invoices
                         where item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Select(x => x.ManufacturerId).Contains(manufacturerId)
                         && (item.InvoiceDate >= date6monthago && item.InvoiceDate <= today)
                         && (regionId.HasValue && regionId.Value > 0 ? item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Select(x => x.RegionId).Contains(regionId.Value) : true)
                         && (item.InvType == "I" || item.InvType == null)
                         && (vehicleTypeId.HasValue && vehicleTypeId.Value > 0 ? item.Repair.Job.Vehicle.Model.VehicleTypes.Select(x => x.Id).Contains(vehicleTypeId.Value) : true)
                         select item;

            var query2 = from item in query
                         group item by new { item.Repair.Job.Bodyshop } into g
                         let manufJobs = query.Where(x => x.Repair.Job.Vehicle.Model.ManufacturerId == manufacturerId && x.Repair.Job.BodyshopId == g.Key.Bodyshop.Id)
                         let allJobs = query.Where(x => x.Repair.Job.BodyshopId == g.Key.Bodyshop.Id)
                         select new tReport
                         {                                     
    MonthSixManufJobTotal = manufJobs.Where(x => x.InvoiceDate.Month == date6monthago.Month && x.InvoiceDate.Year == date6monthago.Year).GroupBy(x => x.Repair.Job).Count(),
    MonthSixJobTotal = allJobs.Where(x => x.InvoiceDate.Month == date6monthago.Month && x.InvoiceDate.Year == date6monthago.Year).GroupBy(x => x.Repair.Job).Count(),

    MonthFiveManufJobTotal = manufJobs.Where(x => x.InvoiceDate.Month == date5monthago.Month && x.InvoiceDate.Year == date5monthago.Year).GroupBy(x => x.Repair.Job).Count(),
    MonthFiveJobTotal = allJobs.Where(x => x.InvoiceDate.Month == date5monthago.Month && x.InvoiceDate.Year == date5monthago.Year).GroupBy(x => x.Repair.Job).Count(),

    MonthFourManufJobTotal = manufJobs.Where(x => x.InvoiceDate.Month == date4monthago.Month && x.InvoiceDate.Year == date4monthago.Year).GroupBy(x => x.Repair.Job).Count(),
    MonthFourJobTotal = allJobs.Where(x => x.InvoiceDate.Month == date4monthago.Month && x.InvoiceDate.Year == date4monthago.Year).GroupBy(x => x.Repair.Job).Count(),

    MonthThreeManufJobTotal = manufJobs.Where(x => x.InvoiceDate.Month == date3monthago.Month && x.InvoiceDate.Year == date3monthago.Year).GroupBy(x => x.Repair.Job).Count(),
    MonthThreeJobTotal = allJobs.Where(x => x.InvoiceDate.Month == date3monthago.Month && x.InvoiceDate.Year == date3monthago.Year).GroupBy(x => x.Repair.Job).Count(),

    MonthTwoManufJobTotal = manufJobs.Where(x => x.InvoiceDate.Month == date2monthago.Month && x.InvoiceDate.Year == date2monthago.Year).GroupBy(x => x.Repair.Job).Count(),
    MonthTwoJobTotal = allJobs.Where(x => x.InvoiceDate.Month == date2monthago.Month && x.InvoiceDate.Year == date2monthago.Year).GroupBy(x => x.Repair.Job).Count(),

    MonthOneManufJobTotal = manufJobs.Where(x => x.InvoiceDate.Month == date1monthago.Month && x.InvoiceDate.Year == date1monthago.Year).GroupBy(x => x.Repair.Job).Count(),
    MonthOneJobTotal = allJobs.Where(x => x.InvoiceDate.Month == date1monthago.Month && x.InvoiceDate.Year == date1monthago.Year).GroupBy(x => x.Repair.Job).Count(),

    ManufTotal = manufJobs.GroupBy(x => x.Repair.Job).Count(),
    Total = allJobs.GroupBy(x => x.Repair.Job).Count(),

    PercentageOf = ((decimal)manufJobs.GroupBy(x => x.Repair.Job).Count() / (decimal)allJobs.GroupBy(x => x.Repair.Job).Count()) * 100
                         };

            return query2.OrderBy(x => x).ToList();
        }

EDIT

var query = from item in context.Invoices.AsNoTracking()
                    where item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(x => x.ManufacturerId == manufacturerId)
                    && (item.InvoiceDate >= date12monthago && item.InvoiceDate <= today)
                    && (item.InvType == "I" || item.InvType == null)
                    select item;

        if (regionId.HasValue && regionId.Value > 0)
        {
            query = query.Where(item => item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Select(x => x.RegionId).Contains(regionId.Value));
        }

        if (vehicleTypeId.HasValue && vehicleTypeId.Value > 0)
        {
            query = query.Where(item => item.Repair.Job.Vehicle.Model.VehicleTypes.Select(x => x.Id).Contains(vehicleTypeId.Value));
        }


              var query2 = from item in hey
                     group item by new { item.Repair.Job.Bodyshop, item.InvoiceDate.Month } into m
                     select new TReport
                     {
                         Bodyshop = m.Key.Bodyshop.Name,
                         Bays = m.Key.Bodyshop.Bays,
                         Region = m.Key.Bodyshop.Manufacturer2Bodyshop.FirstOrDefault(x => x.ManufacturerId == manufacturerId).Region.Name,
                         BodyshopCode = m.Key.Bodyshop.Manufacturer2Bodyshop.FirstOrDefault(x => x.ManufacturerId == manufacturerId).BodyshopCode,
                         Total = m.Count(),
                         ManufTotal = m.Where(x => x.Repair.Job.Vehicle.Model.ManufacturerId == manufacturerId).Count(),
                         Totals = m.GroupBy(j => j.InvoiceDate.Month).Select(j => new TPercentReportInner
                         {
                             Month = j.Key,
                             ManufTotal = j.Where(x => x.Repair.Job.Vehicle.Model.ManufacturerId == manufacturerId).Count(),
                             AllTotal = j.Count()
                         })
                     };

Ive cut the query down. But even this is performing now worse than before?

Answer 1

I would start by removing the hardcoded optional conditionals from your query, which will allow the query optimizer to use different query plans based on the parameters you have, like:

var query = from item in context.Invoices.AsNoTracking()
            where item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Select(x => x.ManufacturerId).Contains(manufacturerId)
            && (item.InvoiceDate >= date12monthago && item.InvoiceDate <= today)
            && (item.InvType == "I" || item.InvType == null)
            select item;

if (regionId.HasValue && regionId.Value > 0)
    query=query.Where(item=>item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Select(x => x.RegionId).Contains(regionId.Value));

if (vehicleTypeId.HasValue && vehicleTypeId.Value > 0)
    query=query.Where(item=>item.Repair.Job.Vehicle.Model.VehicleTypes.Select(x => x.Id).Contains(vehicleTypeId.Value));

var query2 = from item in query
             group item by new { item.InvoiceDate.Month, item.Repair.Job.Bodyshop } into g
             select new TReport
             {
                 BodyshopId = g.Key.Bodyshop.Id,  
                 Month = g.Key.Month,
                 MonthAllJobTotal = g.Count()
             };

return query2.ToList();

You could also check to see if converting .Select(x=>x.id).Contains(id) or .Any(x=>x.Id==id) performs faster although I would think they would be similar in query plan and execution speed. That would give you:

var query = from item in context.Invoices.AsNoTracking()
            where item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(m=>m.ManufacturerId==manufacturerId)
            && (item.InvoiceDate >= date12monthago && item.InvoiceDate <= today)
            && (item.InvType == "I" || item.InvType == null)
            select item;

if (regionId.HasValue && regionId.Value > 0)
    query=query.Where(item=>item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(m=>m.RegionId==regionId.Value));

if (vehicleTypeId.HasValue && vehicleTypeId.Value > 0)
    query=query.Where(item=>item.Repair.Job.Vehicle.Model.VehicleTypes.Any(v=>v.Id==vehicleTypeId.Value));

var query2 = from item in query
             group item by new { item.InvoiceDate.Month, item.Repair.Job.Bodyshop } into g
             select new TReport
             {
                 BodyshopId = g.Key.Bodyshop.Id,  
                 Month = g.Key.Month,
                 MonthAllJobTotal = g.Count()
             };

return query2.ToList();

Based on what you have, I would guess the .AsNoTracking() is doing very little for you, but it couldn't hurt. It has a bigger effect when retrieving large numbers of entities which this doesn't appear to be doing.

I would then clean up and standardize your query by removing the hardcoded ManufacturerId as well, which would give you:

var query = from item in context.Invoices.AsNoTracking()
            where (item.InvoiceDate >= date12monthago && item.InvoiceDate <= today)
            && (item.InvType == "I" || item.InvType == null)
            select item;

if (manufacturerId.HasValue && manufacturerId.Value > 0)
    query=query.Where(item=>item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(m=>m.ManufacturerId==manufacturerId));

if (regionId.HasValue && regionId.Value > 0)
    query=query.Where(item=>item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(m=>m.RegionId==regionId.Value));

if (vehicleTypeId.HasValue && vehicleTypeId.Value > 0)
    query=query.Where(item=>item.Repair.Job.Vehicle.Model.VehicleTypes.Any(v=>v.Id==vehicleTypeId.Value));

var query2 = from item in query
             group item by new { item.InvoiceDate.Month, item.Repair.Job.Bodyshop } into g
             select new TReport
             {
                 BodyshopId = g.Key.Bodyshop.Id,  
                 Month = g.Key.Month,
                 MonthAllJobTotal = g.Count()
             };

return query2.ToList();

and then lastly, I would return an IQueryable instead of List so that if you don't need one or more columns they can be dropped from the final query as well like:

public IQueryable<Report> GetReport(CmsEntities context, long? manufacturerId, long? regionId, long? vehicleTypeId)
    {
{
        var now = DateTime.Now;
        var today = new DateTime(now.Year, now.Month, 1);
        var date1monthago = today.AddMonths(-1);
        var date2monthago = today.AddMonths(-2);
        var date3monthago = today.AddMonths(-3);
        var date4monthago = today.AddMonths(-4);
        var date5monthago = today.AddMonths(-5);
        var date6monthago = today.AddMonths(-6);
        today = TimeManager.EndOfDay(new DateTime(now.AddMonths(-1).Year, today.AddMonths(-1).Month, DateTime.DaysInMonth(now.Year, today.AddMonths(-1).Month)));             

    var query = from item in context.Invoices.AsNoTracking()
                where (item.InvoiceDate >= date12monthago && item.InvoiceDate <= today)
                && (item.InvType == "I" || item.InvType == null)
                select item;

    if (manufacturerId.HasValue && manufacturerId.Value > 0)
        query=query.Where(item=>item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(m=>m.ManufacturerId==manufacturerId));

    if (regionId.HasValue && regionId.Value > 0)
        query=query.Where(item=>item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(m=>m.RegionId==regionId.Value));

    if (vehicleTypeId.HasValue && vehicleTypeId.Value > 0)
        query=query.Where(item=>item.Repair.Job.Vehicle.Model.VehicleTypes.Any(v=>v.Id==vehicleTypeId.Value));

    var query2 = from item in query
                 group item by new { item.InvoiceDate.Month, item.Repair.Job.Bodyshop } into g
                 select new TReport
                 {
                     BodyshopId = g.Key.Bodyshop.Id,  
                     Month = g.Key.Month,
                     MonthAllJobTotal = g.Count()
                 };

    return query2;
}

Then I would break these apart and convert these to extension methods:

public static class MyExtensions 
{
    public static IQueryable<Invoice> Recent(this IQueryable<Invoice> context,long? manufacturerId=null,long? regionId=null,long? vehicleId=null)
    {
        var now = DateTime.Now;
        var today = new DateTime(now.Year, now.Month, 1);
        var date1monthago = today.AddMonths(-1);
        var date2monthago = today.AddMonths(-2);
        var date3monthago = today.AddMonths(-3);
        var date4monthago = today.AddMonths(-4);
        var date5monthago = today.AddMonths(-5);
        var date6monthago = today.AddMonths(-6);
        today = TimeManager.EndOfDay(new DateTime(now.AddMonths(-1).Year, today.AddMonths(-1).Month, DateTime.DaysInMonth(now.Year, today.AddMonths(-1).Month)));             

        var query = from item in context.Invoices.AsNoTracking()
                where (item.InvoiceDate >= date12monthago && item.InvoiceDate <= today)
                && (item.InvType == "I" || item.InvType == null)
                select item;

        if (manufacturerId.HasValue && manufacturerId.Value > 0)
            query=query.Where(item=>item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(m=>m.ManufacturerId==manufacturerId));

        if (regionId.HasValue && regionId.Value > 0)
            query=query.Where(item=>item.Repair.Job.Bodyshop.Manufacturer2Bodyshop.Any(m=>m.RegionId==regionId.Value));

        if (vehicleTypeId.HasValue && vehicleTypeId.Value > 0)
            query=query.Where(item=>item.Repair.Job.Vehicle.Model.VehicleTypes.Any(v=>v.Id==vehicleTypeId.Value));
    return query;
}
public static IQueryable<Report> ToReport(this IQueryable<Invoice> context)
{
    return (from item in query
                 group item by new { item.InvoiceDate.Month, item.Repair.Job.Bodyshop } into g
                 select new TReport
                 {
                     BodyshopId = g.Key.Bodyshop.Id,  
                     Month = g.Key.Month,
                     MonthAllJobTotal = g.Count()
                 });

}
}

Now you can do the following:

var reports=db.Invoices.Recent.ToReport(); 

or

var reports=db.Invoices.Recent(ManufacturerEnum.Toyota).ToReport();

Answer 2

You could implementen paging to avoid materialize all results. I mean, you could implement Skip and Take linq methods.

Simple example based on your code:

public IList<Report> GetReport(CmsEntities context, long manufacturerId, long? regionId, long? vehicleTypeId, int pageSize, int currentPage)
        {

        //Code removed to simplify

        return  query2.Skip(pageSize * currentPage).Take(pageSize );

        }