C# 当生成的查询只需要 1/2 秒时，为什么实体框架需要 30 秒才能加载记录？

Why is Entity Framework taking 30 seconds to load records when the generated query only takes 1/2 of a second?

提问by NotDan

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.

下面的 executeTime 第一次是 30 秒，下次我执行同一组代码时是 25 秒。在 SQL Profiler 中观看时，我立即看到一个登录信息，然后它就在那里停留了大约 30 秒。然后，只要运行 select 语句，应用程序就会完成 ToList 命令。当我从 Management Studio 运行生成的查询时，数据库查询只需要大约 400 毫秒。它返回 14 行和 350 列。看起来将数据库结果转换为实体所花费的时间太小了，以至于不会引起注意。

So what is happening in the 30 seconds before the database call is made?

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

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?

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

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

采纳答案by Chris Dutrow

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

我遇到了完全相同的问题，我的查询需要 40 秒。

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.

我发现问题出在.Include("table_name")功能上。我拥有的越多，情况就越糟。相反，我将代码更改为在查询后立即加载我需要的所有数据，这将总时间从 40 秒缩短到大约 1.5 秒。据我所知，这完成了完全相同的事情。

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.NoTrackingoption disables a lot of the tracking overhead for updating and inserting stuff back into the database:

顺便说一句，如果您要在当前代码中的查询上方添加这行代码，它将把时间缩短到大约 4-5 秒（在我的选项中仍然太长）据我所知，该MergeOption.NoTracking选项禁用了很多更新和将内容插入回数据库的跟踪开销：

context.groups.MergeOption = MergeOption.NoTracking;

回答by ArielBH

It is because of the Include. My guess is that you are eager loading a lot of objects into the memory. It takes much time to build the c# objects that corresponds to your db entities.

这是因为包含。我的猜测是您渴望将大量对象加载到内存中。构建对应于您的 db 实体的 c# 对象需要很多时间。

My recommendation for you is to try to lazy load only the data you need.

我对您的建议是尝试仅延迟加载您需要的数据。

回答by AndreasN

EF takes a while to start up. It needs build metadata from xml and probably generates objects used for mapping. So it takes a few sec to start up, i don't think there is a way to get around that, except never restarting your program.

EF 需要一段时间才能启动。它需要从 xml 构建元数据，并可能生成用于映射的对象。所以启动需要几秒钟，我认为没有办法解决这个问题，除非永远不要重新启动你的程序。

回答by Craig Stuntz

The only way to make the initial compilation of the query faster that I know of is to make the query less complex. The MSDN documentation on performance considerations for the Entity Frameworkand Compiled Queriesdon't indicate that there is any way to save a compiled query for use in a different application execution session.

据我所知，使查询的初始编译更快的唯一方法是使查询不那么复杂。关于实体框架和编译查询的性能注意事项的 MSDN 文档没有表明有任何方法可以保存编译查询以在不同的应用程序执行会话中使用。

I would add that we have found that having lots of Includes can make query execution slower than having fewer Includes and doing more Loads on related entities later. Some trial and error is required to find the right medium.

我要补充的是，我们发现，与包含较少的包含和稍后在相关实体上执行更多的加载相比，拥有大量包含可以使查询执行更慢。需要反复试验才能找到合适的介质。

However, I have to ask if you really need every property of every entity you are including here. It seems to me that there is a large number of different entity types in this query, so materializing them could well be quite expensive. If you are just trying to get tabular results which you don't intend to update, projecting the (relatively) fewer number of fields that you actually need into a flat, anonymous type should be significantly faster for various reasons. Also, this frees you from having to worry about eager loading, calling Load/IsLoaded, etc.

但是，我不得不问您是否真的需要您在此处包含的每个实体的每个属性。在我看来，此查询中有大量不同的实体类型，因此实现它们可能非常昂贵。如果您只是想获得不打算更新的表格结果，由于各种原因，将您实际需要的（相对）较少数量的字段投影到平面匿名类型中应该会明显更快。此外，这使您不必担心急切加载、调用 Load/IsLoaded 等。

You can certainly speed up the initial view generation by precompiling the entity views. There is documentation on MSDN for this. But since you pay that cost at the time the first query is executed, your test with a simple query shows that this is running in the neighborhood of 2 seconds for you. It's nice to say that 2 seconds, but it won't save anything else.

您当然可以通过预编译实体视图来加速初始视图的生成。MSDN 上有这方面的文档。但是由于您在执行第一个查询时支付了该成本，因此您使用简单查询的测试表明，这对您来说运行时间大约为 2 秒。说 2 秒很好，但它不会保存任何其他内容。