1
\$\begingroup\$

This MVC4 controller method, which uses EF6, is taking a long time to run (over 2 seconds). I've added verbose comments for the purpose of this post only.

public async Task<ActionResult> Index()
{
  // Get a list of all the classes I teach.
  var myClasses = await CurrentUser.GetClassesAsync(db);

  // Get a list of all tests...
  var tests = await db.Tests
    // Which are either authored by me or involve one of my classes...
    .Where(o => o.AuthorId == CurrentUser.Id || (o.Author.SchoolId == CurrentUser.SchoolId && o.Classes.Any(c => myClasses.Contains(c.ClassName))))
    // And for each one I want...
    .Select(o => new TestOverviewItem
    {
      // The ID, title and test status
      Id = o.Id,
      Title = o.Title,
      Status = o.Status,
      // A list of which of my classes are registered for the test
      Classes = o.Classes.Where(c => myClasses.Contains(c.ClassName)).Select(c => c.ClassName).ToList(),
      // An indication of whether any results exist for students in my classes
      AnyUploads = o.Results.Any(r => r.User.Classes.Any(c => myClasses.Contains(c.ClassName))),
      // How many unmarked results there are for students in my classes
      RemainingToMark = o.Results.Count(r => r.MarkedBy == null && r.QuestionNumber > 0 && r.User.Classes.Any(c => myClasses.Contains(c.ClassName))),
      // An indication of whether I created the test
      IsOwner = o.AuthorId == CurrentUser.Id
    })
    .OrderByDescending(o => o.Id).ToListAsync();
  return View("Index", new OverviewModel(CurrentUser, tests, myClasses));
}

There are about 50 tests in total which match the criteria, and each one has ~4 Classes and ~2000 Results. I understand that all the subqueries must be slowing it down, but I'm not sure how to optimise.

\$\endgroup\$
  • \$\begingroup\$ Where is classes defined? Can you leave out the .ToList() where you populate Classes? \$\endgroup\$ – Jeroen Vannevel Apr 12 '15 at 12:37
  • \$\begingroup\$ It should have said myClasses. TestOverviewItem.Classes is defined as an IList which is why .ToList() is there. Could be changed if it would make a difference though. \$\endgroup\$ – James Apr 12 '15 at 12:40
  • 1
    \$\begingroup\$ Well it depends on how the code is used but typically a .ToList() call triggers execution of the query though I'm not sure if it will actually have an effect here. \$\endgroup\$ – Jeroen Vannevel Apr 12 '15 at 12:46
2
\$\begingroup\$

I bet the database does not have the indexes to process this combination of conditions, especially with a contains on a name column. You will get table scans. Break off the || (o.Author.SchoolId == CurrentUser.SchoolId... part to return a new list of AuthorIds and you can add the CurrentUser.Id so the db.Tests where condition is just using the primary key.

The idea here is to preprocess your contains to be lists of ints to make the indexes happy.

\$\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.