[entityframework指量添加大数据库]EntityFramework指量添加大数据如何处理性能问题

更新时间:2019-12-27    来源:php与数据库    手机版     字体:

【www.bbyears.com--php与数据库】

做一个批量发消息的功能,要向消息表中批量写入数据,用的是微软的EF框架的插入方法;发现就10000条数据就耗时好几分钟,对应追求用户体验的我来说这是极不能容忍的,后来改为拼接SQL,性能提高了好几倍;现在来分享一下经验:

原始的方法类似这种:

public ActionResult Add(ItemDetails entity)
        {
            var sw = new Stopwatch();
            sw.Start();
            using (db)
            {
                for (var i = 0; i < 10000; i++)
                {
                    db.ItemDetails.Add(entity);
                    db.SaveChanges();
                }
            }
            sw.Stop();
            var date = sw.Elapsed;
            return Json(string.Format("总耗时:{0}", date));
        }



来看看添加10000条数据耗时:


就10000条数据就耗时这么久,要是上百万的数据量,那简直就不能想象,再来看看优化后的:

生成SQL的方法:

public class ItemDetailBatch
    {
        public static string BatchAdd(ItemDetails entity)
        {
            SqlParameter [] paras=
            {
                new SqlParameter("@Item_Name",SqlDbType.VarChar,100), 
                new SqlParameter("@Item_Price",SqlDbType.Int), 
                new SqlParameter("@Image_Name",SqlDbType.VarChar,100), 
                new SqlParameter("@Description",SqlDbType.VarChar,100),
                new SqlParameter("@AddedBy",SqlDbType.VarChar,100)
            };
            paras[0] .Value= entity.Item_Name;
            paras[1].Value = entity.Item_Price;
            paras[2].Value = entity.Image_Name;
            paras[3].Value = entity.Description;
            paras[4].Value = entity.AddedBy;
            var sb=new StringBuilder();
            sb.Append("insert into ItemDetails (Item_Name,Item_Price,Image_Name,Description,AddedBy) ");
            sb.AppendFormat("values ('{0}',{1},'{2}','{3}','{4}')", paras[0].Value, paras[1].Value, paras[2].Value,paras[3].Value, paras[4].Value);
            return sb.ToString();
        }
    }


 

Controller层调用:

public ActionResult Add(ItemDetails entity)
        {
            var sw = new Stopwatch();
            sw.Start();
            using (var db = new ShoppingDBConn())
            {
                var sql = new StringBuilder();
                for (int i = 0; i < 10000; i++)
                {
                    //生成SQL
                    sql.Append(ItemDetailBatch.BatchAdd(entity));
                }
                //一次性执行SQL
                db.Database.ExecuteSqlCommand(sql.ToString());
            }
            sw.Stop();
            var date = sw.Elapsed;
            return Json(string.Format("总耗时:{0}", date));
        }


        
界面数据:

同样10000条总耗时:

EF没添加一次都要向数据库提交一次,而直接拼接SQL的方法就是减少与数据库的交互次数,一次性提交执行所有数据;

继续讨论上面问题


上面的文章经纶有很多人在吐槽,很多人说说把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();
                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(IEnumerable 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();
                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都要快好几倍。

本文来源:http://www.bbyears.com/jiaocheng/83995.html