为什么当生成的查询只需要1/2秒时，实体框架需要30秒才能加载记录？ [英] Why is Entity Framework taking 30 seconds to load records when the generated query only takes 1/2 of a second?

问题描述

下面的executeTime是第一次30秒，下次执行相同的代码集25秒。当在SQL Profiler中观看时，我立即看到一个登录名，然后它坐在那里约30秒钟。然后一旦select语句运行，该应用程序将完成ToList命令。当我从Management Studio运行生成的查询时，数据库查询只需要大约400ms。它返回14行和350列。看起来好像将数据库结果转换为实体的时间是如此之小，这是不值得的。

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.

那么在数据库调用之前的30秒内发生了什么？

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?

更新：
好​​的，如果我使用一个编译查询，第一次需要30秒，第二次需要1/4秒。有没有什么可以做的，以加快第一个电话？

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; 
} 

推荐答案

我有这个完全相同的问题我的查询花了40秒。

I had this exact same problem, my query was taking 40 seconds.

我发现问题是与 .Include（table_name）功能。我越多，越是糟糕。相反，我将代码更改为Lazy加载查询后所需的所有数据，这将总时间从40秒缩短到约1.5秒。据我所知，这完成了同样的事情。

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....
            }
        }
    }
}

顺便提一句，如果你要在当前代码中的这个查询中添加这一行代码，那么它会将时间缩短到大约4-5秒（依然在我的选择中）From我明白， MergeOption.NoTracking 选项禁用了大量跟踪开销，用于更新和插入资料回到数据库中：

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;

这篇关于为什么当生成的查询只需要1/2秒时，实体框架需要30秒才能加载记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！