EF6多线程大量数据写入性能测试

本示例源代码https://git.coding.net/oyyz/vip.oyyz.eftest.git
数据库设计请直接参考模型代码,可在程序.config文件中修改数据库连接字符串
本示例模拟数据库大量数据写入,并存在一定数据转换耗时操作

主函数

        static void Main(string[] args)
        {
            try
            {
                // 1
                Console.WriteLine("开始测试1");
                //t1();
                Console.WriteLine("");

                //2
                Console.WriteLine("开始测试2");
                //t2();
                Console.WriteLine("");

                //3
                Console.WriteLine("开始测试3");
                //t3();
                Console.WriteLine("");

                //4 多线程独立实例数据AddRange操作
                Console.WriteLine("开始测试4,增加10W数据,1-15线程");
                Mult(100000, 1, 15);
                Console.WriteLine("");

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

一、多线程对单一上下文实例进行Add操作

        /// <summary>
        /// 多线程对同一实例进行ADD操作
        /// 会产生异常
        /// </summary>
        static void t1()
        {
            Stopwatch w1 = new Stopwatch();
            w1.Start();

            Task<string> t1 = Task.Run(() => AddLineGlobal(5000));
            Task<string> t2 = Task.Run(() => AddLineGlobal(5000));
            Task.WaitAll(t1, t2);
            Console.WriteLine("t1:" + t1.Result);
            Console.WriteLine("t2:" + t2.Result);
            w1.Stop();
            if (t1.Result == "完成" && t2.Result == "完成")
            {
                Console.WriteLine("用时(s):" + w1.Elapsed.TotalSeconds);
            }
            else
            {
                Console.WriteLine("出现异常不记时");
            }
        }

        /// <summary>
        /// 使用全局实例向数据库增加新行
        /// </summary>
        /// <param name="count">增加数量</param>
        /// <returns>添加结果</returns>
        static string AddLineGlobal(int count)
        {
            try
            {
                for (int i = 0; i < count; i++)
                {
                    dbGlobal.T1MST.Add(new Models.T1MST()
                    {
                        T1KEY_NO = Guid.NewGuid(),
                        TIN_DAT = DateTime.Now
                    });
                }           
                dbGlobal.SaveChanges();
                return "完成";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

结果:抛出异常,不可取。

二、单线程操作,单条Add,完成后调用SaveChanges

        /// <summary>
        /// 单线程对同一实例进行ADD操作并保存
        /// 单条添加统一保存
        /// </summary>
        static void t2()
        {
            Stopwatch w1 = new Stopwatch();
            w1.Start();

            Task<string> t1 = Task.Run(() => AddLineGlobal(5000 * 2));
            Task<string> t2 = Task.Run(() => AddLineGlobal(0));
            Task.WaitAll(t1, t2);
            Console.WriteLine("t1:" + t1.Result);
            Console.WriteLine("t2:" + t2.Result);
            w1.Stop();
            if (t1.Result == "完成" && t2.Result == "完成")
            {
                Console.WriteLine("用时(s):" + w1.Elapsed.TotalSeconds);
            }
            else
            {
                Console.WriteLine("出现异常不记时");
            }
        }

结果

三、单线程操作,AddRange,后调用SaveChanges

        /// <summary>
        /// 单线程对同一实例进行AddRange操作并保存
        /// </summary>
        static void t3()
        {
            Stopwatch w1 = new Stopwatch();
            w1.Start();

            Task<string> t1 = Task.Run(() => AddRangeGlobal(5000 * 2));
            Task<string> t2 = Task.Run(() => AddRangeGlobal(0));
            Task.WaitAll(t1, t2);
            Console.WriteLine("t1:" + t1.Result);
            Console.WriteLine("t2:" + t2.Result);
            w1.Stop();
            if (t1.Result == "完成" && t2.Result == "完成")
            {
                Console.WriteLine("用时(s):" + w1.Elapsed.TotalSeconds);
            }
            else
            {
                Console.WriteLine("出现异常不记时");
            }
        }

        /// <summary>
        /// 生成数据后AddRange
        /// </summary>
        /// <param name="count"></param>
        /// <returns></returns>
        private static string AddRangeGlobal(int count)
        {
            Models.TESTDBEntities dbGlobal = new Models.TESTDBEntities();
            if (count <= 0)
            {
                return "完成";
            }
            List<Models.T1MST> inlist = new List<Models.T1MST>();
            try
            {
                for (int i = 0; i < count; i++)
                {
                    inlist.Add(new Models.T1MST()
                    {
                        T1KEY_NO = Guid.NewGuid(),
                        TIN_DAT = DateTime.Now
                    });
                }
                dbGlobal.T1MST.AddRange(inlist);
                dbGlobal.SaveChanges();
                return "完成";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }

结果:

结论:对于批量数据添加AddRange效率大大高于Add

四、多线程独立实例数据AddRange操作

        /// <summary>
        /// 多线程AddRange
        /// </summary>
        /// <param name="count">每次新增数据总量</param>
        /// <param name="start">最小线程数</param>
        /// <param name="end">最大线程数</param>
        static void Mult(int count, int start, int end)
        {

            List<Task> tasks = new List<Task>();
            for (int i = start; i <= end; i++)
            {
                Stopwatch w1 = new Stopwatch();
                w1.Start();
                int step = count / i;
                int lstep = count % i;
                tasks = new List<Task>();
                for (int j = 1; j <= i; j++)
                {
                    int reals;
                    if (j == i)
                    {
                        reals = step + lstep;
                    }
                    else
                    {
                        reals = step;
                    }
                    tasks.Add(Task.Run(() => AddRange(reals)));
                }
                foreach (var t in tasks)
                {
                    Task.WaitAll(t);
                }
                w1.Stop();
                Console.WriteLine(i + "个线程用时(s):" + w1.Elapsed.TotalSeconds);
            }
        }

结论:对于数据转换存在一定耗时操作时,多线程处理能一定程度上提高效率,从理论上讲线程数量超过CPU线程数后几乎无性能提升(甚至有下降,增开线程有额外开销,测试环境CPU为4核心8线程)

注意

在测试1-15线程添加数据时,最后发现数据总量略大于150W,单步跟踪未能发现异常,但直接运行可复现此情况,最后发现为循环中使用的变量定义位置问题,若变量在循环中被赋值,请在循环体内定义,否则可能出现数据异常!

理论上讲在循环中重复赋值的变量可定义在循环体外提高效率,但也因此可能产生一定的风险!具体原因待讨论。