实体框架性能问题 [英] Entity Framework Performance Issue

问题描述

我正在与实体框架一个有趣的性能问题。我使用code第一。

下面是我的实体的结构：

这本书可以有很多评论。 回顾与一本书有关。 回顾可以有一个或多个评论。 评析与一个审查有关。

 公共类图书
{
    公众诠释BOOKID {获得;组; }
    // ...
    公众的ICollection＆LT;审查＆GT;评论{获得;组; }
}

公共类评论
{
    公众诠释ReviewId {获得;组; }
    公众诠释BOOKID {获得;组; }
    公共图书图书{获得;组; }
    公众的ICollection＆LT;注释＆GT;评论{获得;组; }
}

公共类评论
{
     公众诠释CommentId {获得;组; }
     公众诠释ReviewId {获得;组; }
     大众点评{获得;组; }
}
 

我填充我的数据库中有大量数据，并添加适当的索引。我想找回一个单一的书，拥有1万条针对它使用此查询：

  VAR bookAndReviews = db.Books.Where（B =＆GT; b.BookId == ID）
                       .INCLUDE（B =＆GT; b.Reviews）
                       .FirstOrDefault（）;
 

这本特别的书拥有10000条评论。这个查询的性能约为4秒。运行（通过SQL事件探查器）完全相同的查询实际上返回在任何时候都。我用同样的查询和一个SqlDataAdapter和自定义对象检索数据和它发生在在500毫秒。

使用蚂蚁性能分析器，它看起来像一大块的时间花在做一些不同的事情：

equals方法被调用5000万次。

没有人知道为什么它会需要调用这个5000万次，我怎么能增加这种表现？

解决方案

  

为什么Equals已叫50M倍？

这听起来很可疑。你有10.000审查和50.000.000调用等于。假设这是由于由EF内部实现身份映射。标识映射确保了独特的键每个实体通过上下文跟踪只有一次，所以如果背景下已经有实例，使用相同的密钥从数据库加载记录，它将无法实现新的实例，而是使用现有之一。现在，如何能与这些数字相吻合？我可怕的猜想：

  ====================================== =======
第一纪录阅读| 0比较
第二记录阅读| 1对比
第三记录阅读| 2比较
...
10.000th记录阅读| 9.999的比较
 

这意味着，每一个新的记录与标识映射每一个现存的记录进行比较。通过应用数学来计算所有我们可以使用一种叫做等差数列的比较和：

  A（N）= A（N-1）+ 1
总和（N）=（N / 2）*（一（1）+ A（n））的
琛（10.000）= 5.000 *（0 + 9.999）=＆GT; 5.000 * 10.000 = 50.000.000
 

我希望我没有让错误在我的假设或计算。等待！我希望我做的错误，因为这似乎不是很好。

尝试关闭更改跟踪=希望关闭标识映射检查。

这可能会非常棘手。首先：

  VAR bookAndReviews = db.Books.Where（B =＆GT; b.BookId == ID）
                             .INCLUDE（B =＆GT; b.Reviews）
                             .AsNoTracking（）
                             .FirstOrDefault（）;
 

但有一个很大的机会，你的导航属性将不会填充（因为它是由更改跟踪处理）。在这种情况下使用这种方法：

  VAR书= db.Books.Where（B =＆GT; b.BookId == ID）.AsNoTracking（）FirstOrDefault（）。
book.Reviews = db.Reviews.Where性（r =＆GT; r.BookId ==内径）。.AsNoTracking（）了ToList（）;
 

反正你能看到什么对象类型传递到的Equals？我认为应该只比较主键，甚至50M整数比较不应该出现这样的问题。

作为一个方面说明EF是缓慢的 - 这是众所周知的事实。物化实体时如此简单10.000记录，可以采取一段时间它还在内部使用反射。除非你已经这样做，你也可以关闭动态代理创建（ db.Configuration.ProxyCreationEnabled ）。

I am running into an interesting performance issue with Entity Framework. I am using Code First.

Here is the structure of my entities:

A Book can have many Reviews. A Review is associated with a single Book. A Review can have one or many Comments. A Comment is associated with one Review.

public class Book
{
    public int BookId { get; set; }
    // ...
    public ICollection<Review> Reviews { get; set; }
}

public class Review 
{
    public int ReviewId { get; set; }
    public int BookId { get; set; }
    public Book Book { get; set; }
    public ICollection<Comment> Comments { get; set; }
}

public class Comment
{
     public int CommentId { get; set; }
     public int ReviewId { get; set; }
     public Review Review { get; set; }
}

I populated my database with a lot of data and added the proper indexes. I am trying to retrieve a single book that has 10,000 reviews on it using this query:

var bookAndReviews = db.Books.Where(b => b.BookId == id)
                       .Include(b => b.Reviews)
                       .FirstOrDefault();

This particular book has 10,000 reviews. The performance of this query is around 4 seconds. Running the exact same query (via SQL Profiler) actually returns in no time at all. I used the same query and a SqlDataAdapter and custom objects to retrieve the data and it happens in under 500 milliseconds.

Using ANTS Performance Profiler it looks like a bulk of the time is being spent doing a few different things:

The Equals method is being called 50 million times.

Does anyone know why it would need to call this 50 million times and how I could increase the performance for this?

解决方案

Why is Equals called 50M times?

It sounds quite suspicious. You have 10.000 reviews and 50.000.000 calls to Equals. Suppose that this is caused by identity map internally implemented by EF. Identity map ensures that each entity with unique key is tracked by the context only once so if context already has instance with the same key as loaded record from the database it will not materialize new instance and instead uses the existing one. Now how this can coincide with those numbers? My terrifying guess:

=============================================
1st      record read   |  0     comparisons
2nd      record read   |  1     comparison
3rd      record read   |  2     comparisons
...
10.000th record read   |  9.999 comparisons

That means that each new record is compared with every existing record in identity map. By applying math to compute sum of all comparison we can use something called "Arithmetic sequence":

a(n) = a(n-1) + 1
Sum(n) = (n / 2) * (a(1) + a(n))
Sum(10.000) = 5.000 * (0 + 9.999) => 5.000 * 10.000 = 50.000.000

I hope I didn't make mistake in my assumptions or calculation. Wait! I hope I did mistake because this doesn't seem good.

Try turning off change tracking = hopefully turning off identity map checking.

It can be tricky. Start with:

var bookAndReviews = db.Books.Where(b => b.BookId == id)
                             .Include(b => b.Reviews)
                             .AsNoTracking()
                             .FirstOrDefault();

But there is a big chance that your navigation property will not be populated (because it is handled by change tracking). In such case use this approach:

var book = db.Books.Where(b => b.BookId == id).AsNoTracking().FirstOrDefault();
book.Reviews = db.Reviews.Where(r => r.BookId == id).AsNoTracking().ToList();

Anyway can you see what object type is passed to Equals? I think it should compare only primary keys and even 50M integer comparisons should not be such a problem.

As a side note EF is slow - it is well known fact. It also uses reflection internally when materializing entities so simply 10.000 records can take "some time". Unless you already did that you can also turn off dynamic proxy creation (db.Configuration.ProxyCreationEnabled).

这篇关于实体框架性能问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！