在实体框架中插入的最快方式 [英] Fastest Way of Inserting in Entity Framework

问题描述

我正在寻找插入实体框架的最快方法.

I'm looking for the fastest way of inserting into Entity Framework.

我之所以问这个问题是因为您有一个活动的 TransactionScope 并且插入量很大(4000+).它可能会持续超过 10 分钟(事务的默认超时)，这将导致事务不完整.

I'm asking this because of the scenario where you have an active TransactionScope and the insertion is huge (4000+). It can potentially last more than 10 minutes (default timeout of transactions), and this will lead to an incomplete transaction.

推荐答案

对你的问题的评论中的评论:

To your remark in the comments to your question:

"...SavingChanges (对于每个记录)..."

"...SavingChanges (for each record)..."

这是你能做的最糟糕的事情！为每条记录调用 SaveChanges() 会大大降低批量插入的速度.我会做一些简单的测试，这很可能会提高性能:

That's the worst thing you can do! Calling SaveChanges() for each record slows bulk inserts extremely down. I would do a few simple tests which will very likely improve the performance:

• 在所有记录后调用一次SaveChanges().
• 在例如 100 条记录之后调用 SaveChanges().
• 在例如 100 条记录之后调用 SaveChanges() 并处理上下文并创建一个新的.
• 禁用更改检测

• Call SaveChanges() once after ALL records.
• Call SaveChanges() after for example 100 records.
• Call SaveChanges() after for example 100 records and dispose the context and create a new one.
• Disable change detection

对于批量插入，我正在尝试使用这样的模式:

For bulk inserts I am working and experimenting with a pattern like this:

using (TransactionScope scope = new TransactionScope())
{
    MyDbContext context = null;
    try
    {
        context = new MyDbContext();
        context.Configuration.AutoDetectChangesEnabled = false;

        int count = 0;            
        foreach (var entityToInsert in someCollectionOfEntitiesToInsert)
        {
            ++count;
            context = AddToContext(context, entityToInsert, count, 100, true);
        }

        context.SaveChanges();
    }
    finally
    {
        if (context != null)
            context.Dispose();
    }

    scope.Complete();
}

private MyDbContext AddToContext(MyDbContext context,
    Entity entity, int count, int commitCount, bool recreateContext)
{
    context.Set<Entity>().Add(entity);

    if (count % commitCount == 0)
    {
        context.SaveChanges();
        if (recreateContext)
        {
            context.Dispose();
            context = new MyDbContext();
            context.Configuration.AutoDetectChangesEnabled = false;
        }
    }

    return context;
}

我有一个测试程序，它将 560.000 个实体(9 个标量属性，无导航属性)插入数据库.使用此代码，不到 3 分钟即可运行.

I have a test program which inserts 560.000 entities (9 scalar properties, no navigation properties) into the DB. With this code it works in less than 3 minutes.

为了提高性能，在许多"记录(大约 100 或 1000 个左右的许多")之后调用 SaveChanges() 很重要.它还提高了在 SaveChanges 之后处理上下文并创建新上下文的性能.这会清除所有实体的上下文，SaveChanges 不会这样做，实体仍以 Unchanged 状态附加到上下文.上下文中附加实体的不断增长的大小逐渐减慢了插入速度.所以，过一段时间清除它会很有帮助.

For the performance it is important to call SaveChanges() after "many" records ("many" around 100 or 1000). It also improves the performance to dispose the context after SaveChanges and create a new one. This clears the context from all entites, SaveChanges doesn't do that, the entities are still attached to the context in state Unchanged. It is the growing size of attached entities in the context what slows down the insertion step by step. So, it is helpful to clear it after some time.

以下是我的 560000 个实体的一些测量结果:

Here are a few measurements for my 560000 entities:

• commitCount = 1, recreateContext = false:许多小时(这是您当前的程序)
• commitCount = 100，recreateContext = false:超过 20 分钟
• commitCount = 1000，recreateContext = false:242 秒
• commitCount = 10000，recreateContext = false:202 秒
• commitCount = 100000，recreateContext = false:199 秒
• commitCount = 1000000，recreateContext = false:内存不足异常
• commitCount = 1，recreateContext = true:超过 10 分钟
• commitCount = 10，recreateContext = true:241 秒
• commitCount = 100，recreateContext = true:164 秒
• commitCount = 1000，recreateContext = true:191 秒

• commitCount = 1, recreateContext = false: many hours (That's your current procedure)
• commitCount = 100, recreateContext = false: more than 20 minutes
• commitCount = 1000, recreateContext = false: 242 sec
• commitCount = 10000, recreateContext = false: 202 sec
• commitCount = 100000, recreateContext = false: 199 sec
• commitCount = 1000000, recreateContext = false: out of memory exception
• commitCount = 1, recreateContext = true: more than 10 minutes
• commitCount = 10, recreateContext = true: 241 sec
• commitCount = 100, recreateContext = true: 164 sec
• commitCount = 1000, recreateContext = true: 191 sec

上面第一个测试中的行为是性能非常非线性，并且随着时间的推移急剧下降.(许多小时"是一个估计值，我从未完成过这个测试，我在 20 分钟后停在 50.000 个实体.)这种非线性行为在所有其他测试中并不那么重要.

The behaviour in the first test above is that the performance is very non-linear and decreases extremely over time. ("Many hours" is an estimation, I never finished this test, I stopped at 50.000 entities after 20 minutes.) This non-linear behaviour is not so significant in all other tests.

这篇关于在实体框架中插入的最快方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！