问题描述

我在家用计算机上使用 Entity Framework 6 并决定尝试插入相当多的行，大约 430k.

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

var watch = System.Diagnostics.Stopwatch.StartNew();foreach(组中的 var 事件){db.Events.Add(事件);db.SaveChanges();}var dbCount = db.Events.Count(x => x.ImportInformation.FileName == group.Key);if (dbCount != group.Count()){throw new Exception("为文件添加的行与当前行数不匹配！");}watch.Stop();Console.WriteLine($"在 {watch.Elapsed.ToString()} 中将 {dbCount} 事件添加到数据库");

晚上开始，下班回家后检查.结果是这样的:

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

我现在可以在大约 30 秒内添加 60k+ 事件.它可能不是 SqlBulkCopy 快，但它是仍然是一个巨大的进步.为了实现这一目标，幕后发生了什么?我以为我明天要检查 SQL Server Profiler 以进行查询，但是如果能解释一下代码中发生的事情也会很好.

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

添加与添加范围

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

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

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

可能没有 SqlBulkCopy 快，但它仍然是一个巨大的改进

可以获得非常接近 SqlBulkCopy 的性能.

免责声明:我是项目的所有者Entity Framework Extensions

(这个库不是免费的)

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

• 批量保存更改
• 批量插入
• 批量更新
• 批量删除
• 批量合并
• 批量同步

例子:

//易于使用context.BulkSaveChanges();//易于定制context.BulkSaveChanges(bulk => bulk.BatchSize = 100);//执行批量操作context.BulkDelete(客户);context.BulkInsert(客户);context.BulkUpdate(客户);//自定义主键context.BulkMerge(客户，操作=> {operation.ColumnPrimaryKeyExpression =客户=>客户代码；});

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:

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.

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

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

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.

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 vs AddRange

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.

• The Add method DetectChanges after every record added.
• The AddRange method DetectChanges after all records are added.

See: Entity Framework - Performance Add

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

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

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

(This library is NOT free)

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

• BulkSaveChanges
• BulkInsert
• BulkUpdate
• BulkDelete
• BulkMerge
• BulkSynchronize

Example:

// 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;
});

这篇关于Entity Framework 6 DbSet AddRange vs IDbSet Add - AddRange 怎么能这么快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！