4
\$\begingroup\$

I have a LINQ query that I am having trouble optimizing and takes about 5.5 seconds to run. I am using a view called StaffingResourceData and a table called StaffingForecasts.

Each StaffingResource has a ResourceId, a Division, and a Type. A StaffingForecast has a ResourceId, Project, Date (represents a Monday of a week), Hours. A StaffingResource can have 0-many StaffingForecasts.

For each StaffingResource, I need a list of their total forecasted hours for the next 12 weeks. Here is what I have right now:

// Get list of dates
var dates = new List<DateTime>();
var start = Utilities.GetStartOfWeek(DateTime.Today);
for (var i = 0; i < 12; i++)
{
    dates.Add(start.AddDays(i * 7));
}
var end = dates[11];

// Get resources
var resources = (from r in context.StaffingResourceDatas
                 where r.EmployeeId != null
                     && !exclusionList.Contains(r.ResourceTitleId)
                 join f in context.StaffingForecasts.Where(x => x.Date >= start && x.Date <= end) on r.ResourceId equals f.ResourceId into g1
                 from f in g1.DefaultIfEmpty()
                 group new { f.Date, f.Hours } by r into g2
                 select new ChartResourceModel
                 {
                     ResourceId = g2.Key.ResourceId,
                     Division = g2.Key.ResourceDivision,
                     Type = g2.Key.ResourceType,
                     Dates = dates.Select(d => new ChartDateModel
                     {
                         Date = d,
                         Available = (g2.Where(f => f.Date == d).Any() ? g2.Where(f => f.Date == d).Sum(f => f.Hours) : 0) < 24
                     }).ToList()
                 })
               .ToList();

Any ideas on how I could speed this up?

\$\endgroup\$
  • 5
    \$\begingroup\$ Do you have appropriate indexes in place on your database tables? \$\endgroup\$ – 200_success Nov 3 '15 at 11:52
  • 1
    \$\begingroup\$ What happens if you remove the inner ToList call? Also, if you can and if it's appropriate, try creating a view in the database that does the various joins and filter data on that view. My thought is that somewhere in the query you're dealing with an IEnumerable instead of an IQueryable (see here for the difference between the two). \$\endgroup\$ – Gentian Kasa Nov 3 '15 at 12:05
  • 1
    \$\begingroup\$ Regarding the inner ToList call, I guess Dates is a List in the ChartResourceModel. Can you change the type to IEnumerable? \$\endgroup\$ – Gentian Kasa Nov 3 '15 at 12:15
  • 1
    \$\begingroup\$ What ORM are you using? Entity Framework? How does the generated SQL look like? \$\endgroup\$ – svick Nov 3 '15 at 19:04
  • \$\begingroup\$ If you have the option, write the query as is in SQL and obtain the execution plan to look for any missing indexes. This query should not take long to execute at all unless you are trawling a large number StaffingForecast rows, in which case there is probably a missing index. \$\endgroup\$ – Anthony Fawcett Nov 27 '15 at 0:13
2
\$\begingroup\$

You are combining an IQueryable with a local sequence, dates. It is possible to do that, but Entity Framework1 needs a lot of code to convert the sequence into something that behaves like a SQL table. I'm sure this is a major performance killer.

From what I see, you can do without this local sequence. You know the begin and end date. So it's possible to get the forecasts between those dates. If you group those forecasts by their Date, you achieve the same grouping:

from r in context.StaffingResourceDatas 
where r.EmployeeId != null && !exclusionList.Contains(r.ResourceTitleId)
let forecasts = context.StaffingForecasts
                       .Where(f => r.ResourceId == f.ResourceId
                                && f.Date >= start && f.Date <= end)
select new ChartResourceModel
{
    ResourceId = r.Key.ResourceId,
    Division = r.Key.ResourceDivision,
    Type = r.Key.ResourceType,
    Dates = (from fc in forecasts
             group fc by d.Date into fcg
             select new ChartDateModel
             {
                 Date = fcg.Key,
                 Available = fcg.Sum(f => f.Hours) < 24
             })
}

1 I assume this is EF, because Linq-to-Sql doesn't allow local sequences in a LINQ query.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.