CODEWITHSUNDEEP
c#asp.netlinqentity-framework
Gordon Copestake
Gordon Copestake

Reputation: 1647

Speeding up linq group sum queries

I have a query that processes about 500 records pulled from various tables, grouped and then summaried (if that's a word) into a working report. Everything works fine but it takes about 30 seconds to run this one report and i'm getting complaints from my users.

The procedure in question is this one:

    public static List<LabourEfficiencies> GetLabourEfficienciesByTimeSheet(DateTime dateFrom, DateTime dateTo)
    {
        CS3Entities ctx = new CS3Entities();

        //get all relevant timesheetline items
        var tsItems = from ti in ctx.TimeSheetItems
                      where ti.TimeSheetHeader.Date >= dateFrom && ti.TimeSheetHeader.Date <= dateTo && ti.TimeSheetHeader.TimeSheetCategory != "NON-PROD"
                      select new TimesheetLine
                      {
                          TimesheetNo = ti.TimeSheetNo,
                          HoursProduced = ti.HoursProduced,
                          HoursProducedNet = ti.HoursProducedNet,
                          ItemID = ti.ItemID,
                          ProcessID = ti.ProcessID,
                          ProcessDuration = ti.ProcessDuration,
                          DowntimeHours = 0M
                      };

        //get all relevant downtimeline items
        var tsDownT = from dt in ctx.DowntimeItems
                      where dt.TimeSheetHeader.Date >= dateFrom && dt.TimeSheetHeader.Date <= dateTo && dt.TimeSheetHeader.TimeSheetCategory != "NON-PROD"
                      select new TimesheetLine
                      {
                          TimesheetNo = dt.TimeSheetNo,
                          HoursProduced = 0M,
                          HoursProducedNet = 0M,
                          ItemID = "",
                          ProcessID = "",
                          ProcessDuration = 0M,
                          DowntimeHours = dt.DowntimeHours
                      };

        //combine them into single table
        var tsCombi = tsItems.Concat(tsDownT);

        var flatQuery = (from c in tsCombi
                        join th in ctx.TimeSheetHeaders on c.TimesheetNo equals th.TimeSheetNo
                        select new
                                   {
                                       th.TimeSheetNo,
                                       th.EmployeeNo,
                                       th.TimeSheetCategory,
                                       th.Date,
                                       c.HoursProduced,
                                       c.ProcessDuration,
                                       th.HoursWorked,
                                       c.HoursProducedNet,
                                       c.DowntimeHours,
                                       c.ItemID
                                       });

        //add employee details & group by timesheet no (1 line per timesheet no)
        //NB. FnTlHrs checks whether there are any indirect hrs & deducts them if there are
        var query =  flatQuery.GroupBy(f => f.TimeSheetNo).Select(g => new LabourEfficiencies
                                                                            {
                                                                                Eno = g.FirstOrDefault().EmployeeNo,
                                                                                Dept =g.FirstOrDefault().TimeSheetCategory,
                                                                                Date = g.FirstOrDefault().Date,
                                                                                FnGrHrs =g.Where(w =>w.TimeSheetCategory == "FN" &&!w.ItemID.StartsWith("090")).Sum(h => h.HoursProduced),
                                                                                FnTlHrs =g.Where(w =>w.ItemID.StartsWith("090")).Sum(h => h.ProcessDuration) >0? (g.FirstOrDefault(w =>w.TimeSheetCategory =="FN").HoursWorked) -(g.Where(w =>w.ItemID.StartsWith("090")).Sum(h =>h.ProcessDuration)): g.FirstOrDefault(w =>w.TimeSheetCategory =="FN").HoursWorked,
                                                                                RmGrHrs =g.Where(w =>w.TimeSheetCategory == "RM").Sum(h => h.HoursProduced),RmGrHrsNet =g.Where(w =>w.TimeSheetCategory == "RM").Sum(h => h.HoursProducedNet),
                                                                                RmTlHrs =g.FirstOrDefault(w =>w.TimeSheetCategory == "RM").HoursWorked,
                                                                                MpGrHrs =g.Where(w =>w.TimeSheetCategory =="MATPREP").Sum(h => h.HoursProduced),
                                                                                MpTlHrs =g.FirstOrDefault(w =>w.TimeSheetCategory =="MATPREP").HoursWorked,
                                                                                DtHrs = g.Sum(s => s.DowntimeHours),
                                                                                Indirect =g.Where(w =>w.ItemID.StartsWith("090")).Sum(h => h.ProcessDuration)
                                                                            });

        return query.ToList();
    }

The first few bits just gather the data, it's the last query that is the "meat" of the procedure and takes the time.

I'm fairly sure I've done something horrid as the SQL it spits out is terrible, but for the life of me i can't see how to improve it.

Any hints greatly appreciated.

Gordon

Upvotes: 0

Views: 139

Answers (1)

LINQ2Vodka
LINQ2Vodka

Reputation: 3036

Your expression gets optimized both in IQueriable compilation and SQL server query optimization and even here takes that long. It's highly probable that you have no column indexes needed for execution plan to be faster. Copy/paste your rendered SQL expression to SSMS, run it and see the actual plan. Optimize database structire if needed (put indexes). Otherwise, you got that really large amont of data that makes process slow.

Upvotes: 1

Related Questions