Dapper ORM VS SqlSugar ORM的 8場對決，dapperorm
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
