EF大数据批量添加性能问题（续）

昨天在园子里发了一篇如题的文章EF大数据批量添加性能问题，就引来一大堆的吐槽，我认为知识就应该这样分享出来，不然总以为自己很了不起；再说说昨天那篇文章，很多自认为很牛逼的人都评论说把SaveChanges()放在for循环外面，我不知道他们有没有亲自去尝试过，反正我尝试了，然而并没什么卵用。

下面是我按照他们说的进行更改后的代码：

 public ActionResult Add(ItemDetails entity)
        {
            var sw = new Stopwatch();
            var count = 0;
            //var counts = 0;
            sw.Start();
            using (var db = new ShoppingDBConn())
            {
                for (var i = 0; i < 10000; i++)
                {
                    var data = new ItemDetails
                    {
                        AddedBy = entity.AddedBy,
                        Description = entity.Description,
                        Image_Name = entity.Image_Name,
                        Item_Name = entity.Item_Name,
                        Item_Price = entity.Item_Price
                    };
                    db.ItemDetails.Add(data);
                }
                count = db.SaveChanges();
            }
            sw.Stop();
            var date = sw.Elapsed;
            return Json(string.Format("总耗时：{0},添加数量：{1}", date, count));
        }

运行耗时：

再看看AddRange方式：

        public ActionResult Add(ItemDetails entity)
        {
            var sw = new Stopwatch();
            var count = 0;
            //var counts = 0;
            sw.Start();
            using (var db = new ShoppingDBConn())
            {
                var list = new List<ItemDetails>();
                for (var i = 0; i < 10000; i++)
                {
                    list.Add(new ItemDetails
                    {
                        AddedBy = entity.AddedBy,
                        Description = entity.Description,
                        Image_Name = entity.Image_Name,
                        Item_Name = entity.Item_Name,
                        Item_Price = entity.Item_Price
                    });
                }
                db.ItemDetails.AddRange(list);
                count = db.SaveChanges();
            }
            sw.Stop();
            var date = sw.Elapsed;
            return Json(string.Format("总耗时：{0},添加数量：{1}", date, count));
        }

耗时情况：

 

不过还好有几位给出了很好的建议，用SqlBulkCopy，下面是优化后的代码，比上面任何一种都要快好几倍：

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

            Type t = typeof(T);

            var bulkCopy = new SqlBulkCopy(conn)
            {
                DestinationTableName = t.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;
        }

调用该方法： 

 public ActionResult Add(ItemDetails entity)
        {
            var sw = new Stopwatch();
            var count = 0;
            //var counts = 0;
            sw.Start();
            using (var db = new ShoppingDBConn())
            {
                var list = new List<ItemDetails>();
                for (var i = 0; i < 10000; i++)
                {
                    list.Add(new ItemDetails
                    {
                        AddedBy = entity.AddedBy,
                        Description = entity.Description,
                        Image_Name = entity.Image_Name,
                        Item_Name = entity.Item_Name,
                        Item_Price = entity.Item_Price
                    });
                    count++;
                }
                BulkInsertAll(list);
            }
            sw.Stop();
            var date = sw.Elapsed;
            return Json(string.Format("总耗时：{0},添加数量：{1}", date, count));
        }

总耗时情况： 

比上一篇的拼接SQL都要快好几倍，在此很感谢@_April