实体框架5表现不佳 [英] Entity Framework 5 poor performance

问题描述

我有5个实体：

public class Album
{
    public int Id { get; set; }

    public string Title { get; set; }

    public virtual List<AlbumArtist> AlbumArtists { get; set; }
    public virtual List<Artist> Artists { get; set; }
    public virtual List<Genre> Genres { get; set; }
    public virtual List<Song> Songs { get; set; }

}

public class AlbumArtist
{
    public int Id { get; set; }

    public string Title { get; set; }

    public virtual List<Album> Albums { get; set; }
    public virtual List<Artist> Artists { get; set; }
    public virtual List<Genre> Genres { get; set; }
    public virtual List<Song> Songs { get; set; }
}

public class Artist
{
    public int Id { get; set; }

    public string Title { get; set; }

    public virtual List<AlbumArtist> AlbumArtists { get; set; }
    public virtual List<Album> Albums { get; set; }
    public virtual List<Genre> Genres { get; set; }
    public virtual List<Song> Songs { get; set; }
}

public class Genre
{
    public int Id { get; set; }

    public string Title { get; set; }

    public virtual List<AlbumArtist> AlbumArtists { get; set; }
    public virtual List<Album> Albums { get; set; }
    public virtual List<Artist> Artists { get; set; }
    public virtual List<Song> Songs { get; set; }
}

public class Song
{
    public int Id { get; set; }

    public string Title { get; set; }

    public virtual List<AlbumArtist> AlbumArtists { get; set; }
    public virtual List<Album> Albums { get; set; }
    public virtual List<Artist> Artists { get; set; }
    public virtual List<Genre> Genres { get; set; }
}

正如你可以看到，有很多很多的一对多关系。我填充我的实体，然后尝试将它们保存到的DbContext以这种方式：

As you can see, there are a lot of many-to-many relationships. I populate my entities and then try to save them to DbContext in that way:

_albumArtists.ForEach(delegate(AlbumArtist albumArtist)
{
    if (albumArtist.Id == 0)
    {
            _dbContext.Entry(entity).State = EntityState.Added;
            _dbContext.SaveChanges();
    }
    else
    {
            _dbContext.Entry(entity).State = EntityState.Modified;
            _dbContext.SaveChanges();
    }
});
...

或那样：

_albumArtists.ForEach(delegate(AlbumArtist albumArtist)
{
    if (albumArtist.Id == 0)
    {
            _dbContext.Entry(entity).State = EntityState.Added;
    }
    else
    {
            _dbContext.AlbumArtists.State = EntityState.Modified;
    }
});
_dbContext.SaveChanges();
...

这需要永远我的实体保存到的DbContext。我甚至试着做到以下几点：

It takes forever to save my entities to DbContext. I even tried to do the following:

Configuration.AutoDetectChangesEnabled = false;

但它并没有帮助。顺便说一句，有大约17 000歌曲和1 700相册。

But it didn't helped. By the way, there are for about 17 000 Songs and 1 700 Albums.

什么是错的？

请帮忙

PS

下面是我完整的代码：<！ A HREF =htt​​ps://github.com/vjacheslavravdin/PsyTrance/blob/master/PsyTrance/Program.cs相对=nofollow> https://github.com/vjacheslavravdin/PsyTrance/blob/master/PsyTrance/ Program.cs的
也许你可以建议如何简化它。

Here is my full code: https://github.com/vjacheslavravdin/PsyTrance/blob/master/PsyTrance/Program.cs Maybe you can suggest how to simplify it.

谢谢！

推荐答案

首先一对夫妇澄清：

EF并不比其他方法基于批处理操作显著慢。在我的测试，你可以得到一个可能提高50％用原始的SQL命令，更快，也许高达10倍SQL批量复制，但EF不大于（虽然经常被视为非常慢）比较的方法作为一般规则慢得多。对于大多数应用EF甚至会给出正确的调整一批场景给适当的性能数字。 （见我的文章在这里： http://blog.staticvoid.co.nz/2012 / 3/24 / entity_framework_comparative_performance 和 HTTP：//blog.staticvoid。 co.nz/2012/8/17/mssql_and_large_insert_statements ）

EF is not significantly slower than other methods for batch based operations. in my tests you can get perhaps a 50% improvement with a raw SQL Command and maybe up to 10 times faster with SQL Bulk copy but EF is not much slower than comparative methods as a general rule (though often perceived as very slow). For most applications EF will give suitable performance numbers even in batch scenarios given the correct tuning. (see my article here: http://blog.staticvoid.co.nz/2012/3/24/entity_framework_comparative_performance and http://blog.staticvoid.co.nz/2012/8/17/mssql_and_large_insert_statements)

由于道路EF不会改变跟踪其有潜力远远超过的多大多表现人们会编写基于的SqlCommand插入语句（那里有一大堆caviats做查询规划，往返和交易，使得它非常难写最佳运行BULK INSERT语句）。我提出这些添加到这里（ http://entityframework.codeplex.com/discussions/377636），但还没有实现他们没有。

Because of the way EF does change tracking its has potential to far exceed the performance of how most people would write SqlCommand based insert statements (theres a whole lot of caviats to do with query planning, round trips and transactions that make it pretty hard to write optimally performing bulk insert statements). I have proposed these additions to EF here (http://entityframework.codeplex.com/discussions/377636) but havent implemented them yet.

您是完全正确你的决定关闭自动侦测的变化，每个。新增或.Attach行动，发现变化上列举了跟踪图形，因此，如果你是在同样的情况下加入17K补充则需要在共17000 + 16999 + ... + 2 + 1 = 144500000实体的17000倍列举图，难怪它这么长时间吧？ （见我的文章在这里： http://blog.staticvoid.co.nz/2012 / 5/7 / entityframework_performance_and_autodetectchanges ）

You are exactly right with your decision to turn off auto detect changes, each .Add or .Attach action with detect changes on enumerates the tracking graph, therefore if you're adding 17k additions on the same context you will need to enumerate the graph 17000 times over a total of 17000 + 16999 + ...+ 2 + 1 = 144,500,000 entities, no wonder its taking so long right? (see my article here: http://blog.staticvoid.co.nz/2012/5/7/entityframework_performance_and_autodetectchanges)

保存更改总是需要枚举跟踪图（它调用内部检测更改），所以你的第一个方法将是慢如它实际上将做相同数量的跟踪如上要求。

Save changes always needs to enumerate the tracking graph (it calls detect changes internally) so your first way is going to be slow as its actually going to be doing the same number of tracking calls as above.

第二种方式是更好的，但它仍然有一个非常重大的缺陷，我想象的双重的，首​​先把图是真正的大当你去保存更改（大图成倍高出追踪倍），其次它要占用大量的内存来坚持全图立刻尤其是考虑到EF存储两个副本的您的每一个实体。

The second way is much better but it still has a pretty major flaw which I imagine is twofold, firstly the graph is really big when you go to save changes (bigger graphs have exponentially higher tracking times), and secondly its going to take up a lot of memory to persist the whole graph at once especially given that EF stores two copies of each of your entities.

一个更好的办法是坚持你的块图。有些

A better way is to persist your graph in chunks. some

//With Auto detect changes off.
foreach(var batch in batches)//keep batch size below 1000 items, play around with the numbers a little
{
    using(var ctx = new MyContext())//make sure you create a new context per batch.
    {
        foreach(var entity in batch){
             ctx.Entities.Add(entity);
        }
        ctx.SaveChanges();
    }
}

我希望，你应该针对各地17-30s到。做所有17K行

I would expect you should target around 17-30s to do all 17k rows.

与原始SQL做这个命令你可以得到这个围绕12-20s;

by doing this with raw SQL commands you may be able to get this to around 12-20s;

与批量重新实现复制你很可能得到这个下降到2-5s

with a reimplementation with bulk copy you could probably get this down to 2-5s

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