实体框架6 DbSet AddRange与IDbSet Add-AddRange如何这么快? [英] Entity Framework 6 DbSet AddRange vs IDbSet Add - How Can AddRange be so much faster?

问题描述

我在家用计算机上玩弄Entity Framework 6，并决定尝试插入大约430k的大量行.

I was playing around with Entity Framework 6 on my home computer and decided to try out inserting a fairly large amount of rows, around 430k.

我的第一次尝试是这样的，是的，我知道它可以做得更好，但是无论如何这都是为了研究:

My first try looked like this, yes I know it can be better but it was for research anyway:

var watch = System.Diagnostics.Stopwatch.StartNew();
foreach (var event in group)
{
    db.Events.Add(event);
    db.SaveChanges();
}

var dbCount = db.Events.Count(x => x.ImportInformation.FileName == group.Key);

if (dbCount != group.Count())
{
    throw new Exception("Mismatch between rows added for file and current number of rows!");
}

watch.Stop();
Console.WriteLine($"Added {dbCount} events to database in {watch.Elapsed.ToString()}");

从晚上开始，下班回家后再回来看看.结果就是这样:

Started it in the evening and checked back when I got home from work. This was the result:

如您所见，在最初的4小时41分钟内添加了64523个事件，但随后变得非常慢，接下来的66985事件花费了14小时51分钟.我检查了数据库，该程序仍在插入事件，但速度极慢.然后，我决定尝试使用新" AddRange DbSet的方法.

As you can see 64523 events were added in the first 4 hours and 41 minutes but then it got a lot slower and the next 66985 events took 14 hours and 51 minutes. I checked the database and the program was still inserting events but at an extremely low speed. I then decided to try the "new" AddRange method for DbSet.

我将模型从IDbSet切换为DbSet，并用以下代码替换了foreach循环:

I switched my models from IDbSet to DbSet and replaced the foreach loop with this:

db.Events.AddRange(group);
db.SaveChanges();

我现在可以在30秒钟左右添加6万多个事件.它可能不是 SqlBulkCopy 很快，但确实仍然有很大的进步.要实现这一目标，到底发生了什么?我以为我明天要检查SQL Server Profiler的查询，但是也可以解释一下代码中发生的事情.

I could now add 60k+ events in around 30 seconds. It is perhaps not SqlBulkCopy fast but it is still a huge improvement. What is happening under the hood to achieve this? I thought I was gonna check SQL Server Profiler tomorrow for queries but It would be nice with an explanation what happens in code as well.

推荐答案

Jakub回答说，在每个添加的实体都没有帮助之后调用SaveChanges.但是，即使将其移出，您仍然会遇到一些性能问题.那不能解决由Add方法引起的性能问题.

As Jakub answered, calling SaveChanges after every added entity was not helping. But you would still get some performance problems even if you move it out. That will not fix the performance issue caused by the Add method.

使用Add方法添加多个实体是一个非常常见的错误.实际上，是 INSANELY 缓慢的DetectChanges方法.

That's a very common error to use the Add method to add multiple entities. In fact, it's the DetectChanges method that's INSANELY slow.

• 添加每条记录后，添加方法DetectChanges.
• 添加所有记录后，AddRange方法DetectChanges.

请参阅:实体框架-性能添加

也许不是SqlBulkCopy快速，但是它仍然是一个巨大的改进

It is perhaps not SqlBulkCopy fast, but it is still a huge improvement

有可能使性能非常接近SqlBulkCopy.

It's possible to get performance VERY close to SqlBulkCopy.

免责声明:我是项目实体框架扩展的所有者

Disclaimer: I'm the owner of the project Entity Framework Extensions

(该库不是免费的)

该库允许您一次保存多个实体，从而使您的代码更高效.支持所有批量操作:

This library can make your code more efficient by allowing you to save multiples entities at once. All bulk operations are supported:

• BulkSaveChanges
• BulkInsert
• 批量更新
• 批量删除
• BulkMerge
• 批量同步

示例:

// Easy to use
context.BulkSaveChanges();

// Easy to customize
context.BulkSaveChanges(bulk => bulk.BatchSize = 100);

// Perform Bulk Operations
context.BulkDelete(customers);
context.BulkInsert(customers);
context.BulkUpdate(customers);

// Customize Primary Key
context.BulkMerge(customers, operation => {
   operation.ColumnPrimaryKeyExpression = 
        customer => customer.Code;
});

这篇关于实体框架6 DbSet AddRange与IDbSet Add-AddRange如何这么快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！