Improving bulk insert performance in Entity framework

Question

I want to insert 20000 records in a table by entity framework and it takes about 2 min  Is there any way other than using SP to improve its performance  This is my code     foreach  Employees item in sequence        t   new Employees        t Text   item Text     dataContext Employees AddObject t                         dataContext SaveChanges

User · Answer

Using the code below you can extend the partial context class with a method that will take a collection of entity objects and bulk copy them to the database. Simply replace the name of the class from MyEntities to whatever your entity class is named and add it to your project, in the correct namespace. After that all you need to do is call the BulkInsertAll method handing over the entity objects you want to insert. Do not reuse the context class, instead create a new instance every time you use it. This is required, at least in some versions of EF, since the authentication data associated with the SQLConnection used here gets lost after having used the class once. I don't know why.

This version is for 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);
    }
}

This version is for 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);
    }

}

And finally, a little something for you Linq-To-Sql lovers.

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;
    }
}

User · Answer

Better way is to skip the Entity Framework entirely for this operation and rely on SqlBulkCopy class  Other operations can continue using EF as before   That increases the maintenance cost of the solution  but anyway helps reduce time required to insert large collections of objects into the database by one to two orders of magnitude compared to using EF   Here is an article that compares SqlBulkCopy class with EF for objects with parent-child relationship  also describes changes in design required to implement bulk insert   How to Bulk Insert Complex Objects into SQL Server Database

User · Answer

Maybe this answer here will help you  Seems that you want to dispose of the context periodically  This is because the context gets bigger and bigger as the attached entities grows

User · Answer

There are two major performance issues with your code    Using Add method Using SaveChanges   Using Add method  The Add method becomes only slower and slower at each entity you add   See  http   entityframework net improve-ef-add-performance  For example  adding 10 000 entities via    Add  take  105 000ms  AddRange  take  120ms    Note  Entities has not been saved yet in the database   The problem is that the Add method tries to DetectChanges at every entity added while AddRange does it once after all entities have been added to the context   Common solutions are    Use AddRange over Add SET AutoDetectChanges to false SPLIT SaveChanges in multiple batches   Using SaveChanges  Entity Framework has not been created for Bulk Operations  For every entity you save  a database round-trip is performed    So  if you want to insert 20 000 records  you will perform 20 000 database round-trip which is INSANE   There are some third-party libraries supporting Bulk Insert available    Z EntityFramework Extensions  Recommended  EFUtilities EntityFramework BulkInsert   See  Entity Framework Bulk Insert library  Be careful  when choosing a bulk insert library  Only Entity Framework Extensions support all kind of associations and inheritance  and it s the only one still supported     Disclaimer  I m the owner of Entity Framework Extensions  This library allows you to perform all bulk operations you need for your scenarios    Bulk SaveChanges Bulk Insert Bulk Delete Bulk Update Bulk Merge   Example     Easy to use context BulkSaveChanges        Easy to customize context BulkSaveChanges bulk   gt  bulk BatchSize   100       Perform Bulk Operations context BulkDelete customers   context BulkInsert customers   context BulkUpdate customers       Customize Primary Key context BulkMerge customers  operation   gt       operation ColumnPrimaryKeyExpression            customer   gt  customer Code        EDIT  Answer Question in Comment     Is there a recommend max size for each bulk insert for the library you created   Not too high  not too low  There isn t a particular value that fit in all scenarios since it depends on multiple factors such as row size  index  trigger  etc   It s normally recommended to be around 4000      Also is there a way to tie it all in one transaction and not worry about it timing out   You can use Entity Framework transaction  Our library uses the transaction if one is started  But be careful  a transaction that takes too much time come also with problems such as some row index table lock

User · Answer

Although a late reply  but I m posting the answer because I suffered the same pain  I ve created a new GitHub project just for that  as of now  it supports Bulk insert update delete for Sql server transparently using SqlBulkCopy   https   github com MHanafy EntityExtensions  There re other goodies as well  and hopefully  It will be extended to do more down the track   Using it is as simple as  var insertsAndupdates   new List lt object gt     var deletes   new List lt object gt     context BulkUpdate insertsAndupdates  deletes     Hope it helps

User · Answer

Currently there is no better way  however there may be a marginal improvement by moving SaveChanges inside for loop for probably 10 items   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      You can adjust 10 to be closer to better performance  It will not greatly improve speed but it will allow you to show some progress to user and make it more user friendly

User · Answer

Try using Bulk Insert      http   code msdn microsoft com LinqEntityDataReader  If you have a collection of entities e g storeEntities you can store them using SqlBulkCopy as follows          var bulkCopy   new SqlBulkCopy connection           bulkCopy DestinationTableName   TableName          var dataReader   storeEntities AsDataReader            bulkCopy WriteToServer dataReader     There is one gotcha with this code   Make sure that the Entity Framework definition for the entity correlates exactly with the table definition  ensure that the Entity s properties are in the same order in the Entity Model as the columns in the SQL Server table   Failure to do this will result in an exception

User · Answer

Use   db Set lt tale gt  AddRange list    Ref   TESTEntities db   new TESTEntities    List lt Person gt  persons   new List lt Person gt     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 lt Person gt    AddRange persons   db SaveChanges

User · Answer

In Azure environment with Basic website that has 1 Instance I tried to insert a Batch of 1000 records at a time out of 25000 records using for loop it took 11 5 min but in parallel execution it took  less than a minute So I recommend using TPL Task Parallel Library             var count    you collection   1000    1           Parallel For 0  count  x   gt                        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

User · Answer

There is opportunity for several improvements  if you are using DbContext    Set   yourContext Configuration AutoDetectChangesEnabled   false  yourContext Configuration ValidateOnSaveEnabled   false    Do SaveChanges   in packages of 100 inserts    or you can try with packages of 1000 items and see the changes in performance      Since during all this inserts  the context is the same and it is getting bigger  you can rebuild your context object every 1000 inserts  var yourContext   new YourContext    I think this is the big gain   Doing this improvements in an importing data process of mine  took it from 7 minutes to 6 seconds    The actual numbers    could not be 100 or 1000 in your case    try it and tweak it

User · Answer

There is no way to force EF to improve performance when doing it this way  The problem is that EF executes each insert in separate round trip to the database  Awesome isn t it  Even DataSets supported batch processing  Check this article for some workaround  Another workaround can be using custom stored procedure accepting table valued parameter but you need raw ADO NET for that

[entity-framework] Improving bulk insert performance in Entity framework

Examples related to entity-framework

Examples related to entity