提高实体框架中的批量插入性能

Question

我想通过实体框架在表中插入20000条记录，大约需要2分钟。除了使用SP之外还有什么方法可以提高其性能吗？这是我的代码：

 foreach (Employees item in sequence)
 {
   t = new Employees ();
   t.Text = item.Text;
   dataContext.Employees.AddObject(t);                  
 }
 dataContext.SaveChanges();

Answer 1

有机会进行多项改进（如果您使用DbContext）：

设置：

yourContext.Configuration.AutoDetectChangesEnabled = false;
yourContext.Configuration.ValidateOnSaveEnabled = false;

在 100 个插入的包中执行SaveChanges()。 .. 或者您可以尝试使用 1000 个项目的包并查看性能的变化。

由于在所有这些插入过程中，上下文都是相同的并且它会变得越来越大，因此您可以每 1000 次插入重建上下文对象。 var yourContext = new YourContext(); I觉得这就是最大的收获。

在我的导入数据过程中进行此改进，将时间从 7 分钟缩短到 6 秒。

实际数字...在您的情况下不可能是 100 或 1000...尝试并调整它。

Answer 2

这样做是没有办法强制 EF 提高性能的。问题在于 EF 在数据库的单独往返中执行每个插入。太棒了不是吗？甚至数据集也支持批处理。请查看本文以获取一些解决方法。另一种解决方法是使用接受表值参数的自定义存储过程，但您需要原始 ADO.NET。

Answer 3

使用下面的代码，您可以使用一个方法来扩展部分上下文类，该方法将获取实体对象的集合并将它们批量复制到数据库。只需将 MyEntities 中的类名称替换为实体类的名称，然后将其添加到项目中正确的命名空间中即可。之后，您需要做的就是调用 BulkInsertAll 方法来移交要插入的实体对象。不要重用上下文类，而是在每次使用它时创建一个新实例。这是必需的，至少在某些版本的 EF 中是必需的，因为与此处使用的 SQLConnection 关联的身份验证数据在使用该类一次后就会丢失。我不知道为什么。

此版本适用于 EF 5

public partial class MyEntities
{
    public void BulkInsertAll<T>(T[] entities) where T : class
    {
        var conn = (SqlConnection)Database.Connection;

        conn.Open();

        Type t = typeof(T);
        Set(t).ToString();
        var objectContext = ((IObjectContextAdapter)this).ObjectContext;
        var workspace = objectContext.MetadataWorkspace;
        var mappings = GetMappings(workspace, objectContext.DefaultContainerName, typeof(T).Name);

        var tableName = GetTableName<T>();
        var bulkCopy = new SqlBulkCopy(conn) { DestinationTableName = tableName };

        // Foreign key relations show up as virtual declared 
        // properties and we want to ignore these.
        var properties = t.GetProperties().Where(p => !p.GetGetMethod().IsVirtual).ToArray();
        var table = new DataTable();
        foreach (var property in properties)
        {
            Type propertyType = property.PropertyType;

            // Nullable properties need special treatment.
            if (propertyType.IsGenericType &&
                propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
            {
                propertyType = Nullable.GetUnderlyingType(propertyType);
            }

            // Since we cannot trust the CLR type properties to be in the same order as
            // the table columns we use the SqlBulkCopy column mappings.
            table.Columns.Add(new DataColumn(property.Name, propertyType));
            var clrPropertyName = property.Name;
            var tableColumnName = mappings[property.Name];
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(clrPropertyName, tableColumnName));
        }

        // Add all our entities to our data table
        foreach (var entity in entities)
        {
            var e = entity;
            table.Rows.Add(properties.Select(property => GetPropertyValue(property.GetValue(e, null))).ToArray());
        }

        // send it to the server for bulk execution
        bulkCopy.BulkCopyTimeout = 5 * 60;
        bulkCopy.WriteToServer(table);

        conn.Close();
    }

    private string GetTableName<T>() where T : class
    {
        var dbSet = Set<T>();
        var sql = dbSet.ToString();
        var regex = new Regex(@"FROM (?<table>.*) AS");
        var match = regex.Match(sql);
        return match.Groups["table"].Value;
    }

    private object GetPropertyValue(object o)
    {
        if (o == null)
            return DBNull.Value;
        return o;
    }

    private Dictionary<string, string> GetMappings(MetadataWorkspace workspace, string containerName, string entityName)
    {
        var mappings = new Dictionary<string, string>();
        var storageMapping = workspace.GetItem<GlobalItem>(containerName, DataSpace.CSSpace);
        dynamic entitySetMaps = storageMapping.GetType().InvokeMember(
            "EntitySetMaps",
            BindingFlags.GetProperty | BindingFlags.NonPublic | BindingFlags.Instance,
            null, storageMapping, null);

        foreach (var entitySetMap in entitySetMaps)
        {
            var typeMappings = GetArrayList("TypeMappings", entitySetMap);
            dynamic typeMapping = typeMappings[0];
            dynamic types = GetArrayList("Types", typeMapping);

            if (types[0].Name == entityName)
            {
                var fragments = GetArrayList("MappingFragments", typeMapping);
                var fragment = fragments[0];
                var properties = GetArrayList("AllProperties", fragment);
                foreach (var property in properties)
                {
                    var edmProperty = GetProperty("EdmProperty", property);
                    var columnProperty = GetProperty("ColumnProperty", property);
                    mappings.Add(edmProperty.Name, columnProperty.Name);
                }
            }
        }

        return mappings;
    }

    private ArrayList GetArrayList(string property, object instance)
    {
        var type = instance.GetType();
        var objects = (IEnumerable)type.InvokeMember(property, BindingFlags.GetProperty | BindingFlags.NonPublic | BindingFlags.Instance, null, instance, null);
        var list = new ArrayList();
        foreach (var o in objects)
        {
            list.Add(o);
        }
        return list;
    }

    private dynamic GetProperty(string property, object instance)
    {
        var type = instance.GetType();
        return type.InvokeMember(property, BindingFlags.GetProperty | BindingFlags.NonPublic | BindingFlags.Instance, null, instance, null);
    }
}

此版本适用于 EF 6

public partial class CMLocalEntities
{
    public void BulkInsertAll<T>(T[] entities) where T : class
    {
        var conn = (SqlConnection)Database.Connection;

        conn.Open();

        Type t = typeof(T);
        Set(t).ToString();
        var objectContext = ((IObjectContextAdapter)this).ObjectContext;
        var workspace = objectContext.MetadataWorkspace;
        var mappings = GetMappings(workspace, objectContext.DefaultContainerName, typeof(T).Name);

        var tableName = GetTableName<T>();
        var bulkCopy = new SqlBulkCopy(conn) { DestinationTableName = tableName };

        // Foreign key relations show up as virtual declared 
        // properties and we want to ignore these.
        var properties = t.GetProperties().Where(p => !p.GetGetMethod().IsVirtual).ToArray();
        var table = new DataTable();
        foreach (var property in properties)
        {
            Type propertyType = property.PropertyType;

            // Nullable properties need special treatment.
            if (propertyType.IsGenericType &&
                propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
            {
                propertyType = Nullable.GetUnderlyingType(propertyType);
            }

            // Since we cannot trust the CLR type properties to be in the same order as
            // the table columns we use the SqlBulkCopy column mappings.
            table.Columns.Add(new DataColumn(property.Name, propertyType));
            var clrPropertyName = property.Name;
            var tableColumnName = mappings[property.Name];
            bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(clrPropertyName, tableColumnName));
        }

        // Add all our entities to our data table
        foreach (var entity in entities)
        {
            var e = entity;
            table.Rows.Add(properties.Select(property => GetPropertyValue(property.GetValue(e, null))).ToArray());
        }

        // send it to the server for bulk execution
        bulkCopy.BulkCopyTimeout = 5*60;
        bulkCopy.WriteToServer(table);

        conn.Close();
    }

    private string GetTableName<T>() where T : class
    {
        var dbSet = Set<T>();
        var sql = dbSet.ToString();
        var regex = new Regex(@"FROM (?<table>.*) AS");
        var match = regex.Match(sql);
        return match.Groups["table"].Value;
    }

    private object GetPropertyValue(object o)
    {
        if (o == null)
            return DBNull.Value;
        return o;
    }

    private Dictionary<string, string> GetMappings(MetadataWorkspace workspace, string containerName, string entityName)
    {
        var mappings = new Dictionary<string, string>();
        var storageMapping = workspace.GetItem<GlobalItem>(containerName, DataSpace.CSSpace);
        dynamic entitySetMaps = storageMapping.GetType().InvokeMember(
            "EntitySetMaps",
            BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance,
            null, storageMapping, null);

        foreach (var entitySetMap in entitySetMaps)
        {
            var typeMappings = GetArrayList("EntityTypeMappings", entitySetMap);
            dynamic typeMapping = typeMappings[0];
            dynamic types = GetArrayList("Types", typeMapping);

            if (types[0].Name == entityName)
            {
                var fragments = GetArrayList("MappingFragments", typeMapping);
                var fragment = fragments[0];
                var properties = GetArrayList("AllProperties", fragment);
                foreach (var property in properties)
                {
                    var edmProperty = GetProperty("EdmProperty", property);
                    var columnProperty = GetProperty("ColumnProperty", property);
                    mappings.Add(edmProperty.Name, columnProperty.Name);
                }
            }
        }

        return mappings;
    }

    private ArrayList GetArrayList(string property, object instance)
    {
        var type = instance.GetType();
        var objects = (IEnumerable)type.InvokeMember(
            property, 
            BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance, null, instance, null);
        var list = new ArrayList();
        foreach (var o in objects)
        {
            list.Add(o);
        }
        return list;
    }

    private dynamic GetProperty(string property, object instance)
    {
        var type = instance.GetType();
        return type.InvokeMember(property, BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance, null, instance, null);
    }

}

最后，为 Linq-To-Sql 爱好者提供一些东西。

partial class MyDataContext
{
    partial void OnCreated()
    {
        CommandTimeout = 5 * 60;
    }

    public void BulkInsertAll<T>(IEnumerable<T> entities)
    {
        entities = entities.ToArray();

        string cs = Connection.ConnectionString;
        var conn = new SqlConnection(cs);
        conn.Open();

        Type t = typeof(T);

        var tableAttribute = (TableAttribute)t.GetCustomAttributes(
            typeof(TableAttribute), false).Single();
        var bulkCopy = new SqlBulkCopy(conn) { 
            DestinationTableName = tableAttribute.Name };

        var properties = t.GetProperties().Where(EventTypeFilter).ToArray();
        var table = new DataTable();

        foreach (var property in properties)
        {
            Type propertyType = property.PropertyType;
            if (propertyType.IsGenericType &&
                propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
            {
                propertyType = Nullable.GetUnderlyingType(propertyType);
            }

            table.Columns.Add(new DataColumn(property.Name, propertyType));
        }

        foreach (var entity in entities)
        {
            table.Rows.Add(properties.Select(
              property => GetPropertyValue(
              property.GetValue(entity, null))).ToArray());
        }

        bulkCopy.WriteToServer(table);
        conn.Close();
    }

    private bool EventTypeFilter(System.Reflection.PropertyInfo p)
    {
        var attribute = Attribute.GetCustomAttribute(p, 
            typeof (AssociationAttribute)) as AssociationAttribute;

        if (attribute == null) return true;
        if (attribute.IsForeignKey == false) return true; 

        return false;
    }

    private object GetPropertyValue(object o)
    {
        if (o == null)
            return DBNull.Value;
        return o;
    }
}

Answer 4

也许这里的答案会对您有所帮助。似乎您想定期处理上下文。这是因为随着附加实体的增长，上下文变得越来越大。

Answer 5

您的代码存在两个主要性能问题：

• 使用 Add 方法
• 使用 SaveChanges

使用 Add 方法

在添加每个实体时，Add 方法只会变得越来越慢。

：http://entityframework.net/improve-ef-add-performance

请参阅 例如，通过以下方式添加 10,000 个实体：

• Add (take ~105,000ms)
• AddRange (take ~120ms)

注意：实体尚未保存在数据库中！

问题在于，Add 方法尝试在添加的每个实体上检测更改，而 AddRange 在将所有实体添加到上下文后执行一次。

常见的解决方案有：

• 使用 AddRange 而不是将
• SET AutoDetectChanges 添加到 false
• SPLIT SaveChanges 分批

使用 SaveChanges

尚未为批量操作创建实体框架。对于您保存的每个实体，都会执行数据库往返。

因此，如果您想插入 20,000 条记录，您将执行 20,000 次数据库往返，这是疯狂！

有一些支持批量插入的第三方库：

• Z.EntityFramework.Extensions（推荐）
• EFUtilities
• EntityFramework.BulkInsert

请参阅：实体框架批量插入库

选择批量插入库时要小心。只有实体框架扩展支持所有类型的关联和继承，并且它是唯一仍然受支持的。

---

免责声明：我是 实体框架扩展

该库允许您执行场景所需的所有批量操作：

• 批量保存更改、
• 批量插入、
• 批量删除
• 、批量更新、
• 批量合并

示例

// Easy to use
context.BulkSaveChanges();

// Easy to customize
context.BulkSaveChanges(bulk => bulk.BatchSize = 100);

// Perform Bulk Operations
context.BulkDelete(customers);
context.BulkInsert(customers);
context.BulkUpdate(customers);

// Customize Primary Key
context.BulkMerge(customers, operation => {
   operation.ColumnPrimaryKeyExpression = 
        customer => customer.Code;
});

编辑：回答评论中的问题

您创建的库的每个批量插入是否有建议的最大大小

不要太高，也不要太低。没有适合所有场景的特定值，因为它取决于行大小、索引、触发器等多种因素。

通常建议在 4000 左右。

还有一种方法可以将所有内容整合到一个事务中，而不用担心超时

您可以使用实体框架事务。如果交易启动，我们的图书馆就会使用该交易。但要小心，花费太多时间的事务也会带来诸如某些行/索引/表锁之类的问题。

Answer 6

目前没有更好的方法，但是通过将 SaveChanges 移动到 for 循环内大约 10 个项目可能会有所改善。

int i = 0;

foreach (Employees item in sequence)
{
   t = new Employees ();
   t.Text = item.Text;
   dataContext.Employees.AddObject(t);   

   // this will add max 10 items together
   if((i % 10) == 0){
       dataContext.SaveChanges();
       // show some progress to user based on
       // value of i
   }
   i++;
}
dataContext.SaveChanges();

您可以调整 10 以更接近更好的性能。它不会极大地提高速度，但它可以让您向用户展示一些进度并使其更加用户友好。

Answer 7

在具有 1 个实例的基本网站的 Azure 环境中。我尝试使用 for 循环从 25000 条记录中一次插入一批 1000 条记录，花费了 11.5 分钟，但在并行执行中花费了不到一分钟。所以我建议使用 TPL （任务并行库）。

         var count = (you collection / 1000) + 1;
         Parallel.For(0, count, x =>
        {
            ApplicationDbContext db1 = new ApplicationDbContext();
            db1.Configuration.AutoDetectChangesEnabled = false;

            var records = members.Skip(x * 1000).Take(1000).ToList();
            db1.Members.AddRange(records).AsParallel();

            db1.SaveChanges();
            db1.Dispose();
        });

Answer 8

更好的方法是完全跳过实体框架来执行此操作并依赖 SqlBulkCopy 类。其他操作可以像以前一样继续使用EF。

这会增加解决方案的维护成本，但无论如何，与使用 EF 相比，有助于将大型对象集合插入数据库所需的时间减少一到两个数量级。

以下是一篇文章，针对具有父子关系的对象，对 SqlBulkCopy 类与 EF 进行了比较（还描述了实现批量插入所需的设计更改）： 如何将复杂对象批量插入SQL Server数据库

Answer 9

尝试使用批量插入...

http://code.msdn.microsoft.com/LinqEntityDataReader

如果您有一组实体，例如 storeEntities，您可以使用 SqlBulkCopy 存储它们，如下所示

        var bulkCopy = new SqlBulkCopy(connection);
        bulkCopy.DestinationTableName = TableName;
        var dataReader = storeEntities.AsDataReader();
        bulkCopy.WriteToServer(dataReader);

。此代码有一个问题。确保实体的实体框架定义与表定义完全相关，确保实体的属性在实体模型中的顺序与 SQL Server 表中的列的顺序相同。如果不这样做将导致异常。

Answer 10

虽然回复晚了，但我还是发布了答案，因为我也遭受过同样的痛苦。
我为此创建了一个新的 GitHub 项目，到目前为止，它支持使用 SqlBulkCopy 透明地对 Sql 服务器进行批量插入/更新/删除。

https://github.com/MHanafy/EntityExtensions

还有其他好东西，希望，它将扩展以在未来做更多的事情。

使用它就像简单一样

var insertsAndupdates = new List<object>();
var deletes = new List<object>();
context.BulkUpdate(insertsAndupdates, deletes);

希望它有帮助！

Answer 11

 Use : db.Set<tale>.AddRange(list); 
Ref :
TESTEntities db = new TESTEntities();
List<Person> persons = new List<Person> { 
new  Person{Name="p1",Place="palce"},
new  Person{Name="p2",Place="palce"},
new  Person{Name="p3",Place="palce"},
new  Person{Name="p4",Place="palce"},
new  Person{Name="p5",Place="palce"}
};
db.Set<Person>().AddRange(persons);
db.SaveChanges();