我如何加快DbSet.Add（）？ [英] How do I speed up DbSet.Add()?

问题描述

我要进口大约30K行从CSV文件到我的SQL数据库，这种可悲的是需要20分钟。

I have to import about 30k rows from a CSV file to my SQL database, this sadly takes 20 minutes.

通过探查故障排除表明我的 ？DbSet.Add是最耗时的，但为什么

Troubleshooting with a profiler shows me that DbSet.Add is taking the most time, but why?

我有这些实体框架代码优先类：

I have these Entity Framework Code-First classes:

public class Article
{
    // About 20 properties, each property doesn't store excessive amounts of data
}

public class Database : DbContext
{
    public DbSet<Article> Articles { get; set; }
}

有关for循环我做我的每个项目：

For each item in my for loop I do:

db.Articles.Add(article);

外循环我做的：

Outside the for loop I do:

db.SaveChanges();

这是一个与我的本地的SQLExpress服务器相连，但我想没有写，直到调用SaveChanges任何正在所谓所以我想服务器将不会是问题....

It's connected with my local SQLExpress server, but I guess there isn't anything written till SaveChanges is being called so I guess the server won't be the problem....

推荐答案

在一个单位的工作每个项目有开销，因为它必须检查（和更新）的身份管理器，添加到各种收藏品等。

Each item in a unit-of-work has overhead, as it must check (and update) the identity manager, add to various collections, etc.

我会尝试的配料成，说出的第一句话，组500（改变这个数字，以适应），用新鲜的（新）对象的情况下每次开机 - 否则您可以合理预期伸缩的性能。分解成批次也可以防止巨石交易带来的一切停止

The first thing I would try is batching into, say, groups of 500 (change that number to suit), starting with a fresh (new) object-context each time - as otherwise you can reasonably expect telescoping performance. Breaking it into batches also prevents a megalithic transaction bringing everything to a stop.

除此之外。 SqlBulkCopy的。它是专为大量进口以最小的开销。它不是，虽然EF

Beyond that; SqlBulkCopy. It is designed for large imports with minimal overhead. It isn't EF though.

这篇关于我如何加快DbSet.Add（）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！