在实体框架插入的最快的方法

问题描述

我在寻找插入实体框架的最快的途径，我问这是因为，你有一个积极的TransactionScope的场景和插入是巨大的（4000+）。它可能会持续10分钟以上（交易默认超时），这将导致未完成的事务。

I'm looking for the fastest way of inserting into Entity Framework, 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.

问候。

推荐答案

要你的话：

...... SavingChanges（每个
  记录）...

这是你能做的最糟糕的事情！调用的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（）一旦所有的记录之后。


• 呼叫的SaveChanges（）后，例如100条记录。


• 呼叫的SaveChanges（）后，例如100记录和处置上下文并创建一个新的。


• 禁用更改检测

• 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标性质，没有导航性能）到数据库。有了这个code它在不到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.

有关性能打电话是很重要的的SaveChanges（）后多的记录（一对多约100或1000）。它也提高了性能处置的SaveChanges后的背景下，并创建一个新的。这将清除所有的entites的背景下，的SaveChanges 不这样做，实体仍然附着状态的背景下不变。它是在什么减慢一步的插入步骤的上下文附加实体的尺寸越来越大。因此，它是有帮助的一段时间后，将其清除。

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.

下面是一些三围为我的560.000实体：

Here are a few measurements for my 560.000 entities:

• COMMITCOUNT = 1，recreateContext =假：多小时（这是你目前的程序）


• COMMITCOUNT = 100，recreateContext =假：超过20分钟


• COMMITCOUNT = 1000，recreateContext =假： 242秒


• COMMITCOUNT = 10000，recreateContext =假： 202秒


• COMMITCOUNT = 100000，recreateContext =假： 199秒


• COMMITCOUNT = 1000000，recreateContext =假：内存溢出异常


• COMMITCOUNT = 1，recreateContext =真：超过10分钟


• COMMITCOUNT = 10，recreateContext =真： 241秒


• COMMITCOUNT = 100，recreateContext =真： 164秒


• COMMITCOUNT = 1000，recreateContext =真： 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

在上面的第一测试的行为是表现很非线性的和极其随着时间的推移而减小。 （几个小时是一个估算的，我从来没有完成这个测试，我停在50.000实体20分钟后）。这种非线性行为是不是在所有其他测试，这样显著。

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.

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