EntityFramework 中的 .Include() 与 .Load() 性能 [英] .Include() vs .Load() performance in EntityFramework
问题描述
当查询一个大表时，您需要稍后在代码中访问导航属性(我明确不想使用延迟加载)什么会更好地执行 .Include() 或 >.Load()?或者为什么要使用一个而不是另一个?
When querying a large table where you need to access the navigation properties later on in code (I explicitly don't want to use lazy loading) what will perform better .Include() or .Load()? Or why use the one over the other?
在这个例子中，包含的表都只有大约 10 个条目，员工有大约 200 个条目，并且可能会发生大多数情况下无论如何都会用 include 加载，因为它们匹配 where 子句.
In this example the included tables all only have about 10 entries and employees has about 200 entries, and it can happen that most of those will be loaded anyway with include because they match the where clause.
Context.Measurements.Include(m => m.Product)
.Include(m => m.ProductVersion)
.Include(m => m.Line)
.Include(m => m.MeasureEmployee)
.Include(m => m.MeasurementType)
.Where(m => m.MeasurementTime >= DateTime.Now.AddDays(-1))
.ToList();
或
Context.Products.Load();
Context.ProductVersions.Load();
Context.Lines.Load();
Context.Employees.Load();
Context.MeasurementType.Load();
Context.Measurements.Where(m => m.MeasurementTime >= DateTime.Now.AddDays(-1))
.ToList();
推荐答案
看情况，两个都试试
当使用 Include() 时，您可以获得在一次调用基础数据存储时加载所有数据的好处.例如，如果这是一个远程 SQL Server，这可能会大大提高性能.
It depends, try both
When using Include(), you get the benefit of loading all of your data in a single call to the underlying data store. If this is a remote SQL Server, for example, that can be a major performance boost.
缺点是 Include() 查询往往会真的 复杂，特别是如果您有任何过滤器(例如，Where() 调用)或尝试进行任何分组.EF 将使用 sub-SELECT 和 APPLY 语句生成非常严重的嵌套查询以获取您想要的数据.它的效率也低得多——您将返回一行数据，其中包含每个可能的子对象列，因此顶级对象的数据将重复很多次.(例如，具有 10 个子项的单个父对象将生成 10 行，每行都具有父对象列的相同数据.)我曾遇到过单个 EF 查询变得如此复杂，以至于导致死锁与 EF 更新逻辑同时运行.
The downside is that Include() queries tend to get really complicated, especially if you have any filters (Where() calls, for example) or try to do any grouping. EF will generate very heavily nested queries using sub-SELECT and APPLY statements to get the data you want. It is also much less efficient -- you get back a single row of data with every possible child-object column in it, so data for your top level objects will be repeated a lot of times. (For example, a single parent object with 10 children will product 10 rows, each with the same data for the parent-object's columns.) I've had single EF queries get so complex they caused deadlocks when running at the same time as EF update logic.
Load() 方法简单.每个查询都是针对单个表的单个、简单、直接的 SELECT 语句.这些在所有可能的方式中都容易得多，除了你必须做很多(可能更多倍).如果您有嵌套的集合集合，您甚至可能需要遍历顶级对象并Load 它们的子对象.它可能会失控.
The Load() method is much simpler. Each query is a single, easy, straightforward SELECT statement against a single table. These are much easier in every possible way, except you have to do many of them (possibly many times more). If you have nested collections of collections, you may even need to loop through your top level objects and Load their sub-objects. It can get out of hand.
尽量避免在一个查询中出现任何超过三个Include调用.我发现 EF 的查询变得太难看，无法识别；它也符合我对 SQL Server 查询的经验法则，单个查询中最多四个 JOIN 语句效果很好，但在那之后是时候考虑重构了.
Try to avoid having any more than three Include calls in a single query. I find that EF's queries get too ugly to recognize beyond that; it also matches my rule-of-thumb for SQL Server queries, that up to four JOIN statements in a single query works very well, but after that it's time to consider refactoring.
然而，所有这一切都只是一个起点.
However, all of that is only a starting point.
这取决于您的架构、环境、数据以及许多其他因素.
最后，您只需要尝试各种方式.
选择一个合理的默认"模式来使用，看看它是否足够好，如果不够，请根据口味进行优化.
这篇关于EntityFramework 中的 .Include() 与 .Load() 性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
