Optimizing a slow LINQ query

Question

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?

Answer 1

1. Avoid using Contains. It degrades performance heavily. See this post

2. ToList() is a command to execute your query. Till you call ToList() method, linq query is not started as linq has a feature called deferred execution. So if you call ToList(), you start some real operations with Databaseof files.

3. reducing columns of table reduces bandwidth required(delete unnecessary columns from your query)

4. turn off change-tracking and identity-management (for example, ObjectTrackingEnabled in LINQ-to-SQL)

   using (YourDataContext dataContext = new YourDataContext())    
   {
       dataContext.ObjectTrackingEnabled = false;    
       //Your code
   }
   

5. Use one of the tuning options of EF such as .AsNoTracking(). The extensive description can be seen here.
6. use a pre-compiled query. It sometimes reduces pre-processing overheads