CODEWITHSUNDEEP
performancelinqasp.net-mvc-4
sypahx jugurtha
sypahx jugurtha

Reputation: 53

Linq performance issue in loop

I have an Asp.Mvc5 web site, we have somme performance issues, here is our model:

We have a survey which contains a list of sections, each section has a list of subSections, each subSections has a list of questions. A subsection can be present in several sections (we need another table to stock the order of subSection in a section).

A question has QuestionSubSection which determines the order of the question in the given subSection (a question can be present in several subSecton).

We have a list of companies, each company has a list of answers. Our web site display the list of answers for a given company in the order defined by the model above.

After analysis of the code I figure out that the following line needs 2 seconds to execute:

foreach(var section in Survey.ordredBy(s => s.Order)
 {
  foreach(var subSection in section.SebSections.ordredBy(s => s.Order)
  {
   foreach(var question in subSection .Questions.ordredBy(s => s.Order)
   {
    //this query cause a perfermance issues
    var answer = company.Answers.Where(c => c.QuestionId == questionId);
   }
  }
}

What do you suggest for me to solve this problem? Thanks in advance.

Upvotes: 1

Views: 44

Answers (1)

Eric J.
Eric J.

Reputation: 150108

You are running one query per question in the survey. If you have lots of questions, that may take a while.

Instead, query all of the answers you are going to need in one request to the database, e.g. something like

var allAnswers = company.Answers.Where(c => c.SurveyId == surveyId);

Hopefully the answers know (have a reference to) what question they are for, the questions know what subsection they are for, etc.

Upvotes: 1

Related Questions