你的位置:首页 > ASP.net教程

[ASP.net教程]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 ItemDetails12           {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_Price18           };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     }

运行耗时:

 

 

再看看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 ItemDetails13           {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_Price19           });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     }

 

耗时情况:

 

 

不过还好有几位给出了很好的建议,用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.Name14       };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 ItemDetails13           {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_Price19           });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     }

耗时情况:

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