EF大数据批量添加性能问题（续） - 罗朝福
罗朝福发表于 2015-08-22 06:21:00
昨天在园子里发了一篇如题的文章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 }
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 }
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 }
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 }
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
本文链接：EF大数据批量添加性能问题（续），转载请注明。