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小胜
代码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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>(); } }); } } }
第二场:查询单条,主要比拼实体转换以外的性能
SqlSugar 100分 Dapper 100分
比赛结果:平手
代码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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); } }); } } }
第三场:比拼海量数据更新
SqlSugar 100分 Dapper 60分
比赛结果:SqlSugar胜出
代码:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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); } }); } } }
第四场:比拼海量数据插入
SqlSugar 100分 Dapper 12分
比赛结果:SqlSugar胜出
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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); } }); } } }
第五场:比拼批量删除
SqlSugar 100分 Dapper 50分
比赛结果:SqlSugar胜出
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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); } }); } } }
第六场:分页,项目使用最多的场景之一
SqlSugar 96分 Dapper 100分
比赛结果:Dapper小胜
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }); } }); } } }
第七场:比拼普通插入
SqlSugar 100分 Dapper 96分
比赛结果: SqlSugar小胜
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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); } }); } } }
第八场:比拼普通更新
SqlSugar 90分 Dapper 100分
比赛结果:Dapper小胜
总结
Dapper在批量操作上性能不尽人意,普通查询 增 删 和改 速度可以接受,想了解SqlSugar可以看这篇文章
http://www.cnblogs.com/sunkaixuan/p/5911334.html
测试代码:
有兴趣的朋友可以下载测试,也可以方便的加上其它ORM