实体框架批量插入虚幻缓慢 [英] Entity Framework bulk insert unreal slow

问题描述

我正在使用EF6.我试图插入大约200.000个实体，同时每100个实体后将更改保存到数据库中.

I am using EF 6. I am trying to insert around 200.000 entities while saving changes to database after each 100 entities.

问题在于保存50.000个实体花了11个小时，但它仍然落后.我正在使用WebJobs运行此程序，并且作业与主要网站发布在同一天蓝色的Webapp上.是不是因为这个问题而WebJob没有足够的资源，或者没有保存100个实体，还是这种方法?

The issue is it took 11 hours to save 50.000 entities, and it is still running behind. I am running this with WebJobs, and job is published on the same azure webapp as main website. Is the issue because of that and WebJob don't have enough resources, or saving after 100 entities, or the approach?

方法

public void SaveLeadsForBuyer(ISenderModel model)
{
    var rowCounter = 0;

    foreach (var deliveryRecord in model.Customers.Select(customerModel => new DeliveryRecord()
    {
        BuyerId = model.Buyer.Id,
        AspNetUserId = customerModel.Id,
        DeliveryType = model.Buyer.DeliveryType,
        CreatedOn = DateTime.UtcNow
    }))
    {
        ++rowCounter;

        _unit.Repository<DeliveryRecord>().Insert(deliveryRecord);

        _unit.SaveChangesPartially(rowCounter, 100);
    }

    _unit.SaveChanges();
}

助手

public static class UnitOfWorkHelper
{
    /// <summary>
    /// Helper method triggers SaveChanges() after amount of rows provided through "amount" parameter in method
    /// </summary>
    /// <param name="unit">UnitOfWork object</param>
    /// <param name="count">Current amount of rows</param>
    /// <param name="saveCount">Amount when to save changes to database</param>
    public static void SaveChangesPartially(this IUnitOfWorkAsync unit, int count, int saveCount)
    {
        if (count % saveCount == 0)
        {
            unit.SaveChanges();
        }
    }
}

推荐答案

之所以很慢，是因为Entity Framework为每个记录执行一次数据库往返.因此，如果您保存200,000个实体，则将执行 200,000个数据库往返，这对于保存多个实体来说是最佳选择.

It's slow because Entity Framework performs a database round trip for every record. So, if you save 200,000 entities then 200,000 database round-trips will be performed which is far to be optimal to save multiple entities.

对于这种情况，您需要实现自己或使用支持BulkInsert的库(通常在后台执行SqlBulkCopy)

For this kind of scenario, you need to implement yourself or use a library supporting BulkInsert (which normally execute a SqlBulkCopy under the hood)

有3个主库(2个免费，1个PRO)允许批量插入

There is 3 main library (2 FREE, 1 PRO) which allow Bulk Insert

// Example from Entity Framework Extensions Library
using (var ctx = new EntitiesContext())
{
    ctx.BulkInsert(list);
}

您可以阅读以下文章以了解PROS&每个库的CONS:实体框架-批量插入库评论&比较

You can read the following article to understand PROS & CONS for every library: Entity Framework - Bulk Insert Library Reviews & Comparisons

实体框架扩展是迄今为止提供最大灵活性(批量插入，更新，删除)的库，Merge和BulkSaveChanges并支持所有内容)，但是它是PRO版本.如果您要寻找免费版本，建议您使用EntityFramework.BulkInsert，但现在不再支持它，并且不支持所有关联和继承.

Entity Framework Extensions is the library which offers by far the most flexibility (Bulk Insert, Update, Delete, Merge and BulkSaveChanges and supports everything) however it is a PRO version. If you are looking for a free version, I recommend using EntityFramework.BulkInsert, however, it’s not supported anymore and doesn't support all associations and inheritances.

免责声明:我是该项目的所有者实体框架扩展

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

编辑:回答评论问题

我要保存每100条记录，而不是每条记录

I am saving each 100 records, not each record

向单位上下文中添加一个实体还是100个实体都没有关系，实体框架将它们一个一个地保存(每条记录一个插入语句).只需将SQL Profiler与SQL Server数据库一起使用，您就会明白我的意思.

It doesn't matter if you add one entity or 100 entities to your unit context, Entity Framework saves them one by one (A single insert statement for every record). Just use SQL Profiler with a SQL Server database and you will see what I mean.

编辑:回答评论问题

伟大的乔纳森.有什么办法可以使用ef6泛型来实现 ow?

great jonathan. is there any way to implement this with ef6 generic uow?

答案取决于您选择使用哪个库.

The answer depends on which library you choose to use.

如果使用我的库，则可以创建BulkSaveChanges方法或在UnitOfWork中将所有"_context.SaveChanges()"更改为"_context.BulkSaveChanges()"

If you use my library, you can create the BulkSaveChanges method or change in your UnitOfWork all "_context.SaveChanges()" by "_context.BulkSaveChanges()"

public void SaveLeadsForBuyer(ISenderModel model)
{
    // ... code ...
    // _unit.SaveChanges();
    _unit.BulkSaveChanges();
}

如果要获得最佳性能并从我的库或FREE库中实现批量插入，我可能会添加一个名为BulkInsert的方法或扩展方法(如果无法更改存储库类)

If you want the best performance and Implement Bulk Insert from my library or a FREE library, I would probably add a method or an extension method (if you cannot change the repository class) named BulkInsert

public class Repository<TEntity> : IRepository<TEntity> where TEntity : class
{
    // ... code ...

    public virtual void BulkInsert(List<TEntity> list)
    {
        _context.BulkInsert(list);
    }
}

请紧记BulkInsert直接插入实体而不必调用"SaveChanges"，它不使用上下文/更改跟踪器来获得最佳性能.

Keep in mind BulkInsert directly insert entities without having to call "SaveChanges", it doesn't use the context/change tracker to get the optimal performance.

这篇关于实体框架批量插入虚幻缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！