使用EF加载巨大的实体树

I need to load a model, existing of +/- 20 tables from the database with Entity Framework.

我需要使用Entity Framework从数据库中加载一个+/- 20个表的模型。

So there are probably a few ways of doing this:

所以可能有几种方法可以做到这一点:

Use one huge Include call

使用一个巨大的包含电话

Use many Includes calls while manually iterating the model

在手动迭代模型时使用许多包含调用

Use many IsLoaded and Load calls

使用许多IsLoaded和Load调用

Here's what happens with the 2 options

以下是2个选项的情况

EF creates a HUGE query, puts a very heavy load on the DB and then again with mapping the model. So not really an option.

EF创建了一个巨大的查询,在数据库上施加了非常大的负载,然后再次映射模型。所以不是一个选择。
The database gets called a lot, with again pretty big queries.

数据库被调用很多,同样有很大的查询。
Again, the database gets called even more, but this time with small loads.

同样,数据库被调用得更多,但这次是负载很小。

All of these options weigh heavy on the performance. I do need to load all of that data (calculations for drawing).

所有这些选择都会对性能造成沉重打击。我确实需要加载所有数据(绘图计算)。

So what can I do?

那我该怎么办?

a) Heavy operation => heavy load => do nothing :) b) Review design => but how? c) A magical option that will make all these problems go away

a)繁重的操作=>重负荷=>什么都不做:) b)回顾设计=>但是如何? c)一个神奇的选择,将使所有这些问题消失

3 个解决方案

#1

When you need to load a lot of data from a lack of different tables, there is no "magic" solution which makes all problems go away. But in addition to what you have already discussed, you should consider projection. If you don't need every single property of an entity, it is often cheaper to project the information you do need, i.e.:

当您需要从缺少不同的表中加载大量数据时,没有“神奇”的解决方案可以解决所有问题。但除了你已经讨论过的内容之外,你应该考虑投影。如果您不需要实体的每一个属性,那么投射您需要的信息通常会更便宜,即:

from parent in MyEntities.Parents
select new
{
    ParentName = ParentName,
    Children = from child in parent.Children
               select new
               {
                   ChildName = child.Name
               }
}

One other thing to keep in mind is that for very large queries, the cost of compiling the query can often exceed the cost of executing it. Only profiling can tell you if this is the problem. If this turns out to be the problem, consider using CompiledQuery.

另外要记住的是,对于非常大的查询,编译查询的成本通常会超过执行它的成本。只有剖析可以告诉您这是否是问题所在。如果这是问题所在,请考虑使用CompiledQuery。

#2

You might analyze the ratio of queries to updates. If you mostly upload the model once, then everything else is a query, then maybe you should store an XML representation of the model in the database as a "shadow" of the model. You should be able to either read the entire XML column in at once fairly quickly, or else maybe you can do your calculations (or at least the fetch of the values necessary for the calculations) using XQuery.

您可以分析查询与更新的比率。如果您主要上传模型一次,那么其他所有内容都是查询,那么您可能应该将模型的XML表示存储在数据库中作为模型的“阴影”。您应该能够相当快地读取整个XML列,或者也许您可以使用XQuery进行计算(或至少获取计算所需的值)。

This assumes SQL Server 2005 or above.

这假定SQL Server 2005或更高版本。

#3

You could consider caching your data in memory instead of getting it from the database each time.

您可以考虑将数据缓存在内存中,而不是每次都从数据库中获取数据。

I would recommend Enterprise Library Caching Application block: http://msdn.microsoft.com/en-us/library/dd203099.aspx

我建议使用Enterprise Library Caching Application块:http://msdn.microsoft.com/en-us/library/dd203099.aspx

#1

from parent in MyEntities.Parents
select new
{
    ParentName = ParentName,
    Children = from child in parent.Children
               select new
               {
                   ChildName = child.Name
               }
}

#2