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

[ASP.net教程]Dapper ORM VS SqlSugar ORM的 8场对决

CUP和.NET SQL版本不同也会存在少许差距,但不会有质变,下面的测试结果仅供参考

比赛规则

1.统一使用Realse版本的最新 DLL,Realse模式启用程序

2.为了平衡CPU和数据库空闲情况,使用车轮战,每场比赛连续10回合比试

3.多次重启电脑取平均成绩上图

比赛成员 

1.SqlSugar 3.1.01

2.Dapper 1.5.0.2  Dapper.Contrib 1.5 官方DLL

 

第一场 :查询所有  ,主要比拼数据转换实体的性能 

每次查询100万条数据 

 

SqlSugar 100分  Dapper95分

比赛结果: SqlSugar小胜 

 

代码:

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;using Dapper;using Dapper.Contrib;using Dapper.Contrib.Extensions;using Models.PkDapper;using SqlSugar;using SyntacticSugar;namespace PkDapper.Demos{  public class SelectBigData : IDemos  {    /// <summary>    /// 测试一次读取100万条数据的速度    /// </summary>    public void Init()    {      Console.WriteLine("测试一次读取100万条数据的速度");      var eachCount = 1;      /*******************车轮战是性能评估最准确的一种方式***********************/      for (int i = 0; i < 10; i++)      {        //dapper        Dapper(eachCount);        //sqlSugar        SqlSugar(eachCount);       }     }    private static void SqlSugar(int eachCount)    {      GC.Collect();//回收资源      System.Threading.Thread.Sleep(2000);//休息2秒      PerHelper.Execute(eachCount, "SqlSugar", () =>      {        using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))        {          var list = conn.Queryable<Test>().ToList();        }      });    }    private static void Dapper(int eachCount)    {      GC.Collect();//回收资源      System.Threading.Thread.Sleep(2000);//休息2秒      //正试比拼      PerHelper.Execute(eachCount, "Dapper", () =>      {        using (SqlConnection conn = new SqlConnection(PubConst.connectionString))        {          var list = conn.GetAll<Test>();        }      });    }  }}

View Code

 

第二场:查询单条,主要比拼实体转换以外的性能

SqlSugar 100分  Dapper 100分

比赛结果:平手

 

代码: 

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;using Dapper;using Dapper.Contrib;using Dapper.Contrib.Extensions;using Models.PkDapper;using SqlSugar;using SyntacticSugar;namespace PkDapper.Demos{  public class SelectSingle : IDemos  {    /// <summary>    /// 测试一次读取1条,每次执行1000次    /// </summary>    public void Init()    {      Console.WriteLine("测试一次读取1条");      var eachCount = 1000;      /*******************车轮战是性能评估最准确的一种方式***********************/      for (int i = 0; i < 10; i++)      {        //dapper        Dapper(eachCount);        //sqlSugar        SqlSugar(eachCount);      }    }    private static void SqlSugar(int eachCount)    {      GC.Collect();//回收资源      System.Threading.Thread.Sleep(2000);//休息2秒      PerHelper.Execute(eachCount, "SqlSugar", () =>      {        using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))        {          var list = conn.Queryable<Test>().InSingle(1000);        }      });    }    private static void Dapper(int eachCount)    {      GC.Collect();//回收资源      System.Threading.Thread.Sleep(2000);//休息2秒      //正试比拼      PerHelper.Execute(eachCount, "Dapper", () =>      {        using (SqlConnection conn = new SqlConnection(PubConst.connectionString))        {          var list = conn.Get<Test>(1000);        }      });    }  }}

View Code

 

第三场:比拼海量数据更新

 

SqlSugar 100分  Dapper 60分

比赛结果:SqlSugar胜出

 

代码: 

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;using Dapper;using Dapper.Contrib;using Dapper.Contrib.Extensions;using Models.PkDapper;using SqlSugar;using SyntacticSugar;namespace PkDapper.Demos{  public class UpdateList : IDemos  {    public void Init()    {      Console.WriteLine("测试更新1000条的集合");      var eachCount = 10;      /*******************车轮战是性能评估最准确的一种方式***********************/      for (int i = 0; i < 10; i++)      {        //dapper        Dapper(eachCount);        //sqlSugar        SqlSugar(eachCount);      }    }    private static List<Test> GetList    {      get      {        List<Test> list = new List<Test>();        for (int i = 1000; i < 2000; i++)        {          Test t = new Test()          {            Id=i,            F_Int32 = 1,            F_String = "Test",            F_Float = 1,            F_DateTime = DateTime.Now,            F_Byte = 1,            F_Bool = true          };          list.Add(t);        }        return list;      }    }    private static void SqlSugar(int eachCount)    {      GC.Collect();//回收资源      System.Threading.Thread.Sleep(2000);//休息2秒      PerHelper.Execute(eachCount, "SqlSugar", () =>      {        using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))        {          var list = conn.SqlBulkReplace(GetList);        }      });    }    private static void Dapper(int eachCount)    {      GC.Collect();//回收资源      System.Threading.Thread.Sleep(2000);//休息2秒      //正试比拼      PerHelper.Execute(eachCount, "Dapper", () =>      {        using (SqlConnection conn = new SqlConnection(PubConst.connectionString))        {          var list = conn.Update(GetList);        }      });    }  }}

View Code

 

 

第四场:比拼海量数据插入 

SqlSugar 100分  Dapper 12分

比赛结果:SqlSugar胜出

 

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;using Dapper;using Dapper.Contrib;using Dapper.Contrib.Extensions;using Models.PkDapper;using SqlSugar;using SyntacticSugar;namespace PkDapper.Demos{  public class InsertList : IDemos  {     public void Init()    {      Console.WriteLine("测试插入1000条记录的集合");      var eachCount = 10;      /*******************车轮战是性能评估最准确的一种方式***********************/      for (int i = 0; i < 10; i++)      {        //清除        DeleteAddDatas();        //dapper        Dapper(eachCount);        //清除        DeleteAddDatas();        //sqlSugar        SqlSugar(eachCount);      }      Console.WriteLine("SqlSugar批量插入性能,秒杀Dapper一条街。(Dapper并没有优化过)");    }    private static void DeleteAddDatas()    {      using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))      {        conn.Delete<Test>(it => it.F_String == "Test");      }    }    private static List<Test> GetList    {      get      {        List<Test> list = new List<Test>();        for (int i = 0; i < 1000; i++)        {          Test t = new Test()          {            F_Int32 = 1,            F_String = "Test",            F_Float = 1,            F_DateTime = DateTime.Now,            F_Byte = 1,            F_Bool = true          };          list.Add(t);        }        return list;      }    }    private static void SqlSugar(int eachCount)    {      GC.Collect();//回收资源      System.Threading.Thread.Sleep(2000);//休息2秒      PerHelper.Execute(eachCount, "SqlSugar", () =>      {        using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))        {          var list = conn.SqlBulkCopy(GetList);        }      });    }    private static void Dapper(int eachCount)    {      GC.Collect();//回收资源      System.Threading.Thread.Sleep(2000);//休息2秒      //正试比拼      PerHelper.Execute(eachCount, "Dapper", () =>      {        using (SqlConnection conn = new SqlConnection(PubConst.connectionString))        {          var list = conn.Insert(GetList);        }      });    }  }}

View Code

 

第五场:比拼批量删除 

 

SqlSugar 100分  Dapper 50分

比赛结果:SqlSugar胜出

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;using Dapper;using Dapper.Contrib;using Dapper.Contrib.Extensions;using Models.PkDapper;using SqlSugar;using SyntacticSugar;namespace PkDapper.Demos{  public class DeleteArray : IDemos  {    public void Init()    {      Console.WriteLine("测试删除1000条的集合");      var eachCount = 10;      /*******************车轮战是性能评估最准确的一种方式***********************/      for (int i = 0; i < 10; i++)      {        //dapper        Dapper(eachCount);        //sqlSugar        SqlSugar(eachCount);      }    }    /// <summary>    /// 查询出刚插入的1000条数据    /// </summary>    /// <returns></returns>    private static List<Test> GetDeleteList()    {      using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))      {        conn.Delete<Test>(it => it.F_String == "Test");        //插入1000条        conn.SqlBulkCopy(GetList);        //查询出插入的1000条        var list = conn.Queryable<Test>().Where(it => it.F_String == "Test").ToList();        return list;      }    }    private static List<Test> GetList    {      get      {        List<Test> list = new List<Test>();        for (int i = 1; i < 1000; i++)        {          Test t = new Test()          {            F_Int32 = 1,            F_String = "Test",            F_Float = 1,            F_DateTime = DateTime.Now,            F_Byte = 1,            F_Bool = true          };          list.Add(t);        }        return list;      }    }    private static void SqlSugar(int eachCount)    {      GC.Collect();//回收资源      System.Threading.Thread.Sleep(2000);//休息2秒      PerHelper.Execute(eachCount, "SqlSugar", () =>      {        using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))        {          var ids = GetDeleteList().Select(it => it.Id).ToArray();          var list = conn.Delete<Test,int>(ids);        }      });    }    private static void Dapper(int eachCount)    {      GC.Collect();//回收资源      System.Threading.Thread.Sleep(2000);//休息2秒      //正试比拼      PerHelper.Execute(eachCount, "Dapper", () =>      {        using (SqlConnection conn = new SqlConnection(PubConst.connectionString))        {          var delList = GetDeleteList();          var list = conn.Delete(delList);        }      });    }  }}

View Code

 

第六场:分页,项目使用最多的场景之一

 

SqlSugar 96分  Dapper 100分

比赛结果:Dapper小胜

 

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;using Dapper;using Dapper.Contrib;using Dapper.Contrib.Extensions;using Models.PkDapper;using SqlSugar;using SyntacticSugar;namespace PkDapper.Demos{  public class Page : IDemos  {     public void Init()    {      Console.WriteLine("分页");      var eachCount = 3000;      /*******************车轮战是性能评估最准确的一种方式***********************/      for (int i = 0; i < 10; i++)      {        //dapper        Dapper(eachCount);        //sqlSugar        SqlSugar(eachCount);      }    }    private static void SqlSugar(int eachCount)    {      GC.Collect();//回收资源      System.Threading.Thread.Sleep(2000);//休息2秒      PerHelper.Execute(eachCount, "SqlSugar", () =>      {        using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))        {          var list = conn.SqlQuery<Test>("select * from(select *,row_number() over(order by id) as r from test ) t where t.r between @b and @e ", new { b = 1000, e = 1010 });        }      });    }    private static void Dapper(int eachCount)    {      GC.Collect();//回收资源      System.Threading.Thread.Sleep(2000);//休息2秒      //正试比拼      PerHelper.Execute(eachCount, "Dapper", () =>      {        using (SqlConnection conn = new SqlConnection(PubConst.connectionString))        {          var list = conn.Query<Test>("select * from(select *,row_number() over(order by id) as r from test ) t where t.r between @b and @e ", new { b = 1000, e = 1010 });        }      });    }  }}

View Code

 

第七场:比拼普通插入

SqlSugar 100分  Dapper 96分

比赛结果: SqlSugar小胜 

 

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;using Dapper;using Dapper.Contrib;using Dapper.Contrib.Extensions;using Models.PkDapper;using SqlSugar;using SyntacticSugar;namespace PkDapper.Demos{  public class InsertItem : IDemos  {    public void Init()    {      Console.WriteLine("测试插入单条记录");      var eachCount = 1000;      /*******************车轮战是性能评估最准确的一种方式***********************/      for (int i = 0; i < 10; i++)      {        //清除        DeleteAddDatas();        //dapper        Dapper(eachCount);        //清除        DeleteAddDatas();        //sqlSugar        SqlSugar(eachCount);      }    }    private static void DeleteAddDatas()    {      using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))      {        conn.Delete<Test>(it => it.F_String == "Test");      }    }    private static Test GetItem    {      get      {          Test t = new Test()          {            F_Int32 = 1,            F_String = "Test",            F_Float = 1,            F_DateTime = DateTime.Now,            F_Byte = 1,            F_Bool = true          };        return t;      }    }    private static void SqlSugar(int eachCount)    {      GC.Collect();//回收资源      System.Threading.Thread.Sleep(2000);//休息2秒      PerHelper.Execute(eachCount, "SqlSugar", () =>      {        using (SqlSugarClient conn = new SqlSugarClient(PubConst.connectionString))        {          var list = conn.Insert(GetItem);        }      });    }    private static void Dapper(int eachCount)    {      GC.Collect();//回收资源      System.Threading.Thread.Sleep(2000);//休息2秒      //正试比拼      PerHelper.Execute(eachCount, "Dapper", () =>      {        using (SqlConnection conn = new SqlConnection(PubConst.connectionString))        {          var list = conn.Insert(GetItem);        }      });    }  }}

View Code

 

 

第八场:比拼普通更新

SqlSugar 90分  Dapper 100分

比赛结果:Dapper小胜

 

总结 

Dapper在批量操作上性能不尽人意,普通查询 增  删 和改 速度可以接受,想了解SqlSugar可以看这篇文章

http://www.cnblogs.com/sunkaixuan/p/5911334.html

 

测试代码:

有兴趣的朋友可以下载测试,也可以方便的加上其它ORM

https://github.com/sunkaixuan/SqlSugar