.NET LINQ查询准备问题 [英] Issue with .NET LINQ query preparation

问题描述

我们在linq查询中遇到问题。在select查询中，多个表与联接相关联。记录过滤器应用于连接部分的位置。此查询运行正常，并在过去8到9个月内及时给出了正确的结果。

突然花了大约2分钟的时间来检索具有相同连接的记录并消耗99％的CPU。



正在使用此查询总是在用户登录时确保它在过去直到发布时刻运行正常。

任何人都可以帮助解决为什么突然发生这个问题吗？

 var entityList =（from db.TableA中的x 
在db.TableB中加入y 
在x.Id上等于y.TableC.TableAId到g 
来自d in g.DefaultIfEmpty（）
 where（d.EndDateTime == null&&（d == null || d.TableE.PersonID == personId））
&& x.EndDateTime == null 
& ;& d.TableC.EndDateTime == null 
&& x.TableE.PersonID == personId 
选择新的
 {
 Col1 = d！= null？ d.TableC.TableD.Id：0，
 Col2 = d！= null？ d.TableC.Id：0，
}）。AsQueryable（）; 
 

我的尝试：



我们检查了表的索引。

解决方案

您需要查看实体框架生成的基础SQL正在做什么。然后，拿出相同的sql并通过sql server（假设）运行它，打开查询分析器，看看瓶颈在哪里或缺少索引（如果有的话）。



要记录实体框架生成的sql，您需要执行类似的操作。假设您使用的是log4net。

 var log = LogManager.GetLogger（MethodBase.GetCurrentMethod（）。DeclaringType）; 
 db.Database.Log = log.Info; 
 //或者你可以做
 db.Database.Log = Debug.WriteLine; 

这样就可以了将生成的sql输出到日志文件或调试输出窗口。



从那里，取出sql，在管理工作室中运行它，分析查询并调整你的linq从那里查询。实体框架/ linq因创建一些没有意义的令人讨厌的sql而臭名昭着，所以我感觉你在查询中做了很多空检查就是生成一个巨大的sql语句，它正在你的CPU上吃。

We are having an issue in linq query. In select query multiple tables are associated with joins. Where record filters were applied on join section. This query was running fine and giving proper result within time since past 8 to 9 months.
Suddenly it took time around 2 minute to retrieve the record with the same join and consuming 99% CPU.

This query is being used always while user login so its sure that it was running fine in past till issue moment.
Can any one help why this issue occurred suddenly?

var entityList = (from x in db.TableA
                           join y in db.TableB
                           on x.Id equals y.TableC.TableAId into g
                           from d in g.DefaultIfEmpty()
                           where (d.EndDateTime == null && (d == null || d.TableE.PersonID == personId))
                                 && x.EndDateTime == null
                                 && d.TableC.EndDateTime == null
                                 && x.TableE.PersonID == personId
                           select new
                           {
                             Col1 = d != null ? d.TableC.TableD.Id : 0,
                             Col2 = d != null ? d.TableC.Id : 0,
                           }).AsQueryable();

What I have tried:

We have checked indexes of tables.

解决方案

You need to see what the underlying SQL being generated by entity framework is doing. Then, take that same sql and run it through sql server (assumingly) with query analyzer turned on to see where the bottle necks or missing indexes (if any) are.

To log the sql being generated by entity framework you'd need to do something like this. Assuming you are using log4net for example.

var log = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);
db.Database.Log = log.Info;
// or you could do
db.Database.Log = Debug.WriteLine;

This would then output the generated sql to a log file or the debug output window.

From there, take that sql, run it in management studio, anaylze the query and adjust your linq query from there. Entity framework/linq is notorious for creating some nasty sql that doesn't make sense so I have a feeling a lot of your null checking you are doing in your query is generating a gigantic sql statement that is eating at your CPU.

这篇关于.NET LINQ查询准备问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！