你的位置:首页 > ASP.net教程

[ASP.net教程]测试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");    }  }}

View Code

 

 

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

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

 

 

这么一看,是因为查询慢吗?

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

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

 

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

Pooling=true;

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