转载

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

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

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

 1  public ActionResult Add(ItemDetails entity)  2         {  3             var sw = new Stopwatch();  4             var count = 0;  5             //var counts = 0;  6             sw.Start();  7             using (var db = new ShoppingDBConn())  8             {  9                 for (var i = 0; i < 10000; i++) 10                 { 11                     var data = new ItemDetails 12                     { 13                         AddedBy = entity.AddedBy, 14                         Description = entity.Description, 15                         Image_Name = entity.Image_Name, 16                         Item_Name = entity.Item_Name, 17                         Item_Price = entity.Item_Price 18                     }; 19                     db.ItemDetails.Add(data); 20                 } 21                 count = db.SaveChanges(); 22             } 23             sw.Stop(); 24             var date = sw.Elapsed; 25             return Json(string.Format("总耗时：{0},添加数量：{1}", date, count)); 26         }

运行耗时：

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

再看看AddRange方式：

 1         public ActionResult Add(ItemDetails entity)  2         {  3             var sw = new Stopwatch();  4             var count = 0;  5             //var counts = 0;  6             sw.Start();  7             using (var db = new ShoppingDBConn())  8             {  9                 var list = new List<ItemDetails>(); 10                 for (var i = 0; i < 10000; i++) 11                 { 12                     list.Add(new ItemDetails 13                     { 14                         AddedBy = entity.AddedBy, 15                         Description = entity.Description, 16                         Image_Name = entity.Image_Name, 17                         Item_Name = entity.Item_Name, 18                         Item_Price = entity.Item_Price 19                     }); 20                 } 21                 db.ItemDetails.AddRange(list); 22                 count = db.SaveChanges(); 23             } 24             sw.Stop(); 25             var date = sw.Elapsed; 26             return Json(string.Format("总耗时：{0},添加数量：{1}", date, count)); 27         }

耗时情况：

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

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

 1         public void BulkInsertAll<T>(IEnumerable<T> entities)    2         {  3             entities = entities.ToArray();  4             var cons=new ShoppingDBConn();  5             string cs = cons.Database.Connection.ConnectionString;  6             var conn = new SqlConnection(cs);  7             conn.Open();  8   9             Type t = typeof(T); 10  11             var bulkCopy = new SqlBulkCopy(conn) 12             { 13                 DestinationTableName = t.Name 14             }; 15  16             var properties = t.GetProperties().Where(EventTypeFilter).ToArray(); 17             var table = new DataTable(); 18  19             foreach (var property in properties) 20             { 21                 Type propertyType = property.PropertyType; 22                 if (propertyType.IsGenericType && 23                     propertyType.GetGenericTypeDefinition() == typeof(Nullable<>)) 24                 { 25                     propertyType = Nullable.GetUnderlyingType(propertyType); 26                 } 27  28                 table.Columns.Add(new DataColumn(property.Name, propertyType)); 29             } 30  31             foreach (var entity in entities) 32             { 33                 table.Rows.Add(properties.Select( 34                   property => GetPropertyValue( 35                   property.GetValue(entity, null))).ToArray()); 36             } 37  38             bulkCopy.WriteToServer(table); 39             conn.Close(); 40         } 41  42         private bool EventTypeFilter(System.Reflection.PropertyInfo p) 43         { 44             var attribute = Attribute.GetCustomAttribute(p, 45                 typeof(AssociationAttribute)) as AssociationAttribute; 46  47             if (attribute == null) return true; 48             if (attribute.IsForeignKey == false) return true; 49  50             return false; 51         } 52  53         private object GetPropertyValue(object o) 54         { 55             if (o == null) 56                 return DBNull.Value; 57             return o; 58         }

调用该方法：

 1  public ActionResult Add(ItemDetails entity)  2         {  3             var sw = new Stopwatch();  4             var count = 0;  5             //var counts = 0;  6             sw.Start();  7             using (var db = new ShoppingDBConn())  8             {  9                 var list = new List<ItemDetails>(); 10                 for (var i = 0; i < 10000; i++) 11                 { 12                     list.Add(new ItemDetails 13                     { 14                         AddedBy = entity.AddedBy, 15                         Description = entity.Description, 16                         Image_Name = entity.Image_Name, 17                         Item_Name = entity.Item_Name, 18                         Item_Price = entity.Item_Price 19                     }); 20                     count++; 21                 } 22                 BulkInsertAll(list); 23             } 24             sw.Stop(); 25             var date = sw.Elapsed; 26             return Json(string.Format("总耗时：{0},添加数量：{1}", date, count)); 27         }