Friday, December 9, 2011

Speeding up inserts using Linq-To-Sql - Part 2

Part one is found at http://blog.tanneryd.com/2011/11/speeding-up-inserts-using-linq-to-sql.html.

This version works better if you have nullable columns in your table and it also handles foreign keys. I.e. it will ignore foreign key objects but it requires that you set the foreign key id instead.
    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;
        }
    }

5 comments:

Josh Buedel said...

This post was a real timesaver to me today. I cut a data importer runtime down by about half. Thanks!

Måns Tånneryd said...

Your very welcome. Glad it helped!

gabe said...

Cut my insert time by a factor of 10! Sweet.

La bala que dobló la esquinna said...

If you modify the function

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

with this

private bool EventTypeFilter ( System.Reflection.PropertyInfo p )
{
/ / Exclude column ID for bulk upload
if ( p.Name == "ID" )
return false;

Attribute.GetCustomAttribute columAttr var = (p, typeof ( ColumnAttribute )) as ColumnAttribute ;
if ( columAttr ! = null)
{
if ( columAttr.IsPrimaryKey )
{
columAttr.IsPrimaryKey = false;
return false;
}
else return true;
}
else
return false;
}

BulkCopy ignores IDENTITY column and insert the record with the original ID

Regards

La bala que dobló la esquinna said...

If you modify the function

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

with this

private bool EventTypeFilter ( System.Reflection.PropertyInfo p )
{
/ / Exclude column ID for bulk upload
if ( p.Name == "ID" )
return false;

Attribute.GetCustomAttribute columAttr var = (p, typeof ( ColumnAttribute )) as ColumnAttribute ;
if ( columAttr ! = null)
{
if ( columAttr.IsPrimaryKey )
{
columAttr.IsPrimaryKey = false;
return false;
}
else return true;
}
else
return false;
}

BulkCopy ignores IDENTITY column and insert the record with the original ID

Regards