Linq查询性能问题求助：子查询导致加载缓慢

问题背景 #

编写LINQ查询从数据库获取数据时，添加指定子查询后耗时剧增，查询会先拉取全量数据到内存，再在内存中执行子查询逻辑。尝试关联Response表后，性能问题仍未得到解决。

性能瓶颈代码 #

导致性能问题的核心代码段：

projectType= internshipNameResponses.FirstOrDefault(y => y.QuestionID == 22).Answer,
additionalCriteria = internshipNameResponses.FirstOrDefault(y => y.QuestionID == 27).Answer,
tamidStudent = internshipNameResponses.FirstOrDefault(y => y.QuestionID == 28).Answer,
studentEmail = internshipNameResponses.FirstOrDefault(y => y.QuestionID == 2134).Answer,
jobDesc = internshipNameResponses.Where(y => y.QuestionID == 17).Select(x => x.Answer).ToList(),
experience = internshipNameResponses.Where(y => y.QuestionID == 21).Select(x => x.Answer).ToList(),
VCF = internshipNameResponses.Where(y => y.QuestionID == 4239).Select(x => x.AnswerCode).ToList(),
lang = internshipNameResponses.Where(y => y.QuestionID == 23).Select(x => x.Answer).ToList(),
codingLang = internshipNameResponses.Where(y => y.QuestionID == 24).Select(x => x.Answer).ToList(),
academic = internshipNameResponses.Where(y => y.QuestionID == 25).Select(x => x.Answer).ToList(),
HPD = internshipNameResponses.FirstOrDefault(y => y.QuestionID == 26).Answer,

瓶颈原因分析 #

1. 内存中执行子查询：EF无法将FirstOrDefault、Where+ToList这类针对join后集合的操作转换为SQL，只能先将所有关联数据拉取到内存，再逐个在内存中过滤处理，导致数据量爆炸且内存开销巨大。
2. 笛卡尔积数据膨胀：原查询中两次join Responses后使用from展开，会生成笛卡尔积，导致数据行数大幅增加，进一步加重内存处理的负担。
3. 重复查询逻辑：针对同一集合多次执行类似的过滤逻辑，重复计算导致额外性能损耗。

优化方案 #

1. 分组查询一次性获取所需数据 #

通过GroupBy按QuestionnaireResponseID分组Responses，一次性获取每个问卷响应对应的所有答案，避免多次子查询。

2. 避免内存中过滤操作 #

将针对QuestionID的过滤逻辑提前到SQL层面，或通过分组后的结果直接提取对应值，确保EF能生成高效的SQL语句。

3. 移除不必要的笛卡尔积 #

取消重复的join Responses操作，利用分组或导航属性一次性获取所有需要的响应数据。

优化后的查询示例 #

var result = (from cr in CompanyRepresentatives.Where(x => companyIds.Contains(x.ID))
              join p in QuestionnaireResponses on cr.ID equals p.RespondentID
              // 一次性获取所有Responses并按QuestionnaireResponseID分组
              join r in Responses on p.ID equals r.QuestionnaireResponseID into responsesGroup
              where p.QuestionnaireID == 2 && cr.Active == true && (includeHidden || !p.IsHidden)
                    && (string.IsNullOrEmpty(companyOrInternshipName) || cr.CompanyName.ToLower().Contains(companyOrInternshipName)
                        || responsesGroup.Any(y => y.QuestionID == 2130 && y.Answer.ToLower().Contains(companyOrInternshipName)))
              // 过滤出包含实习名称和可用岗位的问卷响应
              where responsesGroup.Any(y => y.QuestionID == 2130) && responsesGroup.Any(y => y.QuestionID == 29)
              orderby cr.CompanyName
              select new
              {
                  ID = p.ID,
                  // 从分组中获取实习名称
                  Name = responsesGroup.FirstOrDefault(y => y.QuestionID == 2130)?.Answer,
                  CompanyID = cr.ID,
                  Company = cr.CompanyName,
                  CompanyEmail = cr.Email,
                  CompanyDesc = cr.CompanyDescription,
                  usOfficeCity = cr.USOffice_City,
                  isrOfficeoth = cr.IsraelOffice_City_Other,
                  CompanyRank = cr.Rank,
                  // 从分组中直接提取对应QuestionID的值
                  projectType = responsesGroup.FirstOrDefault(y => y.QuestionID == 22)?.Answer,
                  additionalCriteria = responsesGroup.FirstOrDefault(y => y.QuestionID == 27)?.Answer,
                  tamidStudent = responsesGroup.FirstOrDefault(y => y.QuestionID == 28)?.Answer,
                  studentEmail = responsesGroup.FirstOrDefault(y => y.QuestionID == 2134)?.Answer,
                  AvailablePositions = responsesGroup.FirstOrDefault(y => y.QuestionID == 29)?.Answer,
                  FilledPositions = p.FilledVacancies,
                  Status = p.Status,
                  Visible = !p.IsHidden,
                  DatePosted = p.Created,
                  Rejected = p.IsInternshipRejected,
                  jobDesc = responsesGroup.Where(y => y.QuestionID == 17).Select(x => x.Answer).ToList(),
                  experience = responsesGroup.Where(y => y.QuestionID == 21).Select(x => x.Answer).ToList(),
                  VCF = responsesGroup.Where(y => y.QuestionID == 4239).Select(x => x.AnswerCode).ToList(),
                  lang = responsesGroup.Where(y => y.QuestionID == 23).Select(x => x.Answer).ToList(),
                  codingLang = responsesGroup.Where(y => y.QuestionID == 24).Select(x => x.Answer).ToList(),
                  academic = responsesGroup.Where(y => y.QuestionID == 25).Select(x => x.Answer).ToList(),
                  HPD = responsesGroup.FirstOrDefault(y => y.QuestionID == 26)?.Answer,
                  industry = cr.CompanyIndustry,
                  companySize = cr.CompanySize,
                  usOffice = cr.USOffice_City,
                  isrOffice = cr.IsraelOffice_City,
                  companyType = cr.CompanyType,
                  market = cr.CompanyTargetMarket,
                  financingStage = cr.FinancingStage
              }).ToList();

进一步优化：使用导航属性与预加载 #

如果实体类中QuestionnaireResponse与Response存在导航属性关系，可以直接使用Include预加载，结合投影进一步简化查询：

var result = (from p in QuestionnaireResponses.Include(r => r.Responses)
              where p.QuestionnaireID == 2 && p.CompanyRepresentative.Active == true
                    && companyIds.Contains(p.CompanyRepresentative.ID)
                    && (includeHidden || !p.IsHidden)
                    && (string.IsNullOrEmpty(companyOrInternshipName) || p.CompanyRepresentative.CompanyName.ToLower().Contains(companyOrInternshipName)
                        || p.Responses.Any(y => y.QuestionID == 2130 && y.Answer.ToLower().Contains(companyOrInternshipName)))
              where p.Responses.Any(y => y.QuestionID == 2130) && p.Responses.Any(y => y.QuestionID == 29)
              orderby p.CompanyRepresentative.CompanyName
              select new
              {
                  ID = p.ID,
                  Name = p.Responses.FirstOrDefault(y => y.QuestionID == 2130)?.Answer,
                  CompanyID = p.CompanyRepresentative.ID,
                  Company = p.CompanyRepresentative.CompanyName,
                  CompanyEmail = p.CompanyRepresentative.Email,
                  CompanyDesc = p.CompanyRepresentative.CompanyDescription,
                  usOfficeCity = p.CompanyRepresentative.USOffice_City,
                  isrOfficeoth = p.CompanyRepresentative.IsraelOffice_City_Other,
                  CompanyRank = p.CompanyRepresentative.Rank,
                  projectType = p.Responses.FirstOrDefault(y => y.QuestionID == 22)?.Answer,
                  additionalCriteria = p.Responses.FirstOrDefault(y => y.QuestionID == 27)?.Answer,
                  tamidStudent = p.Responses.FirstOrDefault(y => y.QuestionID == 28)?.Answer,
                  studentEmail = p.Responses.FirstOrDefault(y => y.QuestionID == 2134)?.Answer,
                  AvailablePositions = p.Responses.FirstOrDefault(y => y.QuestionID == 29)?.Answer,
                  FilledPositions = p.FilledVacancies,
                  Status = p.Status,
                  Visible = !p.IsHidden,
                  DatePosted = p.Created,
                  Rejected = p.IsInternshipRejected,
                  jobDesc = p.Responses.Where(y => y.QuestionID == 17).Select(x => x.Answer).ToList(),
                  experience = p.Responses.Where(y => y.QuestionID == 21).Select(x => x.Answer).ToList(),
                  VCF = p.Responses.Where(y => y.QuestionID == 4239).Select(x => x.AnswerCode).ToList(),
                  lang = p.Responses.Where(y => y.QuestionID == 23).Select(x => x.Answer).ToList(),
                  codingLang = p.Responses.Where(y => y.QuestionID == 24).Select(x => x.Answer).ToList(),
                  academic = p.Responses.Where(y => y.QuestionID == 25).Select(x => x.Answer).ToList(),
                  HPD = p.Responses.FirstOrDefault(y => y.QuestionID == 26)?.Answer,
                  industry = p.CompanyRepresentative.CompanyIndustry,
                  companySize = p.CompanyRepresentative.CompanySize,
                  usOffice = p.CompanyRepresentative.USOffice_City,
                  isrOffice = p.CompanyRepresentative.IsraelOffice_City,
                  companyType = p.CompanyRepresentative.CompanyType,
                  market = p.CompanyRepresentative.CompanyTargetMarket,
                  financingStage = p.CompanyRepresentative.FinancingStage
              }).ToList();

优化效果说明 #

通过上述优化，EF会将分组和过滤逻辑转换为SQL语句在数据库端执行，避免全量数据拉取到内存，同时消除笛卡尔积导致的数据膨胀，大幅降低查询耗时和内存占用。

内容的提问来源于stack exchange，提问作者Engr Umair