ORM增刪改查並行效能測試
2022-09-11 18:03:09
這兩天在對一些ORM進行效能測試(涉及SqlSugar、FreeSql、Fast.Framework、Dapper.LiteSql)，測試用的是Winform程式，別人第一眼看到我的程式，說，你這測試沒意義！
可能我的測試程式的某些地方寫的比較變態吧，但我認為有現實意義，並且網上有相關網站崩潰問題的文章，那什麼「爆高分析」，「崩潰問題」，WinDbg用的很6，那寫程式階段為什麼沒發現呢？
場景
假設WebApi(或者說網站後臺服務)正在被高並行存取，一段時間後，服務的執行緒數量達到了200及以上，那麼.NET的執行緒池，如果執行緒處於空閒狀態，預設是20秒後釋放執行緒，假設這200多個執行緒空閒了，但是還沒有達到空閒20秒的時間，還沒有釋放，如果此時，又有大量高並行的存取，200個執行緒去運算元據庫，那就可能很危險了。
關於效能
雖然程式碼寫的可能有點變態，但我的測試不是很嚴格，也不是很規範。
效能如果不是數量級的差，問題都不大，比如一個是0.8秒，一個是1.8秒，那1.8秒的慢是慢了一點，但問題不大；但如果一個是0.8秒，一個是10秒，那10秒的這個，可能就有點問題了，即使別人用的Emit你用的反射，也不應該差這麼多。
當然了，我的測試，資料規模不大，10000條，button可能會點10次，那也才10萬資料量，在資料量方面我沒有做極端測試，我的重點不是這個，因為很少會去查100萬資料到記憶體中，就暫不討論這個。
這個測試除了大體上測試一下效能，主要就是增加了並行測試。
非並行效能測試的截圖我就不放全了，這幾個ORM有差別，但我覺得2秒3秒還是6秒甚至8秒，都問題不大的，都算差不多，都算堪用。
測試程式碼
測試資料庫是MySQL版本5.7.28，MySql的連線池大小是1200，MySql.Data.dll目前用的是最新的8.0.30版本(題外話，它裡面的非同步是假非同步，把同步方法包裝成的非同步，我說怎麼反而慢了呢，真非同步要使用MySqlConnector.dll，Fast.Framework使用的是這個效能很好)。
1. 資料庫連線字串
server=localhost;database=sqlsugar_test;user id=root;password=123456;character set=utf8mb4;
這裡沒有設定min pool size和max pool size，也沒有設定connection timeout，沒有設定的話connection timeout預設是15秒。
2. 在寫Winform做高並行測試的時候，有個引數可能需要設定
<system.net>
<connectionManagement>
<add address="*" maxconnection="1200" />
</connectionManagement>
</system.net>
測試高並行網路請求的時候，要設定這個，那麼資料庫增刪改查要不要設定呢？反正就一個引數，加或不加，可以試試。
3. 實體類
以SqlSugar的為例吧，幾種ORM測試用的表結構是相同的。
using System;
using System.Linq;
using System.Text;
using SqlSugar;
namespace Models
{
///<summary>
///使用者表
///</summary>
[SugarTable("sys_user")]
public partial class SysUser
{
public SysUser(){
}
/// <summary>
/// Desc:主鍵
/// Default:
/// Nullable:False
/// </summary>
[SugarColumn(IsPrimaryKey=true,IsIdentity=true,ColumnName="id")]
public long Id {get;set;}
/// <summary>
/// Desc:使用者名稱
/// Default:
/// Nullable:False
/// </summary>
[SugarColumn(ColumnName="user_name")]
public string UserName {get;set;}
/// <summary>
/// Desc:使用者姓名
/// Default:
/// Nullable:True
/// </summary>
[SugarColumn(ColumnName="real_name")]
public string RealName {get;set;}
/// <summary>
/// Desc:使用者密碼
/// Default:
/// Nullable:False
/// </summary>
[SugarColumn(ColumnName="password")]
public string Password {get;set;}
/// <summary>
/// Desc:備註
/// Default:
/// Nullable:True
/// </summary>
[SugarColumn(ColumnName="remark")]
public string Remark {get;set;}
/// <summary>
/// Desc:建立者ID
/// Default:
/// Nullable:False
/// </summary>
[SugarColumn(ColumnName="create_userid")]
public string CreateUserid {get;set;}
/// <summary>
/// Desc:建立時間
/// Default:
/// Nullable:False
/// </summary>
[SugarColumn(ColumnName="create_time")]
public DateTime CreateTime {get;set;}
/// <summary>
/// Desc:更新者ID
/// Default:
/// Nullable:True
/// </summary>
[SugarColumn(ColumnName="update_userid")]
public string UpdateUserid {get;set;}
/// <summary>
/// Desc:更新時間
/// Default:
/// Nullable:True
/// </summary>
[SugarColumn(ColumnName="update_time")]
public DateTime? UpdateTime {get;set;}
}
}
4. 測試介面，感受一下
5. FreeSql測試程式碼
public class FreeSqlUtil
{
#region CreateFreeSqlClient
public static IFreeSql CreateFreeSqlClient()
{
IFreeSql db = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.MySql, @"server=localhost;database=freesql_test;user id=root;password=123456;character set=utf8mb4;")
.UseGenerateCommandParameterWithLambda(true)
.Build(); //請務必定義成 Singleton 單例模式
return db;
}
#endregion
}
using Models;
using NLog;
using FreeSqlDemo.Utils;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using FreeSql.Aop;
using Newtonsoft.Json;
using System.Threading;
namespace FreeSqlDemo
{
public partial class Form1 : Form
{
#region 變數
private Logger _log = NLog.LogManager.GetCurrentClassLogger();
private IFreeSql _db;
private int _count = 10000;
private bool _printSql = false;
#endregion
#region Form1
public Form1()
{
InitializeComponent();
}
#endregion
#region Form1_Load
private void Form1_Load(object sender, EventArgs e)
{
_db = FreeSqlUtil.CreateFreeSqlClient();
_db.Aop.CurdAfter += CurdAfter;
_printSql = cbxPrintSql.Checked;
RunTask(() =>
{
_db.Queryable<SysUser>().Count(); //預熱
Log("預熱完成");
});
}
#endregion
#region Log
private void Log(string log)
{
if (!this.IsDisposed)
{
string msg = DateTime.Now.ToString("mm:ss.fff") + " " + log + "\r\n\r\n";
if (this.InvokeRequired)
{
this.BeginInvoke(new Action(() =>
{
textBox1.AppendText(msg);
}));
}
else
{
textBox1.AppendText(msg);
}
}
}
#endregion
#region 清空輸出框
private void button10_Click(object sender, EventArgs e)
{
textBox1.Text = string.Empty;
}
#endregion
#region RunTask
private Task RunTask(Action action)
{
return Task.Run(() =>
{
try
{
action();
}
catch (Exception ex)
{
Log(ex.Message + "\r\n" + ex.StackTrace);
}
});
}
private Task RunTask<T>(Action<T> action, T t)
{
return Task.Factory.StartNew(obj =>
{
try
{
action((T)obj);
}
catch (Exception ex)
{
Log(ex.ToString());
throw;
}
}, t);
}
#endregion
#region 列印SQL
private void CurdAfter(object sender, CurdAfterEventArgs e)
{
if (_printSql)
{
RunTask(() =>
{
string msg = "SQL：" + e.Sql + "\r\nParam：" + JsonConvert.SerializeObject(e.DbParms.ToDictionary(it => it.ParameterName, it => it.Value));
Console.WriteLine(msg);
_log.Debug(msg);
});
}
}
#endregion
#region cbxPrintSql_Click
private void cbxPrintSql_Click(object sender, EventArgs e)
{
_printSql = cbxPrintSql.Checked;
}
#endregion
#region 生成實體類
private void button1_Click(object sender, EventArgs e)
{
RunTask(() =>
{
Log("開始生成實體類");
Log("生成實體類完成");
});
}
#endregion
#region 測試查詢
private void button2_Click(object sender, EventArgs e)
{
Log("開始查詢");
SysUser conditionModel = new SysUser();
conditionModel.Remark = "管理員";
string remark = "管理員";
List<SysUser> list = _db.Queryable<SysUser>().Where(t => t.Id < 20 && t.Remark.Contains(conditionModel.Remark) && t.CreateTime > new DateTime(2010, 1, 1)).ToList();
foreach (SysUser user in list)
{
Log(string.Format("{0}，{1}，{2}，{3}", user.UserName, user.RealName, user.Remark, user.CreateTime.ToString("yyyy-MM-dd")));
}
Log("查詢結束 count=" + list.Count);
}
#endregion
#region 測試批次修改
private void button3_Click(object sender, EventArgs e)
{
RunTask(() =>
{
Random rnd = new Random();
List<SysUser> userList = _db.Queryable<SysUser>().Where(t => t.Id > 20).ToList();
Log("批次修改 開始 count=" + userList.Count.ToString("0 000"));
foreach (SysUser user in userList)
{
user.Remark = "測試修改使用者" + rnd.Next(0, 10000);
user.UpdateUserid = "1";
user.UpdateTime = DateTime.Now;
}
DateTime dt = DateTime.Now;
_db.Ado.Transaction(() =>
{
_db.Update<SysUser>().SetSource(userList).ExecuteAffrows();
});
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("批次修改 完成，耗時：" + time + "秒");
});
}
#endregion
#region 測試批次新增
private void button4_Click(object sender, EventArgs e)
{
RunTask(() =>
{
List<SysUser> userList = new List<SysUser>();
for (int i = 1; i <= _count; i++)
{
SysUser user = new SysUser();
user.UserName = "testUser";
user.RealName = "測試插入使用者";
user.Password = "123456";
user.CreateUserid = "1";
userList.Add(user);
}
Log("批次新增 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
_db.Ado.Transaction(() =>
{
_db.Insert(userList).ExecuteAffrows();
});
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("批次新增 結束，完成：" + time + "秒");
});
}
#endregion
#region 刪除
private void button5_Click(object sender, EventArgs e)
{
RunTask(() =>
{
Log("刪除 開始");
_db.Delete<SysUser>().Where(t => t.Id > 20).ExecuteAffrows();
Log("刪除 完成");
});
}
#endregion
#region 測試迴圈修改
private void button7_Click(object sender, EventArgs e)
{
RunTask(() =>
{
Random rnd = new Random();
List<SysUser> userList = _db.Queryable<SysUser>().Where(t => t.Id > 20).ToList();
Log("迴圈修改 開始 count=" + userList.Count.ToString("0 000"));
DateTime dt = DateTime.Now;
_db.Ado.Transaction(() =>
{
var repo = _db.GetRepository<SysUser>();
foreach (SysUser user in userList)
{
repo.Attach(user);
}
foreach (SysUser user in userList)
{
user.Remark = "測試修改使用者" + rnd.Next(0, 10000);
user.UpdateUserid = "1";
user.UpdateTime = DateTime.Now;
}
foreach (SysUser user in userList)
{
repo.Update(user);
}
});
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("迴圈修改 完成，耗時：" + time + "秒");
});
}
#endregion
#region 測試迴圈新增
private void button6_Click(object sender, EventArgs e)
{
RunTask(() =>
{
List<SysUser> userList = new List<SysUser>();
for (int i = 1; i <= _count; i++)
{
SysUser user = new SysUser();
user.UserName = "testUser";
user.RealName = "測試插入使用者";
user.Password = "123456";
user.CreateUserid = "1";
userList.Add(user);
}
Log("迴圈新增 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
_db.Ado.Transaction(() =>
{
foreach (SysUser user in userList)
{
_db.Insert(user).ExecuteIdentity();
}
});
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("迴圈新增 完成，耗時：" + time + "秒");
});
}
#endregion
#region 查詢
private void button9_Click(object sender, EventArgs e)
{
RunTask(() =>
{
_db.Queryable<SysUser>().Where(t => t.Id == 1).ToList(); //預熱
Log("查詢 開始");
DateTime dt = DateTime.Now;
for (int i = 0; i < 10; i++)
{
List<SysUser> userList = _db.Queryable<SysUser>().Where(t => t.Id > 20 && t.RealName.Contains("測試")).OrderByDescending(t => t.CreateTime).OrderBy(t => t.Id).ToList();
Log("查詢結果 count=" + userList.Count.ToString());
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("查詢 完成，耗時：" + time + "秒");
});
}
#endregion
#region 分頁查詢
private void button8_Click(object sender, EventArgs e)
{
RunTask(() =>
{
_db.Queryable<SysUser>().Where(t => t.Id == 1).ToList(); //預熱
Log("分頁查詢 開始");
DateTime dt = DateTime.Now;
for (int i = 0; i < 10; i++)
{
long total = _db.Queryable<SysUser>().Count();
int pageSize = 100;
int pageCount = (int)((total - 1) / pageSize + 1);
List<SysUser> userList = new List<SysUser>();
for (int page = 1; page <= pageCount; page++)
{
userList.AddRange(_db.Queryable<SysUser>().Where(t => t.Id > 20 && t.RealName.Contains("測試")).OrderByDescending(t => t.CreateTime).OrderBy(t => t.Id).Page(page, pageSize).ToList());
}
Log("分頁查詢結果 count=" + userList.Count.ToString());
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("分頁查詢 完成，耗時：" + time + "秒");
});
}
#endregion
#region 並行查詢
private void button11_Click(object sender, EventArgs e)
{
ThreadPool.SetMaxThreads(1000, 1000);
ThreadPool.SetMinThreads(200, 200);
RunTask(() =>
{
Log("並行查詢 開始");
DateTime dt = DateTime.Now;
List<Task> tasks = new List<Task>();
for (int i = 0; i < _count; i++)
{
Task task = RunTask(() =>
{
List<SysUser> userList = _db.Queryable<SysUser>()
.Where(t => t.Id > 20 && t.RealName.Contains("測試"))
.OrderByDescending(t => t.CreateTime).OrderBy(t => t.Id).ToList();
Log("查詢結果 count=" + userList.Count.ToString());
});
tasks.Add(task);
}
Task.WaitAll(tasks.ToArray());
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("並行查詢 完成，耗時：" + time + "秒");
});
}
#endregion
#region 並行插入
private void button12_Click(object sender, EventArgs e)
{
ThreadPool.SetMaxThreads(1000, 1000);
ThreadPool.SetMinThreads(200, 200);
RunTask(() =>
{
List<SysUser> userList = new List<SysUser>();
for (int i = 1; i <= _count; i++)
{
SysUser user = new SysUser();
user.UserName = "testUser";
user.RealName = "測試插入使用者";
user.Remark = "測試插入使用者" + i;
user.Password = "123456";
user.CreateUserid = "1";
user.CreateTime = DateTime.Now;
userList.Add(user);
}
Log("並行插入 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
List<Task> tasks = new List<Task>();
foreach (SysUser item in userList)
{
var task = RunTask(user =>
{
_db.Insert(user).ExecuteIdentity();
}, item);
tasks.Add(task);
}
Task.WaitAll(tasks.ToArray());
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("並行插入 完成，耗時：" + time + "秒");
});
}
#endregion
}
}
6. SqlSugar測試程式碼
public class SqlSugarUtil
{
public static readonly string ConnectionString = "server=localhost;database=sqlsugar_test;user id=root;password=123456;character set=utf8mb4;";
#region CreateSqlSugarClient
public static SqlSugarScope CreateSqlSugarClient()
{
SqlSugarScope db = new SqlSugarScope(new ConnectionConfig()
{
ConnectionString = ConnectionString,//連線符字串
DbType = SqlSugar.DbType.MySql, //資料庫型別
IsAutoCloseConnection = true //不設成true要手動close
});
return db;
}
#endregion
#region CreateModels 生成實體類
public static void CreateModels(SqlSugarClient db, string tableName = null)
{
......此處省略
}
#endregion
}
using Models;
using NLog;
using SqlSugar;
using SqlSugarDemo.Utils;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace SqlSugarDemo
{
public partial class Form1 : Form
{
#region 變數
private Logger _log = NLog.LogManager.GetCurrentClassLogger();
private SqlSugarScope _db;
private int _count = 10000;
#endregion
#region Form1
public Form1()
{
InitializeComponent();
}
#endregion
#region Form1_Load
private void Form1_Load(object sender, EventArgs e)
{
_db = SqlSugarUtil.CreateSqlSugarClient();
_db.Aop.OnLogExecuting = OnLogExecuting;
RunTask(() =>
{
_db.Queryable<SysUser>().Count(); //預熱
Log("預熱完成");
});
}
#endregion
#region Log
private void Log(string log)
{
if (!this.IsDisposed)
{
string msg = DateTime.Now.ToString("mm:ss.fff") + " " + log + "\r\n\r\n";
if (this.InvokeRequired)
{
this.BeginInvoke(new Action(() =>
{
textBox1.AppendText(msg);
}));
}
else
{
textBox1.AppendText(msg);
}
}
}
#endregion
#region 清空輸出框
private void button10_Click(object sender, EventArgs e)
{
textBox1.Text = string.Empty;
}
#endregion
#region RunTask
private Task RunTask(Action action)
{
return Task.Run(() =>
{
try
{
action();
}
catch (Exception ex)
{
Log(ex.Message + "\r\n" + ex.StackTrace);
}
});
}
private Task RunTask<T>(Action<T> action, T t)
{
return Task.Factory.StartNew(obj =>
{
try
{
action((T)obj);
}
catch (Exception ex)
{
Log(ex.ToString());
}
}, t);
}
#endregion
#region 列印SQL
private void OnLogExecuting(string sql, SugarParameter[] paramArr)
{
RunTask(() =>
{
string msg = "SQL：" + sql + "\r\nParam：" + _db.Utilities.SerializeObject(paramArr.ToDictionary(it => it.ParameterName, it => it.Value));
Console.WriteLine(msg);
_log.Debug(msg);
});
}
#endregion
#region 生成實體類
private void button1_Click(object sender, EventArgs e)
{
RunTask(() =>
{
Log("開始生成實體類");
SqlSugarUtil.CreateModels(new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = SqlSugarUtil.ConnectionString,//連線符字串
DbType = SqlSugar.DbType.MySql, //資料庫型別
IsAutoCloseConnection = true //不設成true要手動close
}));
Log("生成實體類完成");
});
}
#endregion
#region 測試查詢
private void button2_Click(object sender, EventArgs e)
{
Log("開始查詢");
SysUser conditionModel = new SysUser();
conditionModel.Remark = "管理員";
string remark = "管理員";
List<SysUser> list = _db.Queryable<SysUser>().Where(t => t.Id < 20 && t.Remark.Contains(conditionModel.Remark) && t.CreateTime > new DateTime(2010, 1, 1)).ToList();
foreach (SysUser user in list)
{
Log(string.Format("{0}，{1}，{2}，{3}", user.UserName, user.RealName, user.Remark, user.CreateTime.ToString("yyyy-MM-dd")));
}
Log("查詢結束 count=" + list.Count);
}
#endregion
#region 測試批次修改
private void button3_Click(object sender, EventArgs e)
{
RunTask(() =>
{
Random rnd = new Random();
List<SysUser> userList = _db.Queryable<SysUser>().Where(t => t.Id > 20).ToList();
Log("批次修改 開始 count=" + userList.Count.ToString("0 000"));
foreach (SysUser user in userList)
{
user.Remark = "測試修改使用者" + rnd.Next(0, 10000);
user.UpdateUserid = "1";
user.UpdateTime = DateTime.Now;
}
DateTime dt = DateTime.Now;
try
{
_db.Ado.BeginTran();
_db.Updateable(userList).ExecuteCommand();
_db.Ado.CommitTran();
}
catch (Exception ex)
{
_db.Ado.RollbackTran();
throw ex;
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("批次修改 完成，耗時：" + time + "秒");
});
}
#endregion
#region 測試批次新增
private void button4_Click(object sender, EventArgs e)
{
RunTask(() =>
{
List<SysUser> userList = new List<SysUser>();
for (int i = 1; i <= _count; i++)
{
SysUser user = new SysUser();
user.UserName = "testUser";
user.RealName = "測試插入使用者";
user.Password = "123456";
user.CreateUserid = "1";
userList.Add(user);
}
Log("批次新增 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
try
{
_db.Ado.BeginTran();
_db.Insertable(userList).ExecuteCommand();
_db.Ado.CommitTran();
}
catch (Exception ex)
{
_db.Ado.RollbackTran();
throw ex;
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("批次新增 結束，完成：" + time + "秒");
});
}
#endregion
#region 刪除
private void button5_Click(object sender, EventArgs e)
{
RunTask(() =>
{
Log("刪除 開始");
_db.Deleteable<SysUser>(t => t.Id > 20).ExecuteCommand();
Log("刪除 完成");
});
}
#endregion
#region 測試迴圈修改
private void button7_Click(object sender, EventArgs e)
{
RunTask(() =>
{
Random rnd = new Random();
List<SysUser> userList = _db.Queryable<SysUser>().Where(t => t.Id > 20).ToList();
Log("迴圈修改 開始 count=" + userList.Count.ToString("0 000"));
foreach (SysUser user in userList)
{
user.Remark = "測試修改使用者" + rnd.Next(0, 10000);
user.UpdateUserid = "1";
user.UpdateTime = DateTime.Now;
}
DateTime dt = DateTime.Now;
try
{
_db.Ado.BeginTran();
foreach (SysUser user in userList)
{
_db.Updateable(user).ExecuteCommand();
}
_db.Ado.CommitTran();
}
catch (Exception ex)
{
_db.Ado.RollbackTran();
throw ex;
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("迴圈修改 完成，耗時：" + time + "秒");
});
}
#endregion
#region 測試迴圈新增
private void button6_Click(object sender, EventArgs e)
{
RunTask(() =>
{
List<SysUser> userList = new List<SysUser>();
for (int i = 1; i <= _count; i++)
{
SysUser user = new SysUser();
user.UserName = "testUser";
user.RealName = "測試插入使用者";
user.Password = "123456";
user.CreateUserid = "1";
userList.Add(user);
}
Log("迴圈新增 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
try
{
_db.Ado.BeginTran();
foreach (SysUser user in userList)
{
_db.Insertable(user).ExecuteCommand();
}
_db.Ado.CommitTran();
}
catch (Exception ex)
{
_db.Ado.RollbackTran();
throw ex;
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("迴圈新增 完成，耗時：" + time + "秒");
});
}
#endregion
#region 查詢
private void button9_Click(object sender, EventArgs e)
{
RunTask(() =>
{
_db.Queryable<SysUser>().Where(t => t.Id == 1).ToList(); //預熱
Log("查詢 開始");
DateTime dt = DateTime.Now;
for (int i = 0; i < 10; i++)
{
List<SysUser> userList = _db.Queryable<SysUser>().Where(t => t.Id > 20 && t.RealName.Contains("測試")).OrderBy(t => t.CreateTime, OrderByType.Desc).OrderBy(t => t.Id).ToList();
Log("查詢結果 count=" + userList.Count.ToString());
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("查詢 完成，耗時：" + time + "秒");
});
}
#endregion
#region 分頁查詢
private void button8_Click(object sender, EventArgs e)
{
RunTask(() =>
{
_db.Queryable<SysUser>().Where(t => t.Id == 1).ToList(); //預熱
Log("分頁查詢 開始");
DateTime dt = DateTime.Now;
for (int i = 0; i < 10; i++)
{
int total = _db.Queryable<SysUser>().Count();
int pageSize = 100;
int pageCount = (total - 1) / pageSize + 1;
List<SysUser> userList = new List<SysUser>();
for (int page = 1; page <= pageCount; page++)
{
userList.AddRange(_db.Queryable<SysUser>().Where(t => t.Id > 20 && t.RealName.Contains("測試")).OrderBy(t => t.CreateTime, OrderByType.Desc).OrderBy(t => t.Id).ToPageList(page, pageSize));
}
Log("分頁查詢結果 count=" + userList.Count.ToString());
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("分頁查詢 完成，耗時：" + time + "秒");
});
}
#endregion
#region 並行查詢
private void button11_Click(object sender, EventArgs e)
{
ThreadPool.SetMaxThreads(1000, 1000);
ThreadPool.SetMinThreads(200, 200);
RunTask(() =>
{
Log("並行查詢 開始");
DateTime dt = DateTime.Now;
List<Task> tasks = new List<Task>();
for (int i = 0; i < 200; i++)
{
Task task = RunTask(() =>
{
List<SysUser> userList = _db.Queryable<SysUser>().Where(t => t.Id > 20 && t.RealName.Contains("測試")).OrderBy(t => t.CreateTime, OrderByType.Desc).OrderBy(t => t.Id).ToList();
Log("查詢結果 count=" + userList.Count.ToString());
});
tasks.Add(task);
}
Task.WaitAll(tasks.ToArray());
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("並行查詢 完成，耗時：" + time + "秒");
});
}
#endregion
#region 並行插入
private void button12_Click(object sender, EventArgs e)
{
ThreadPool.SetMaxThreads(1000, 1000);
ThreadPool.SetMinThreads(200, 200);
RunTask(() =>
{
List<SysUser> userList = new List<SysUser>();
for (int i = 1; i <= _count; i++)
{
SysUser user = new SysUser();
user.UserName = "testUser";
user.RealName = "測試插入使用者";
user.Remark = "測試插入使用者" + i;
user.Password = "123456";
user.CreateUserid = "1";
user.CreateTime = DateTime.Now;
userList.Add(user);
}
Log("並行插入 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
List<Task> tasks = new List<Task>();
foreach (SysUser item in userList)
{
var task = RunTask(user =>
{
_db.Insertable(user).ExecuteCommand();
}, item);
tasks.Add(task);
}
Task.WaitAll(tasks.ToArray());
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("並行插入 完成，耗時：" + time + "秒");
});
}
#endregion
}
}
8. Fast.Framework測試程式碼(.NET6，Dapper.LiteSql .NET Framework 4.5.2和.NET6各寫了一份測試，實測插入和更新速度差不多，查詢.NET6會稍微快一些)
using Fast.Framework;
using Fast.Framework.Interfaces;
using Fast.Framework.Models;
using Fast.Framework.Extensions;
using Fast.Framework.Aop;
using Models;
namespace FastFrameworkDemo
{
public partial class Form1 : Form
{
#region 變數
private Random _rnd = new Random();
private int _count = 10000;
private bool _showSqlLog = false;
#endregion
#region Form1
public Form1()
{
InitializeComponent();
}
#endregion
#region Form1_Load
private void Form1_Load(object sender, EventArgs e)
{
RunTask(async () =>
{
await GetDbContext().Query<SysUser>().CountAsync();
Log("預熱完成");
});
}
#endregion
#region Log
private void Log(string log)
{
if (!this.IsDisposed)
{
string msg = DateTime.Now.ToString("mm:ss.fff") + " " + log + "\r\n\r\n";
if (this.InvokeRequired)
{
this.BeginInvoke(new Action(() =>
{
textBox1.AppendText(msg);
}));
}
else
{
textBox1.AppendText(msg);
}
}
}
#endregion
#region 清空輸出框
private void button10_Click(object sender, EventArgs e)
{
textBox1.Text = string.Empty;
}
#endregion
#region RunTask
private Task RunTask(Action action)
{
return Task.Run(() =>
{
try
{
action();
}
catch (Exception ex)
{
Log(ex.ToString());
}
});
}
private Task RunTask<T>(Action<T> action, T t)
{
return Task.Factory.StartNew(obj =>
{
try
{
action((T)obj);
}
catch (Exception ex)
{
Log(ex.ToString());
}
}, t);
}
#endregion
#region GetDbContext
private IDbContext GetDbContext()
{
IDbContext _db = new DbContext(new List<DbOptions>() {
new DbOptions()
{
DbId = "1",
DbType = DbType.MySQL,
ProviderName = "MySqlConnector",
FactoryName = "MySqlConnector.MySqlConnectorFactory,MySqlConnector",
ConnectionStrings = "server=localhost;database=fast_framework_test;user id=root;password=123456;character set=utf8mb4;"
}
});
if (_showSqlLog)
{
_db.Aop.DbLog = (sql, dp) =>
{
Console.WriteLine($"執行Sql:{sql}");
if (dp != null)
{
foreach (var item in dp)
{
Console.WriteLine($"引數名稱:{item.ParameterName} 引數值:{item.Value}");
}
}
};
}
return _db;
}
#endregion
#region 刪除
private void button1_Click(object sender, EventArgs e)
{
RunTask(async () =>
{
Log("刪除 開始");
await GetDbContext().Delete<SysUser>().Where(t => t.Id > 20).ExceuteAsync();
Log("刪除 完成");
});
}
#endregion
#region 批次修改
private void button2_Click(object sender, EventArgs e)
{
RunTask(async () =>
{
List<SysUser> userList = await GetDbContext().Query<SysUser>().Where(t => t.Id > 20).ToListAsync();
Log("批次修改 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
foreach (SysUser user in userList)
{
user.Remark = "測試修改使用者" + _rnd.Next(1, 10000);
user.UpdateUserid = "1";
user.UpdateTime = DateTime.Now;
}
await GetDbContext().Update(userList).ExceuteAsync();
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("批次修改 完成，耗時：" + time + "秒");
});
}
#endregion
#region 批次新增
private void button3_Click(object sender, EventArgs e)
{
RunTask(async () =>
{
List<SysUser> userList = new List<SysUser>();
for (int i = 1; i <= _count; i++)
{
SysUser user = new SysUser();
user.UserName = "testUser";
user.RealName = "測試插入使用者";
user.Password = "123456";
user.CreateUserid = "1";
user.CreateTime = DateTime.Now;
userList.Add(user);
}
Log("批次新增 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
try
{
await GetDbContext().Insert(userList).ExceuteAsync();
}
catch
{
//todo:沒有rollback?
throw;
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("批次新增 完成，耗時：" + time + "秒");
});
}
#endregion
#region 迴圈修改
private void button4_Click(object sender, EventArgs e)
{
RunTask(async () =>
{
List<SysUser> userList = await GetDbContext().Query<SysUser>().Where(t => t.Id > 20).ToListAsync();
Log("迴圈修改 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
try
{
foreach (SysUser user in userList)
{
user.Remark = "測試修改使用者" + _rnd.Next(1, 10000);
user.UpdateUserid = "1";
user.UpdateTime = DateTime.Now;
}
var db = GetDbContext();
await db.Ado.BeginTranAsync();
foreach (SysUser user in userList)
{
await db.Update(user).ExceuteAsync();
}
await db.Ado.CommitTranAsync();
}
catch
{
//todo:沒有rollback?
throw;
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("迴圈修改 完成，耗時：" + time + "秒");
});
}
#endregion
#region 迴圈新增
private void button5_Click(object sender, EventArgs e)
{
RunTask(async () =>
{
List<SysUser> userList = new List<SysUser>();
for (int i = 1; i <= _count; i++)
{
SysUser user = new SysUser();
user.UserName = "testUser";
user.RealName = "測試插入使用者";
user.Password = "123456";
user.CreateUserid = "1";
user.CreateTime = DateTime.Now;
userList.Add(user);
}
Log("迴圈新增 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
try
{
var db = GetDbContext();
await db.Ado.BeginTranAsync();
foreach (SysUser user in userList)
{
await db.Insert(user).ExceuteAsync();
}
await db.Ado.CommitTranAsync();
}
catch
{
//todo:沒有rollback?
throw;
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("迴圈新增 完成，耗時：" + time + "秒");
});
}
#endregion
#region 查詢
private void button6_Click(object sender, EventArgs e)
{
RunTask(async () =>
{
Log("查詢 開始");
DateTime dt = DateTime.Now;
var db = GetDbContext();
for (int i = 0; i < 10; i++)
{
List<SysUser> userList = await db.Query<SysUser>()
.Where(t => t.Id > 20 && t.RealName.Contains("%測試%"))
.OrderBy("create_time", "desc")
.OrderBy("id", "asc").ToListAsync();
Log("查詢結果 count=" + userList.Count.ToString());
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("查詢 完成，耗時：" + time + "秒");
});
}
#endregion
#region 分頁查詢
private void button7_Click(object sender, EventArgs e)
{
Log("尚未實現");
}
#endregion
#region 並行查詢
private void button8_Click(object sender, EventArgs e)
{
RunTask(() =>
{
ThreadPool.SetMaxThreads(1000, 1000);
ThreadPool.SetMinThreads(200, 200);
Log("並行查詢 開始");
DateTime dt = DateTime.Now;
List<Task> tasks = new List<Task>();
for (int i = 0; i < 200; i++)
{
Task task = RunTask(async () =>
{
List<SysUser> userList = await GetDbContext().Query<SysUser>()
.Where(t => t.Id > 20 && t.RealName.Contains("%測試%"))
.OrderBy("create_time", "desc")
.OrderBy("id", "asc").ToListAsync();
Log("查詢結果 count=" + userList.Count.ToString());
});
tasks.Add(task);
}
Task.WaitAll(tasks.ToArray());
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("並行查詢 完成，耗時：" + time + "秒");
});
}
#endregion
#region 並行插入
private void button9_Click(object sender, EventArgs e)
{
RunTask(() =>
{
ThreadPool.SetMaxThreads(1000, 1000);
ThreadPool.SetMinThreads(200, 200);
List<SysUser> userList = new List<SysUser>();
for (int i = 1; i <= _count; i++)
{
SysUser user = new SysUser();
user.UserName = "testUser";
user.RealName = "測試插入使用者";
user.Remark = "測試插入使用者" + i;
user.Password = "123456";
user.CreateUserid = "1";
user.CreateTime = DateTime.Now;
userList.Add(user);
}
Log("並行插入 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
List<Task> tasks = new List<Task>();
foreach (SysUser item in userList)
{
var task = RunTask(async user =>
{
await GetDbContext().Insert(user).ExceuteAsync();
}, item);
tasks.Add(task);
}
Task.WaitAll(tasks.ToArray());
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("並行插入 完成，耗時：" + time + "秒");
});
}
#endregion
}
}
9. Dapper.LiteSql(還有一個不依賴Dapper的版本，那個插入和更新效能差不多，查詢因為用的是反射，大約慢50%)
using DAL;
using Dapper.LiteSql;
using Models;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using Utils;
namespace PerformanceTest
{
public partial class Form1 : Form
{
#region 變數
private BsOrderDal m_BsOrderDal = ServiceHelper.Get<BsOrderDal>();
private SysUserDal m_SysUserDal = ServiceHelper.Get<SysUserDal>();
private Random _rnd = new Random();
private int _count = 10000;
#endregion
#region Form1
public Form1()
{
InitializeComponent();
}
#endregion
#region Form1_Load
private void Form1_Load(object sender, EventArgs e)
{
RunTask(() =>
{
m_BsOrderDal.Preheat(); //預熱
Log("預熱完成");
});
}
#endregion
#region Log
private void Log(string log)
{
if (!this.IsDisposed)
{
string msg = DateTime.Now.ToString("mm:ss.fff") + " " + log + "\r\n\r\n";
if (this.InvokeRequired)
{
this.BeginInvoke(new Action(() =>
{
textBox1.AppendText(msg);
}));
}
else
{
textBox1.AppendText(msg);
}
}
}
#endregion
#region 清空輸出框
private void button10_Click(object sender, EventArgs e)
{
textBox1.Text = string.Empty;
}
#endregion
#region RunTask
private Task RunTask(Action action)
{
return Task.Run(() =>
{
try
{
action();
}
catch (Exception ex)
{
Log(ex.ToString());
throw;
}
});
}
private Task RunTask<T>(Action<T> action, T t)
{
return Task.Factory.StartNew(obj =>
{
try
{
action((T)obj);
}
catch (Exception ex)
{
Log(ex.ToString());
throw;
}
}, t);
}
#endregion
#region 刪除
private void button5_Click(object sender, EventArgs e)
{
RunTask(() =>
{
Log("刪除 開始");
var session = LiteSqlFactory.GetSession();
session.DeleteByCondition<SysUser>(string.Format("id>=12"));
Log("刪除 完成");
});
}
#endregion
#region 測試批次修改
private void button3_Click(object sender, EventArgs e)
{
RunTask(() =>
{
List<SysUser> userList = m_SysUserDal.GetList("select t.* from sys_user t where t.id > 20");
Log("批次修改 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
var session = LiteSqlFactory.GetSession();
try
{
session.OnExecuting = (sql, param) =>
{
Console.WriteLine(sql); //列印SQL
};
session.AttachOld(userList);
foreach (SysUser user in userList)
{
user.Remark = "測試修改使用者" + _rnd.Next(1, 10000);
user.UpdateUserid = "1";
user.UpdateTime = DateTime.Now;
}
userList.ForEach(item => item.UpdateTime = DateTime.Now);
session.BeginTransaction();
session.Update(userList);
session.CommitTransaction();
}
catch (Exception ex)
{
session.RollbackTransaction();
throw;
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("批次修改 完成，耗時：" + time + "秒");
});
}
#endregion
#region 測試批次新增
private void button4_Click(object sender, EventArgs e)
{
RunTask(() =>
{
List<SysUser> userList = new List<SysUser>();
for (int i = 1; i <= _count; i++)
{
SysUser user = new SysUser();
user.UserName = "testUser";
user.RealName = "測試插入使用者";
user.Password = "123456";
user.CreateUserid = "1";
user.CreateTime = DateTime.Now;
userList.Add(user);
}
Log("批次新增 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
var session = LiteSqlFactory.GetSession();
try
{
session.OnExecuting = (s, p) => Console.WriteLine(s); //列印SQL
session.BeginTransaction();
session.Insert(userList);
session.CommitTransaction();
}
catch
{
session.RollbackTransaction();
throw;
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("批次新增 完成，耗時：" + time + "秒");
});
}
#endregion
#region 測試迴圈修改
private void button7_Click(object sender, EventArgs e)
{
RunTask(() =>
{
List<SysUser> userList = m_SysUserDal.GetList("select t.* from sys_user t where t.id > 20");
Log("迴圈修改 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
var session = LiteSqlFactory.GetSession();
try
{
session.OnExecuting = (sql, param) =>
{
Console.WriteLine(sql); //列印SQL
};
session.AttachOld(userList);
foreach (SysUser user in userList)
{
user.Remark = "測試修改使用者" + _rnd.Next(1, 10000);
user.UpdateUserid = "1";
user.UpdateTime = DateTime.Now;
}
session.BeginTransaction();
foreach (SysUser user in userList)
{
session.Update(user);
}
session.CommitTransaction();
}
catch
{
session.RollbackTransaction();
throw;
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("迴圈修改 完成，耗時：" + time + "秒");
});
}
#endregion
#region 測試迴圈新增
private void button6_Click(object sender, EventArgs e)
{
RunTask(() =>
{
List<SysUser> userList = new List<SysUser>();
for (int i = 1; i <= _count; i++)
{
SysUser user = new SysUser();
user.UserName = "testUser";
user.RealName = "測試插入使用者";
user.Password = "123456";
user.CreateUserid = "1";
user.CreateTime = DateTime.Now;
userList.Add(user);
}
Log("迴圈新增 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
var session = LiteSqlFactory.GetSession();
try
{
session.OnExecuting = (sql, param) =>
{
Console.WriteLine(sql); //列印SQL
};
session.BeginTransaction();
foreach (SysUser user in userList)
{
session.Insert(user);
}
session.CommitTransaction();
}
catch
{
session.RollbackTransaction();
throw;
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("迴圈新增 完成，耗時：" + time + "秒");
});
}
#endregion
#region 查詢
private void button1_Click(object sender, EventArgs e)
{
RunTask(() =>
{
Log("查詢 開始");
DateTime dt = DateTime.Now;
for (int i = 0; i < 10; i++)
{
var session = LiteSqlFactory.GetSession();
ISqlString sql = session.CreateSql(@"
select t.*
from sys_user t
where t.id > @id
and t.real_name like @remark", 20, "%測試%");
sql.Append(" order by t.create_time desc, t.id asc");
List<SysUser> userList = sql.QueryList<SysUser>();
Log("查詢結果 count=" + userList.Count.ToString());
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("查詢 完成，耗時：" + time + "秒");
});
}
#endregion
#region 分頁查詢
private void button2_Click(object sender, EventArgs e)
{
RunTask(() =>
{
Log("分頁查詢 開始");
DateTime dt = DateTime.Now;
for (int i = 0; i < 10; i++)
{
int total = m_SysUserDal.GetTotalCount();
int pageSize = 100;
int pageCount = (total - 1) / pageSize + 1;
var session = LiteSqlFactory.GetSession();
List<SysUser> userList = new List<SysUser>();
for (int page = 1; page <= pageCount; page++)
{
ISqlString sql = session.CreateSql(@"
select t.*
from sys_user t
where 1=1
and t.id > @id
and t.real_name like @remark", 20, "%測試%");
string orderby = " order by t.create_time desc, t.id asc";
userList.AddRange(sql.QueryPage<SysUser>(orderby, pageSize, page));
}
Log("分頁查詢結果 count=" + userList.Count.ToString());
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("分頁查詢 完成，耗時：" + time + "秒");
});
}
#endregion
#region 並行查詢
private void button8_Click(object sender, EventArgs e)
{
ThreadPool.SetMaxThreads(1000, 1000);
ThreadPool.SetMinThreads(200, 200);
RunTask(() =>
{
Log("並行查詢 開始");
DateTime dt = DateTime.Now;
List<Task> tasks = new List<Task>();
for (int i = 0; i < 200; i++)
{
Task task = RunTask(() =>
{
var session = LiteSqlFactory.GetSession();
ISqlString sql = session.CreateSql(@"
select t.*
from sys_user t
where t.id > @id
and t.real_name like @remark", 20, "%測試%");
sql.Append(" order by t.create_time desc, t.id asc");
List<SysUser> userList = sql.QueryList<SysUser>();
Log("查詢結果 count=" + userList.Count.ToString());
});
tasks.Add(task);
}
Task.WaitAll(tasks.ToArray());
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("並行查詢 完成，耗時：" + time + "秒");
});
}
#endregion
#region 並行插入
private void button9_Click(object sender, EventArgs e)
{
ThreadPool.SetMaxThreads(1000, 1000);
ThreadPool.SetMinThreads(200, 200);
RunTask(() =>
{
List<SysUser> userList = new List<SysUser>();
for (int i = 1; i <= _count; i++)
{
SysUser user = new SysUser();
user.UserName = "testUser";
user.RealName = "測試插入使用者";
user.Remark = "測試插入使用者" + i;
user.Password = "123456";
user.CreateUserid = "1";
user.CreateTime = DateTime.Now;
userList.Add(user);
}
Log("並行插入 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
List<Task> tasks = new List<Task>();
foreach (SysUser item in userList)
{
var task = RunTask(user =>
{
LiteSqlFactory.GetSession().Insert(user);
}, item);
tasks.Add(task);
}
Task.WaitAll(tasks.ToArray());
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("並行插入 完成，耗時：" + time + "秒");
});
}
#endregion
}
}
.NET6版本(為了和Fast.Framewor對比，用了非同步，不過我用的MySql.Data.dll是假非同步，Fast.Framework用的MySqlConnector是真非同步)：
using Dapper.LiteSql;
using Models;
using Provider;
namespace DapperLiteSqlDemo
{
public partial class Form1 : Form
{
#region 變數
private Random _rnd = new Random();
private int _count = 10000;
private bool _showSqlLog = false;
private static ILiteSqlClient _db;
#endregion
#region Form1
public Form1()
{
InitializeComponent();
}
#endregion
#region Form1_Load
private void Form1_Load(object sender, EventArgs e)
{
ThreadPool.SetMaxThreads(1000, 1000);
ThreadPool.SetMinThreads(200, 200);
_db = new LiteSqlClient("server=localhost;database=litesql_test;user id=root;password=123456;character set=utf8mb4;",
DBType.MySQL, new MySQLProvider());
RunTask(() =>
{
_db.GetSession(); //預熱
Log("預熱完成");
});
}
#endregion
#region Log
private void Log(string log)
{
if (!this.IsDisposed)
{
string msg = DateTime.Now.ToString("mm:ss.fff") + " " + log + "\r\n\r\n";
if (this.InvokeRequired)
{
this.BeginInvoke(new Action(() =>
{
textBox1.AppendText(msg);
}));
}
else
{
textBox1.AppendText(msg);
}
}
}
#endregion
#region 清空輸出框
private void button10_Click(object sender, EventArgs e)
{
textBox1.Text = string.Empty;
}
#endregion
#region RunTask
private Task RunTask(Action action)
{
return Task.Run(() =>
{
try
{
action();
}
catch (Exception ex)
{
Log(ex.ToString());
throw;
}
});
}
private Task RunTask<T>(Action<T> action, T t)
{
return Task.Factory.StartNew(obj =>
{
try
{
action((T)obj);
}
catch (Exception ex)
{
Log(ex.ToString());
throw;
}
}, t);
}
#endregion
#region 刪除
private void button1_Click(object sender, EventArgs e)
{
RunTask(async () =>
{
Log("刪除 開始");
await _db.GetSession().CreateSql("id>@Id", 20).DeleteByConditionAsync<SysUser>();
Log("刪除 完成");
});
}
#endregion
#region 批次修改
private void button2_Click(object sender, EventArgs e)
{
RunTask(async () =>
{
List<SysUser> userList = await _db.GetSession().Queryable<SysUser>().Where(t => t.Id > 20).ToListAsync();
Log("批次修改 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
ISession? session = null;
try
{
foreach (SysUser user in userList)
{
user.Remark = "測試修改使用者" + _rnd.Next(1, 10000);
user.UpdateUserid = "1";
user.UpdateTime = DateTime.Now;
}
session = await _db.GetSessionAsync();
session.BeginTransaction();
await session.UpdateAsync(userList);
session.CommitTransaction();
}
catch
{
session?.RollbackTransaction();
throw;
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("批次修改 完成，耗時：" + time + "秒");
});
}
#endregion
#region 批次新增
private void button3_Click(object sender, EventArgs e)
{
RunTask(async () =>
{
List<SysUser> userList = new List<SysUser>();
for (int i = 1; i <= _count; i++)
{
SysUser user = new SysUser();
user.UserName = "testUser";
user.RealName = "測試插入使用者";
user.Password = "123456";
user.CreateUserid = "1";
user.CreateTime = DateTime.Now;
userList.Add(user);
}
Log("批次新增 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
ISession? session = null;
try
{
session = await _db.GetSessionAsync();
session.BeginTransaction();
await session.InsertAsync(userList);
session.CommitTransaction();
}
catch
{
session?.RollbackTransaction();
throw;
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("批次新增 完成，耗時：" + time + "秒");
});
}
#endregion
#region 迴圈修改
private void button4_Click(object sender, EventArgs e)
{
RunTask(async () =>
{
List<SysUser> userList = await _db.GetSession().Queryable<SysUser>().Where(t => t.Id > 20).ToListAsync();
Log("迴圈修改 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
ISession? session = null;
try
{
foreach (SysUser user in userList)
{
user.Remark = "測試修改使用者" + _rnd.Next(1, 10000);
user.UpdateUserid = "1";
user.UpdateTime = DateTime.Now;
}
session = await _db.GetSessionAsync();
session.BeginTransaction();
foreach (SysUser user in userList)
{
await session.UpdateAsync(user);
}
session.CommitTransaction();
}
catch
{
session?.RollbackTransaction();
throw;
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("迴圈修改 完成，耗時：" + time + "秒");
});
}
#endregion
#region 迴圈新增
private void button5_Click(object sender, EventArgs e)
{
RunTask(async () =>
{
List<SysUser> userList = new List<SysUser>();
for (int i = 1; i <= _count; i++)
{
SysUser user = new SysUser();
user.UserName = "testUser";
user.RealName = "測試插入使用者";
user.Password = "123456";
user.CreateUserid = "1";
user.CreateTime = DateTime.Now;
userList.Add(user);
}
Log("迴圈新增 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
ISession? session = null;
try
{
session = await _db.GetSessionAsync();
session.BeginTransaction();
foreach (SysUser user in userList)
{
await session.InsertAsync(user);
}
session.CommitTransaction();
}
catch
{
session?.RollbackTransaction();
throw;
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("迴圈新增 完成，耗時：" + time + "秒");
});
}
#endregion
#region 查詢
private void button6_Click(object sender, EventArgs e)
{
RunTask(async () =>
{
Log("查詢 開始");
DateTime dt = DateTime.Now;
for (int i = 0; i < 10; i++)
{
List<SysUser> userList = await _db.GetSession().Queryable<SysUser>()
.Where(t => t.Id > 20 && t.RealName.Contains("%測試%"))
.OrderByDescending(t => t.CreateTime)
.OrderBy(t => t.Id).ToListAsync();
Log("查詢結果 count=" + userList.Count.ToString());
}
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("查詢 完成，耗時：" + time + "秒");
});
}
#endregion
#region 分頁查詢
private void button7_Click(object sender, EventArgs e)
{
Log("尚未實現");
}
#endregion
#region 並行查詢
private void button8_Click(object sender, EventArgs e)
{
RunTask(() =>
{
ThreadPool.SetMaxThreads(1000, 1000);
ThreadPool.SetMinThreads(200, 200);
Log("並行查詢 開始");
DateTime dt = DateTime.Now;
List<Task> tasks = new List<Task>();
for (int i = 0; i < 200; i++)
{
Task task = RunTask(async () =>
{
List<SysUser> userList = await _db.GetSession().Queryable<SysUser>()
.Where(t => t.Id > 20 && t.RealName.Contains("%測試%"))
.OrderByDescending(t => t.CreateTime)
.OrderBy(t => t.Id).ToListAsync();
Log("查詢結果 count=" + userList.Count.ToString());
});
tasks.Add(task);
}
Task.WaitAll(tasks.ToArray());
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("並行查詢 完成，耗時：" + time + "秒");
});
}
#endregion
#region 並行插入
private void button9_Click(object sender, EventArgs e)
{
RunTask(() =>
{
ThreadPool.SetMaxThreads(1000, 1000);
ThreadPool.SetMinThreads(200, 200);
List<SysUser> userList = new List<SysUser>();
for (int i = 1; i <= _count; i++)
{
SysUser user = new SysUser();
user.UserName = "testUser";
user.RealName = "測試插入使用者";
user.Remark = "測試插入使用者" + i;
user.Password = "123456";
user.CreateUserid = "1";
user.CreateTime = DateTime.Now;
userList.Add(user);
}
Log("並行插入 開始 count=" + userList.Count);
DateTime dt = DateTime.Now;
List<Task> tasks = new List<Task>();
foreach (SysUser item in userList)
{
var task = RunTask(async user =>
{
await _db.GetSession().InsertAsync(user);
}, item);
tasks.Add(task);
}
Task.WaitAll(tasks.ToArray());
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("並行插入 完成，耗時：" + time + "秒");
});
}
#endregion
}
}
部分測試結果
迴圈插入、迴圈修改和查詢
真的差不多。
並行插入，全部通過
Fast.Framework還在優化我用的臨時版本
SqlSugar和Fast.Framework的測試結果，就是我這篇部落格要講的的重點了，並行，肯定要加鎖，並行大的時候，鎖們可能在相互等待，幹一會活，歇一會，導致白白浪費幾秒，甚至幾十秒。
正常的是類似這樣的，任務來了，就幹活：
不正常的是類似這樣的(我寫的Dapper.LiteSql，以前是使用using的沒問題，後來為了幹掉using，就加了連線池，就用到鎖，鎖的顆粒度大，就是像下面這樣樣子，比這還過份，後來把鎖的粒度搞小一點，有改善，但還是不行，最後接著優化，除了用執行緒安全佇列外(它底層也是鎖)，能不用鎖就不用鎖(有時候不用鎖實現不了功能，又必須用)，就解決了這個問題)：
耗時的全是外部呼叫：
並行查詢測試
我懷疑MySqlConnector的非同步有BUG，Fast.Framework測試程式，明明用了Task.WaitAll(tasks.ToArray());，別人「並行查詢 完成」都是最後輸出，它還沒完就輸出了，這個3秒是不對的，實際是5秒，確實快，是不是有一部分是MySqlConnector真非同步的功勞呢？
FreeSql就出了問題了，卡死了，報錯。我之前大致看過它這個ObjectPool原始碼，沒仔細研究，沒太看懂，就覺得寫的挺牛X，我還不太會。
結論
有問題很正常，我也只是測試了一種惡劣情況，說不定別人測試其它的惡劣情況，Dapper.LiteSql就報錯了。
後續
這種寫法是不是會好一點，try catch要加的，不過try catch可能也會有影響吧。
List<Task> tasks = new List<Task>();
foreach (SysUser item in userList)
{
var task = Task.Factory.StartNew(async user =>
{
try
{
await _db.GetSession().InsertAsync(user);
}
catch (Exception ex)
{
Log(ex.ToString());
}
}, item);
tasks.Add(task);
}
Task.WaitAll(tasks.ToArray());
Fast.Framework的
List<Task> tasks = new List<Task>();
foreach (SysUser item in userList)
{
var task = Task.Factory.StartNew(async user =>
{
try
{
await GetDbContext().Insert((SysUser)user).ExceuteAsync();
}
catch (Exception ex)
{
Log(ex.ToString());
}
}, item);
tasks.Add(task);
}
Task.WaitAll(tasks.ToArray());
