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         }

运行耗时：

 

 

再看看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         }

 

耗时情况：

 

 

不过还好有几位给出了很好的建议，用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         }

总耗时情况： 

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

转载于:https://www.cnblogs.com/izhaofu/p/4750196.html