测试EntityFramework，Z.EntityFramework.Extensions，原生语句在不同的查询中的表现。原来池化与非池化设定是有巨大的影响的。

Insert测试，只测试1000条的情况，多了在实际的项目中应该就要另行处理了。

 

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.Entity;
using System.Diagnostics;
using System.Linq;
using EE.Service.DbAccess;
using EE.Service.DbEntity;
using EntityFramework.Extensions;
using EntityFramework.Future;
using MySql.Data.MySqlClient;

namespace EE.Services.ConsoleTest.DbAccessTests
{
    public class EFPerformanceTest
    {
        public static void Run()
        {
            //InsertTest();

            UpdateTests();
        }



        #region UpdateTest()

        static void UpdateTests()
        {
            var list = new List<SmsLog>();
            for (int i = 0; i < 1000; i++)
            {
                list.Add(new SmsLog() { Id = i, AppId = $"{i}_", CreateTime = DateTime.Now });
            }
            var sw = new Stopwatch();
            Watcher(sw, EFInit, list.GetRange(0, 10));

            Watcher(sw, MySqlSelect, list);

            Watcher(sw, EFSelect,list);



            Watcher(sw, MySqlUpdate, list);

            Watcher(sw, EFBulkUpdate, AddRangeId(list));

            Watcher(sw, EFUpdateWithNoState, AddRangeId(list));

            Watcher(sw, EFUpdate, AddRangeId(list));

        }

        static IEnumerable<SmsLog> AddRangeId(IEnumerable<SmsLog> logs)
        {
            foreach (var log in logs)
            {
                log.Id += 1000;
            }
            return logs;
        }

        static int EFSelect(IEnumerable<SmsLog> items)
        {
            using (var db = new ServiceLogDbContent())
            {
                var val = 0;
                foreach (var item in items)
                {
                    var rst = (from tb in db.SmsLogs where tb.Id == 1 select tb).FirstOrDefault();
                    if (rst != null) val++;
                }
                return val;
            }
        }

        static int EFUpdate(IEnumerable<SmsLog> items)
        {
            using (var db = new ServiceLogDbContent())
            {
                foreach (var item in items)
                {
                    //db.SmsLogs.Attach(item);
                    var o = db.SmsLogs.Where(x => x.Id == item.Id).FirstOrDefault();
                    if (o == null) continue;
                    o.AppId = item.AppId+"U";
                    o.CreateTime = item.CreateTime;
                }
                var val = db.SaveChanges();
                return val;
            }
        }

        static int EFUpdateWithNoState(IEnumerable<SmsLog> items)
        {
            using (var db = new ServiceLogDbContent())
            {
                db.Configuration.AutoDetectChangesEnabled = false;
                db.Configuration.ValidateOnSaveEnabled = false;
                db.Configuration.LazyLoadingEnabled = false;
                db.Configuration.ProxyCreationEnabled = false;
                var val = 0;
                foreach (var item in items)
                {
                    //传统更新方式 .. 更新不了（没有状态，无法完成更新）
                    var o = db.SmsLogs.Where(x => x.Id == item.Id).FirstOrDefault();
                    if (o == null) continue;
                    o.AppId = item.AppId+"SU";
                    o.CreateTime = item.CreateTime;
                }
                val = db.SaveChanges();
                return val;
            }
        }

        static int EFBulkUpdate(IEnumerable<SmsLog> items)
        {
            using (var db = new ServiceLogDbContent())
            {
                foreach (var item in items)
                {
                    var o = db.SmsLogs.Where(x => x.Id == item.Id).FirstOrDefault();
                    if (o == null) continue;
                    o.AppId = item.AppId+"BU";
                    o.CreateTime = item.CreateTime;
                }
                db.BulkSaveChanges();
                return 0;
            }
        }

        static int MySqlUpdate(IEnumerable<SmsLog> items)
        {
            var val = 0;
            var connStr = ConfigurationManager.ConnectionStrings["EEServiceLogDb"].ConnectionString;
            using (var conn = new MySqlConnection(connStr))
            {
                conn.Open();
                foreach (var item in items)
                {
                    val += MySqlHelper.ExecuteNonQuery(conn, "Update SmsLogs set AppId=@appId,CreateTime=@createTime where Id = @id;", new MySqlParameter("@appId", item.AppId+"S"), new MySqlParameter("@createTime", item.CreateTime), new MySqlParameter("@id", item.Id));
                }
                conn.Close();
            }
            return val;
        }
        static int MySqlSelect(IEnumerable<SmsLog> items)
        {
            var val = 0;
            var connStr = ConfigurationManager.ConnectionStrings["EEServiceLogDb"].ConnectionString;
            using (var conn = new MySqlConnection(connStr))
            {
                conn.Open();
                foreach (var item in items)
                {
                    var dr = MySqlHelper.ExecuteReader(conn, "select * from SmsLogs where Id = @id;", new[] { new MySqlParameter("@id", item.Id) });
                    while (dr.Read())
                    {
                        val += 1;
                    }
                    dr.Close();
                }
                conn.Close();
            }
            return val;
        }



        #endregion



        #region InsertTest()


        static void InsertTest()
        {
            var list = new List<SmsLog>();
            for (int i = 0; i < 1000; i++)
            {
                list.Add(new SmsLog() { AppId = $"{i}", CreateTime = DateTime.Now });
            }
            var sw = new Stopwatch();

            Watcher(sw, EFInit, list.GetRange(0, 10));
            Watcher(sw, EFBulkInsert, list);
            Watcher(sw, EFInsertWithNoState, list);
            Watcher(sw, EFInsert, list);
            Watcher(sw, MySqlInsert, list);
            //Watcher(sw, EFInsert, list);

        }


        static int EFInsert(IEnumerable<SmsLog> items)
        {
            using (var db = new ServiceLogDbContent())
            {
                foreach (var item in items)
                    db.SmsLogs.Add(item);

                var val = db.SaveChanges();
                return val;
            }
        }

        static int EFInsertWithNoState(IEnumerable<SmsLog> items)
        {
            using (var db = new ServiceLogDbContent())
            {
                db.Configuration.AutoDetectChangesEnabled = false;
                db.Configuration.ValidateOnSaveEnabled = false;
                db.Configuration.LazyLoadingEnabled = false;
                db.Configuration.ProxyCreationEnabled = false;

                foreach (var item in items)
                    db.SmsLogs.Add(item);

                var val = db.SaveChanges();
                return val;
            }
        }

        static int EFBulkInsert(IEnumerable<SmsLog> items)
        {
            using (var db = new ServiceLogDbContent())
            {
                //db.Configuration.AutoDetectChangesEnabled = false;
                //db.Configuration.ValidateOnSaveEnabled = false;
                foreach (var item in items)
                    db.SmsLogs.Add(item);

                db.BulkSaveChanges();
                return 0;
            }
        }

        static int MySqlInsert(IEnumerable<SmsLog> items)
        {
            var val = 0;
            var connStr = ConfigurationManager.ConnectionStrings["EEServiceLogDb"].ConnectionString;
            using (var conn = new MySqlConnection(connStr))
            {
                conn.Open();
                foreach (var item in items)
                {
                    val += MySqlHelper.ExecuteNonQuery(conn, "Insert into SmsLogs(AppId,CreateTime)values(@appId,@createTime)", new MySqlParameter("@appId", item.AppId), new MySqlParameter("@createTime", item.CreateTime));
                }
                conn.Close();
            }
            return val;
        }


        #endregion



        /// <summary>
        /// 初始化一下，尽量减少EF初始化时间的影响
        /// </summary>
        /// <param name="items"></param>
        /// <returns></returns>
        static int EFInit(IEnumerable<SmsLog> items)
        {
            using (var db = new ServiceLogDbContent())
            {
                foreach (var item in items)
                {
                    var o = db.SmsLogs.FirstOrDefault(x => x.Id == item.Id);
                    if (o != null)
                    {
                        o.AppId = "Init";
                    }
                }
                var val = db.SaveChanges();
                return val;
            }
        }

        static void Watcher<T>(Stopwatch stopwatch, Func<IEnumerable<T>, int> func, IEnumerable<T> list)
        {
            if (stopwatch == null) stopwatch = new Stopwatch();
            stopwatch.Reset();
            Console.WriteLine($"[{DateTime.Now:HH:mm:ss ffffff}] {func.Method.Name} BEGIN =====================================================");
            stopwatch.Start();
            var val = func(list);
            stopwatch.Stop();
            Console.WriteLine($"[{DateTime.Now:HH:mm:ss ffffff}] {func.Method.Name}[{stopwatch.Elapsed}  IN<{list.Count()}> -> RS<{val}>]");
            //Console.WriteLine($"[{DateTime.Now:HH:mm:ss ffffff}] {func.Method.Name} end");
        }

    }
}

 

 

本机开发机，DB在内网服务器上。测试结果：

可以看到，EF的初始执行还是蛮耗时间的。扩展的BuilInsert要比循环的MySqlInsert语句效率还好一些。关掉状态跟踪与默认情况，差别不大（不知是不是我写的有问题）。

 

 

这么一看，是因为查询慢吗？

更新的方式就是选Where出对象，再修改对象的值，再SaveChagnes。 难道是我写的方式不对？为什么查询这么慢？

 -----------------------------------------------------------------------------------------------------------------------------------

 

原来不是我的写的问题，而是配置问题，连接串中没有池化与有池化的结果相差近10倍。这算什么？？？

Pooling=false|true;

连接串中的这个就是影响性能差别巨大的“元凶”

 

下面的是EF的Save，即AddOrUpdate ，通过1000条的测试发现：耗时都在4~5秒左右。比起先Select再Update还是要耗时一些。所以在性能有要求的地方，要稍微慎重一些。是不是先读取后赋值一下再Update。还是直接AddOrUpdate