创建 1000 个实体框架对象时，我应该何时调用 SaveChanges()?(例如在导入期间) [英] When should I call SaveChanges() when creating 1000's of Entity Framework objects? (like during an import)

问题描述

我正在运行一个导入，每次运行都会有 1000 条记录.只是想确认一下我的假设:

I am running an import that will have 1000's of records on each run. Just looking for some confirmation on my assumptions:

以下哪个最有意义:

1. 在每次 AddToClassName() 调用时运行 SaveChanges().
2. 每 n 次 AddToClassName() 调用运行 SaveChanges().
3. 在 AddToClassName() 调用的所有之后运行 SaveChanges().

1. Run SaveChanges() every AddToClassName() call.
2. Run SaveChanges() every n number of AddToClassName() calls.
3. Run SaveChanges() after all of the AddToClassName() calls.

第一个选项可能很慢吧?由于需要分析内存中的EF对象，生成SQL等

The first option is probably slow right? Since it will need to analyze the EF objects in memory, generate SQL, etc.

我认为第二个选项是两全其美的，因为我们可以在 SaveChanges() 调用周围包裹一个 try catch，并且只丢失 n 个一次记录，如果其中一个失败.可能将每个批次存储在 List<> 中.如果 SaveChanges() 调用成功，则删除列表.如果失败，请记录这些项目.

I assume that the second option is the best of both worlds, since we can wrap a try catch around that SaveChanges() call, and only lose n number of records at a time, if one of them fails. Maybe store each batch in an List<>. If the SaveChanges() call succeeds, get rid of the list. If it fails, log the items.

最后一个选项可能最终也很慢，因为在调用 SaveChanges() 之前，每个 EF 对象都必须在内存中.如果保存失败，则不会提交任何内容，对吗?

The last option would probably end up being very slow as well, since every single EF object would have to be in memory until SaveChanges() is called. And if the save failed nothing would be committed, right?

推荐答案

我会先测试一下以确认.性能不一定那么差.

I would test it first to be sure. Performance doesn't have to be that bad.

如果您需要在一个事务中输入所有行，请在所有 AddToClassName 类之后调用它.如果可以独立输入行，则在每一行之后保存更改.数据库一致性很重要.

If you need to enter all rows in one transaction, call it after all of AddToClassName class. If rows can be entered independently, save changes after every row. Database consistence is important.

我不喜欢第二个选项.如果我对系统进行了导入并且会拒绝 1000 行中的 10 行，这会让我感到困惑(从最终用户的角度来看)，仅仅因为 1 行不好.您可以尝试导入10个，如果失败，则一个一个尝试，然后登录.

Second option I don't like. It would be confusing for me (from final user perspective) if I made import to system and it would decline 10 rows out of 1000, just because 1 is bad. You can try to import 10 and if it fails, try one by one and then log.

测试是否需要很长时间.不要写'propably'.你还不知道.只有当它确实是一个问题时，才考虑其他解决方案(marc_s).

Test if it takes long time. Don't write 'propably'. You don't know it yet. Only when it is actually a problem, think about other solution (marc_s).

编辑

我做了一些测试(时间以毫秒为单位):

I've done some tests (time in miliseconds):

10000 行:

10000 rows:

1 行后的 SaveChanges():18510,534
100 行后的 SaveChanges():4350,3075
10000 行后的 SaveChanges():5233,0635

SaveChanges() after 1 row:18510,534
SaveChanges() after 100 rows:4350,3075
SaveChanges() after 10000 rows:5233,0635

50000 行:

SaveChanges() 后 1 行:78496,929
SaveChanges() 500 行后:22302,2835
50000 行后的 SaveChanges():24022,8765

SaveChanges() after 1 row:78496,929
SaveChanges() after 500 rows:22302,2835
SaveChanges() after 50000 rows:24022,8765

所以实际上在 n 行之后提交比在所有之后提交要快.

我的建议是:

• 在 n 行之后 SaveChanges().
• 如果一次提交失败，请一一尝试查找错误行.

测试类:

表格:

CREATE TABLE [dbo].[TestTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SomeInt] [int] NOT NULL,
    [SomeVarchar] [varchar](100) NOT NULL,
    [SomeOtherVarchar] [varchar](50) NOT NULL,
    [SomeOtherInt] [int] NULL,
 CONSTRAINT [PkTestTable] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

类:

public class TestController : Controller
{
    //
    // GET: /Test/
    private readonly Random _rng = new Random();
    private const string _chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";

    private string RandomString(int size)
    {
        var randomSize = _rng.Next(size);

        char[] buffer = new char[randomSize];

        for (int i = 0; i < randomSize; i++)
        {
            buffer[i] = _chars[_rng.Next(_chars.Length)];
        }
        return new string(buffer);
    }


    public ActionResult EFPerformance()
    {
        string result = "";

        TruncateTable();
        result = result + "SaveChanges() after 1 row:" + EFPerformanceTest(10000, 1).TotalMilliseconds + "<br/>";
        TruncateTable();
        result = result + "SaveChanges() after 100 rows:" + EFPerformanceTest(10000, 100).TotalMilliseconds + "<br/>";
        TruncateTable();
        result = result + "SaveChanges() after 10000 rows:" + EFPerformanceTest(10000, 10000).TotalMilliseconds + "<br/>";
        TruncateTable();
        result = result + "SaveChanges() after 1 row:" + EFPerformanceTest(50000, 1).TotalMilliseconds + "<br/>";
        TruncateTable();
        result = result + "SaveChanges() after 500 rows:" + EFPerformanceTest(50000, 500).TotalMilliseconds + "<br/>";
        TruncateTable();
        result = result + "SaveChanges() after 50000 rows:" + EFPerformanceTest(50000, 50000).TotalMilliseconds + "<br/>";
        TruncateTable();

        return Content(result);
    }

    private void TruncateTable()
    {
        using (var context = new CamelTrapEntities())
        {
            var connection = ((EntityConnection)context.Connection).StoreConnection;
            connection.Open();
            var command = connection.CreateCommand();
            command.CommandText = @"TRUNCATE TABLE TestTable";
            command.ExecuteNonQuery();
        }
    }

    private TimeSpan EFPerformanceTest(int noOfRows, int commitAfterRows)
    {
        var startDate = DateTime.Now;

        using (var context = new CamelTrapEntities())
        {
            for (int i = 1; i <= noOfRows; ++i)
            {
                var testItem = new TestTable();
                testItem.SomeVarchar = RandomString(100);
                testItem.SomeOtherVarchar = RandomString(50);
                testItem.SomeInt = _rng.Next(10000);
                testItem.SomeOtherInt = _rng.Next(200000);
                context.AddToTestTable(testItem);

                if (i % commitAfterRows == 0) context.SaveChanges();
            }
        }

        var endDate = DateTime.Now;

        return endDate.Subtract(startDate);
    }
}

这篇关于创建 1000 个实体框架对象时，我应该何时调用 SaveChanges()?(例如在导入期间)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！