当生成的查询只需要1/2秒时，为什么Entity Framework需要30秒才能加载记录？

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事件探查器中观看时,我立即看到登录,然后它就在那里停留约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; 
}

4 个解决方案

#1

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”)函数中。我拥有的越多,它就越糟糕。相反,我在查询后立即将我的代码更改为Lazy Load所需的所有数据,这将总时间从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.NoTracking option 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;

#2

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.

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

#3

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 Framework and Compiled Queries don'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秒,但它不会保存任何其他东西。

#4

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的构建元数据,并且可能生成用于映射的对象。所以启动需要几秒钟,我不认为有办法解决这个问题,除非从不重启你的程序。

#1