为什么实体框架需要30秒加载记录时生成的查询只需要第二1/2？ [英] Why is Entity Framework taking 30 seconds to load records when the generated query only takes 1/2 of a second?
问题描述
该executeTime下面是我下次执行同一套code 30秒一次，25秒。当SQL事件探查器看，我立刻看到一个登录，那么它只是坐在那里约30秒。然后只要选择语句运行，应用程序完成的了ToList命令。当我从Management Studio中运行生成的查询，数据库查询只需要大约400毫秒。它返回14行和350列。它看起来像花费的时间改变数据库结果的实体是如此之小，它不是noticable。
那么，什么是发生在30秒内数据库调用之前？
如果实体框架是这种缓慢的，这不是我们能够使用它。有什么我做错了什么，我可以改变，以加快这极大地？
更新： 好吧，如果我用一个编译的查询，第一时间就需要30秒，第二次就需要一秒钟的1/4。有什么我可以做，以加快在第一次调用？
使用（EntitiesContext上下文=新EntitiesContext（））
{
秒表SW =新的秒表（）;
sw.Start（）;
VAR groupQuery =（从context.Groups.Include克（DealContract）
.INCLUDE（DealContract.Contracts）
.INCLUDE（DealContract.Contracts.AdvertiserAccountType1）
.INCLUDE（DealContract.Contracts.ContractItemDetails）
.INCLUDE（DealContract.Contracts.Brands）
.INCLUDE（DealContract.Contracts.Agencies）
.INCLUDE（DealContract.Contracts.AdvertiserAccountType2）
.INCLUDE（DealContract.Contracts.ContractProductLinks.Products）
.INCLUDE（DealContract.Contracts.ContractPersonnelLinks）
.INCLUDE（DealContract.Contracts.ContractSpotOrderTypes）
.INCLUDE（DealContract.Contracts.Advertisers）
其中，g.GroupKey == 6
选择G）.OfType＆LT;应对＆GT;（）;
sw.Stop（）;
VAR queryTime = sw.Elapsed;
sw.Reset（）;
sw.Start（）;
变种组= groupQuery.ToList（）;
sw.Stop（）;
VAR executeTime = sw.Elapsed;
}
我有此相同的问题，我的查询正在采取40秒。
我发现这个问题是与.INCLUDE（表格名）的功能。这些我有更多的，它是雪上加霜。相反，我改变了我的code到延迟加载所有我需要在查询之后的数据，被撞的总时间缩短到约1.5秒，从40秒。据我所知，这将完成同样的事情。
因此，对于你code这将是这样的：
VAR groupQuery =（从context.Groups克
其中，g.GroupKey == 6
选择G）.OfType＆LT;应对＆GT;（）;
变种组= groupQuery.ToList（）;
的foreach（在组VAR G）
{
//假设Dealcontract是一个对象，没有对象的集合
g.DealContractReference.Load（）;
如果（g.DealContract！= NULL）
{
的foreach（VAR d在g.DealContract）
{
//如果引用是一个集合，你可以到直.Load
//如果是一个对象，你所说的.Load关于refence，而不是像g.DealContractReference以上
d.Contracts.Load（）;
的foreach（在d.Contracts变种C）
{
c.AdvertiserAccountType1Reference.Load（）;
// 等等....
}
}
}
}
顺便说一句，如果你要加入这一行的code在当前code上面的查询，这将敲时间缩短到约4-5秒（仍然过于玲在我的选项）从什么我明白了，MergeOption.NoTracking选项禁用了很多跟踪开销更新和插入的东西回数据库：
context.groups.MergeOption = MergeOption.NoTracking;
The executeTime below is 30 seconds the first time, and 25 seconds the next time I execute the same set of code. When watching in SQL Profiler, I immediately see a login, then it just sits there for about 30 seconds. Then as soon as the select statement is run, the app finishes the ToList command. When I run the generated query from Management Studio, the database query only takes about 400ms. It returns 14 rows and 350 columns. It looks like time it takes transforming the database results to the entities is so small it is not noticable.
So what is happening in the 30 seconds before the database call is made?
If entity framework is this slow, it is not possible for us to use it. Is there something I am doing wrong or something I can change to speed this up dramatically?
UPDATE: All right, if I use a Compiled query, the first time it take 30 seconds, and the second time it takes 1/4th of a second. Is there anything I can do to speed up the first call?
using (EntitiesContext context = new EntitiesContext())
{
Stopwatch sw = new Stopwatch();
sw.Start();
var groupQuery = (from g in context.Groups.Include("DealContract")
.Include("DealContract.Contracts")
.Include("DealContract.Contracts.AdvertiserAccountType1")
.Include("DealContract.Contracts.ContractItemDetails")
.Include("DealContract.Contracts.Brands")
.Include("DealContract.Contracts.Agencies")
.Include("DealContract.Contracts.AdvertiserAccountType2")
.Include("DealContract.Contracts.ContractProductLinks.Products")
.Include("DealContract.Contracts.ContractPersonnelLinks")
.Include("DealContract.Contracts.ContractSpotOrderTypes")
.Include("DealContract.Contracts.Advertisers")
where g.GroupKey == 6
select g).OfType<Deal>();
sw.Stop();
var queryTime = sw.Elapsed;
sw.Reset();
sw.Start();
var groups = groupQuery.ToList();
sw.Stop();
var executeTime = sw.Elapsed;
}
I had this exact same problem, my query was taking 40 seconds.
I found the problem was with the .Include("table_name") functions. The more of these I had, the worse it was. Instead I changed my code to Lazy Load all the data I needed right after the query, this knocked the total time down to about 1.5 seconds from 40 seconds. As far as I know, this accomplishes the exact same thing.
So for your code it would be something like this:
var groupQuery = (from g in context.Groups
where g.GroupKey == 6
select g).OfType<Deal>();
var groups = groupQuery.ToList();
foreach (var g in groups)
{
// Assuming Dealcontract is an Object, not a Collection of Objects
g.DealContractReference.Load();
if (g.DealContract != null)
{
foreach (var d in g.DealContract)
{
// If the Reference is to a collection, you can just to a Straight ".Load"
// if it is an object, you call ".Load" on the refence instead like with "g.DealContractReference" above
d.Contracts.Load();
foreach (var c in d.Contracts)
{
c.AdvertiserAccountType1Reference.Load();
// etc....
}
}
}
}
Incidentally, if you were to add this line of code above the query in your current code, it would knock the time down to about 4-5 seconds (still too ling in my option) From what I understand, the "MergeOption.NoTracking" option disables a lot of the tracking overhead for updating and inserting stuff back into the database:
context.groups.MergeOption = MergeOption.NoTracking;
这篇关于为什么实体框架需要30秒加载记录时生成的查询只需要第二1/2？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
