Monday, November 28, 2011

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


The latest post in this serie can be found at

Speeding up inserts using Linq-To-Entities - Part 4

I posted a followup on this at http://blog.tanneryd.com/2011/12/speeding-up-inserts-using-linq-to-sql.html.

I use linq-to-sql quite a lot and I really like it. But, if you need to insert huge amounts of data into a table, linq-to-sql is not your best friend. I had a table I needed to initialize with more than a million rows and it took forever, something like 10 hours. I scanned the net for solutions but couldn't find anything I really liked. There were various tips about SqlBulkCopy but the proposed solutions were all really complex and mostly involved generating regular SQL code and I really did not want to do that. I just wanted the linq-to-sql datacontext to have a method like InsertAllOnSubmit that would bulk insert my millions of linq-to-sql entities. So, I wrote my own. The solution I ended up using is the one posted below. It extends the linq-to-sql generated DataContext class and should work just fine for you as well. The CommandTimeout setting isn't necessary but I added it just to show how to set it if you need to.
    partial class MyDataContext
    {
        partial void OnCreated()
        {
            CommandTimeout = 5*60;            
        }

        public void BulkInsertAll<T>(IEnumerable<T> entities)
        {
            string cs = Connection.ConnectionString;
            var conn = new SqlConnection(cs);
            conn.Open();

            Type t = typeof (T);

            var properties = t.GetProperties();
            var table = new DataTable();
            foreach (var property in properties)
            {
                table.Columns.Add(new DataColumn(property.Name, property.PropertyType));
            }

            foreach (var entity in entities)
            {
                table.Rows.Add(properties.Select(property => property.GetValue(entity, null)).ToArray());
            }
            var tableAttribute = (TableAttribute)t.GetCustomAttributes(typeof(TableAttribute), false).Single();
            var bulkCopy = new SqlBulkCopy(conn) { DestinationTableName = tableAttribute.Name };
            bulkCopy.WriteToServer(table);
            conn.Close();
        }
    }