EF query for calculating monthly trends

Question

My application has a fairly important query that is used in a lot of places. Unfortunately it takes about 14 seconds to run, so I'd like to find a way of possibly improving it.

The application receives incidents (right now there's about 70,000 total incidents, and about 16,000 incidents in this query), with each incident having a range of different flags. The purpose of the query is to get a monthly trend of the incidents and their flags.

For example:

Month |  Flagged  |  Falls  |  FallsWithInjury  | .... 
-------------------------------------------------------
Jan        10          3            1 
Feb        15          6            0 
Mar        14          2            2 
....

which I can then graph client side.

In the following query, FacilityIds are a list of facilities I want to load incidents for (about 80 IDs in the array). The start and end filters are 6 months apart.

var query = from i in Db.Incidents.Where(x => FacilityIds.Contains(x.FacilityId))
            where i.IncidentDate >= start && i.IncidentDate <= end
            group i by new { i.IncidentDate.Year, i.IncidentDate.Month } into incidentsByMonth
            let date = incidentsByMonth.Key
            select new MonthlyStat
                        {
                            Year = date.Year,
                            Month = date.Month,
                            AllIncidents = incidentsByMonth.Count(),
                            Flagged = incidentsByMonth.Count(ibm => ibm.IsSerious),
                            SentToHospital = incidentsByMonth.Count(x => x.Treatments.Any(it => it.TreatmentTypeId == (int)TreatmentTypes.Senttohospital)),
                            FallsWithInjuries = incidentsByMonth.Count(ibm => ibm.IncidentTypes.Any(it => it.IncidentTypeId == (int)IncidentTypes.Fall) && ibm.InjuryTypes.Any(inj => inj.InjuryTypeId != (int)InjuryTypes.None)),
                            FallsWithFracture = incidentsByMonth.Count(ibm => ibm.IncidentTypes.Any(it => it.IncidentTypeId == (int)IncidentTypes.Fall) && ibm.InjuryTypes.Any(inj => inj.InjuryTypeId == (int)InjuryTypes.Fracture)),
                            IncidentTypes = incidentsByMonth.SelectMany(ibm => ibm.IncidentTypes).GroupBy(it => new { it.IncidentTypeId, it.Description }).Select(it => new IncidentProperty<IncidentTypes>
                            {
                                Key = (IncidentTypes)it.Key.IncidentTypeId,
                                Count = it.Count()
                            }),
                            InjuryTypes = incidentsByMonth.SelectMany(ibm => ibm.InjuryTypes).GroupBy(it => new { it.InjuryTypeId, it.Description }).Select(it => new IncidentProperty<InjuryTypes>
                            {
                                Key = (InjuryTypes)it.Key.InjuryTypeId,
                                Count = it.Count()
                            }),
                            DayOfWeek = incidentsByMonth.GroupBy(ibm => SqlFunctions.DatePart("weekday", ibm.IncidentDate)).Select(ibm => new Aggregate<DayOfWeek> { Key = (DayOfWeek)ibm.Key.Value, Count = ibm.Count() }),
                            IncidentsByHour = incidentsByMonth.GroupBy(ibm => ibm.IncidentDate.Hour).Select(h => new Aggregate<int> { Key = h.Key, Count = h.Count() }),
                            PotentialIncurredMedian = incidentsByMonth.Select(x => x.PotentialIncurredMedian * .4).DefaultIfEmpty(0).Sum(),
                        };

Profiling different components, it looks like the IncidentTypes and InjuryTypes properties account for about 6 seconds of the 14 seconds totals. I've tried rewriting it a few different ways, but all seem to be worse in terms of performance.

I've started writing the query in native T-SQL, but I think it's a bit complex for my abilities right now.

Any suggestions would be greatly appreciated.

Answer 1

Native T-SQL

I don't know enough about linq to do a proper code review for that, however as far as performance is concerned I think T-SQL would take care of the expensive part of your query more efficiently.

The following query will not get you all the information from your entire query, but it would take care of at least this section:

Year = date.Year,
Month = date.Month,
AllIncidents = incidentsByMonth.Count(),
Flagged = incidentsByMonth.Count(ibm => ibm.IsSerious),
SentToHospital = incidentsByMonth.Count(x => x.Treatments.Any(it => it.TreatmentTypeId == (int)TreatmentTypes.Senttohospital)),
FallsWithInjuries = incidentsByMonth.Count(ibm => ibm.IncidentTypes.Any(it => it.IncidentTypeId == (int)IncidentTypes.Fall) && ibm.InjuryTypes.Any(inj => inj.InjuryTypeId != (int)InjuryTypes.None)),
FallsWithFracture = incidentsByMonth.Count(ibm => ibm.IncidentTypes.Any(it => it.IncidentTypeId == (int)IncidentTypes.Fall) && ibm.InjuryTypes.Any(inj => inj.InjuryTypeId == (int)InjuryTypes.Fracture)),

Note that I made assumptions about your schema design and you may need to make some adjustments. Also please note that I used more descriptive table aliases as well to make it easier for Mr. Maintainer.

DECLARE @dateStart DATE = '2014-01-01'; -- or whichever date you want
DECLARE @dateEnd DATE = DATEADD(MONTH, 6, @dateStart); -- 6 months later e.g. July 1 2014
SELECT     
    DATEPART(yyyy, incd.date) AS Year,
    DATEPART(mm, incd.date) AS Month,
    COUNT(incd.IncidentId) AS AllIncidents,
    COUNT(incd.IsSerious) AS Flagged,
    (SELECT COUNT incd.IncidentId
        FROM incd
        WHERE treat.TreatmentType = 'SenttoHospital') AS SentToHospital,
    (SELECT COUNT incd.IncidentId
        FROM incd 
        WHERE incdType = 'Fall' 
        AND injuType.InjuryType <> 'None') AS FallsWithInjuries,
    (SELECT COUNT incd.IncidentId
        FROM incd 
        WHERE incdType = 'Fall' 
        AND injuType.InjuryType = 'Fracture') AS FallsWithFracture,
FROM Incidents AS incd
LEFT JOIN IncidentTypes AS incdType ON inc.IncidenTypeId = incdType.IncidentTypeId
LEFT JOIN InjuryTypes AS injuType ON inc.InjuryTypeId = injuType.InjuryTypeId
LEFT JOIN Treatments AS treat ON inc.TreatmentTypeId = treat.TreatmentTypeId
WHERE incd.date >= @dateStart
AND incd.date < @dateEnd;