-
ORM增删改查并发性能测试2
重要补充说明(放在最前面)
博客中EFCore测试结果比其它ORM快的原因
涉及博客《ORM增删改查并发性能测试》《ORM增删改查并发性能测试2》《.NET 6 EFCore WebApi 使用 JMeter 进行吞吐量测试》
测试代码中EFCore的多字段排序写法不正确，导致生成的SQL不正确，SQL中只有Id排序，没有CreateTime字段的排序！
EFCore不正确的多字段排序写法
string remark = "测试";
int id = 20;
var list = await _db.Set<SysUser>().AsNoTracking()
.Where(t => t.Id > id && t.RealName.Contains(remark))
.OrderByDescending(t => t.CreateTime)
.OrderBy(t => t.Id).Skip(1800).Take(200).ToListAsync();
EFCore正确的多字段排序写法
string remark = "测试";
int id = 20;
var list = await _db.Set<SysUser>().AsNoTracking()
.Where(t => t.Id > id && t.RealName.Contains(remark))
.OrderByDescending(t => t.CreateTime)
.ThenBy(t => t.Id).Skip(1800).Take(200).ToListAsync();
重新测试EFCore
测试命令：
第一轮：
跟其它ORM差不多。
第二轮：
跟其它ORM差不多。
代码：
ORM增删改查并发性能测试2
前言
上一篇《ORM增删改查并发性能测试》出现了点小失误，有的输出SQL日志的代码没有禁用，数据库连接字符串可能有问题。统一环境，统一代码后，重新写一篇。
这次重点是并发性能测试，真不是为了diss其它ORM，开始是因为我写的Dapper.LiteSql高并发场景下存在BUG，才写了这个测试，经过修改优化，最好Dapper.LiteSql通过了这个测试。然后Fast.Framework的作者说他的ORM性能很好，我就好奇，是否真的有他说的这么好，口说无凭，我就对它进程各种测试，包括这个高并发测试，刚开始Fast.Framework高并发测试也不通过，后经过作者的优化，就通过了。
本测试不偏向任何ORM，有一说一。如果测试环境或程序有问题，请指出，难免有失误。
测试的现实意义
这两天在对一些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
测试程序中配置的数据连接字符串
Data Source=localhost;Port=3306;User ID=root;Password=123456;Initial Catalog=xxxxxx_test;Charset=utf8mb4;SslMode=none;Allow User Variables=True;
MySql.Data.dll版本
MySql.Data.dll目前用的是最新的8.0.30版本(题外话，它里面的异步是假异步，把同步方法包装成的异步，我说怎么反而慢了呢，真异步要使用MySqlConnector.dll，Fast.Framework使用的是这个性能很好)。
参数化
测试的增删改查，都是参数化的。
实体类
以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;}
}
}
测试配置及代码
Dapper.LiteSql net461
public class LiteSqlFactory
{
#region 变量
private static ILiteSqlClient _liteSqlClient = new LiteSqlClient("Data Source=localhost;Port=3306;User ID=root;Password=123456;Initial Catalog=litesql_test;Charset=utf8mb4;SslMode=none;Allow User Variables=True;", DBType.MySQL, new MySQLProvider());
#endregion
#region 获取 ISession
/// <summary>
/// 获取 ISession
/// </summary>
/// <param name="splitTableMapping">分表映射</param>
public static ISession GetSession(SplitTableMapping splitTableMapping = null)
{
return _liteSqlClient.GetSession(splitTableMapping);
}
#endregion
#region 获取 ISession (异步)
/// <summary>
/// 获取 ISession (异步)
/// </summary>
/// <param name="splitTableMapping">分表映射</param>
public static async Task<ISession> GetSessionAsync(SplitTableMapping splitTableMapping = null)
{
return await _liteSqlClient.GetSessionAsync(splitTableMapping);
}
#endregion
}
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;
private bool _printSql = false;
#endregion
#region Form1
public Form1()
{
InitializeComponent();
}
#endregion
#region Form1_Load
private void Form1_Load(object sender, EventArgs e)
{
RunTask(() =>
{
LiteSqlFactory.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 cbxPrintSql_Click
private void cbxPrintSql_Click(object sender, EventArgs e)
{
_printSql = cbxPrintSql.Checked;
}
#endregion
#region 删除
private void button5_Click(object sender, EventArgs e)
{
RunTask(() =>
{
Log("删除 开始");
var session = LiteSqlFactory.GetSession();
session.CreateSql("id>@Id", 12).DeleteByCondition<SysUser>();
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();
if (_printSql)
{
session.OnExecuting = (s, p) => Console.WriteLine(s); //打印SQL
}
try
{
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();
if (_printSql)
{
session.OnExecuting = (s, p) => Console.WriteLine(s); //打印SQL
}
try
{
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();
if (_printSql)
{
session.OnExecuting = (s, p) => Console.WriteLine(s); //打印SQL
}
try
{
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();
if (_printSql)
{
session.OnExecuting = (s, p) => Console.WriteLine(s); //打印SQL
}
try
{
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 = 1; i <= 1000; i++)
{
int index = 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>();
if (index % 50 == 0) Log("第" + index + "次查询结果 count=" + userList.Count);
});
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
}
}
FreeSql net461
注意：Winform程序能够通过测试，如果是控制台程序，需要把connection timeout设置大一点，才能通过测试。
public class FreeSqlUtil
{
#region CreateFreeSqlClient
public static IFreeSql CreateFreeSqlClient()
{
IFreeSql db = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.MySql, @"Data Source=localhost;Port=3306;User ID=root;Password=123456;Initial Catalog=freesql_test;Charset=utf8mb4;SslMode=none;Allow User Variables=True;")
.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();
if (_printSql)
{
_db.Aop.CurdAfter += CurdAfter;
}
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";
user.CreateTime = DateTime.Now;
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";
user.CreateTime = DateTime.Now;
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 = 1; i <= 1000; i++)
{
int index = 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();
if (index % 50 == 0) Log("第" + index + "次查询结果 count=" + userList.Count);
});
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
}
}
SqlSugar net461
注意：需要把connection timeout设置大一点，才能通过测试。
public class SqlSugarUtil
{
public static readonly string ConnectionString = "Data Source=localhost;Port=3306;User ID=root;Password=123456;Initial Catalog=sqlsugar_test;Charset=utf8mb4;SslMode=none;Allow User Variables=True;connection timeout=600;";
#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 生成实体类
/// <summary>
/// 生成实体类
/// </summary>
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;
private bool _printSql = false;
#endregion
#region Form1
public Form1()
{
InitializeComponent();
}
#endregion
#region Form1_Load
private void Form1_Load(object sender, EventArgs e)
{
_db = SqlSugarUtil.CreateSqlSugarClient();
if (_printSql)
{
_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 cbxPrintSql_Click
private void cbxPrintSql_CheckedChanged(object sender, EventArgs e)
{
_printSql = cbxPrintSql.Checked;
}
#endregion
#region 打印SQL
private void OnLogExecuting(string sql, SugarParameter[] paramArr)
{
if (_printSql)
{
RunTask(() =>
{
//string msg = "SQL：" + sql + "\r\nParam：" + _db.Utilities.SerializeObject(paramArr.ToDictionary(it => it.ParameterName, it => it.Value));
Console.WriteLine(sql);
_log.Debug(sql);
});
}
}
#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";
user.CreateTime = DateTime.Now;
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";
user.CreateTime = DateTime.Now;
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 = 1; i <= 1000; i++)
{
int index = 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();
if (index % 50 == 0) Log("第" + index + "次查询结果 count=" + userList.Count);
});
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
}
}
Entity Framework
数据库连接字符串：
注意：connection timeout要设置大一点，不然会报错。
Data Source=localhost;Port=3306;User ID=root;Password=123456;Initial Catalog=entityframework_test;Charset=utf8mb4;SslMode=none;Allow User Variables=True;connection timeout=600;
DatabaseContext 类：
DbContext不能跨线程使用。
[DbConfigurationType(typeof(MySql.Data.Entity.MySqlEFConfiguration))]
public class DatabaseContext : DbContext
{
public DatabaseContext() : base("name=DefaultConnection")
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
}
public DbSet<SysUser> SysUser { get; set; }
}
测试代码：
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;
namespace EFDemo
{
public partial class Form1 : Form
{
#region 变量
private DatabaseContext _db = new DatabaseContext();
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 = new DatabaseContext();
if (_printSql)
{
_db.Database.Log = OnLogExecuting;
}
RunTask(() =>
{
_db.Set<SysUser>().First(); //预热
Log("预热完成");
});
}
#endregion
private void cbxPrintSql_Click(object sender, EventArgs e)
{
_printSql = cbxPrintSql.Checked;
}
#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());
throw;
}
}, t);
}
#endregion
#region 打印SQL
private void OnLogExecuting(string sql)
{
if (_printSql)
{
RunTask(() =>
{
string msg = "SQL：" + sql;
Console.WriteLine(msg);
});
}
}
#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.Set<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.Set<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.SaveChanges();
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;
_db.Set<SysUser>().AddRange(userList);
_db.SaveChanges();
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.Set<SysUser>().RemoveRange(_db.Set<SysUser>().Where(t => t.Id > 20));
_db.SaveChanges();
Log("删除 完成");
});
}
#endregion
#region 测试循环修改
private void button7_Click(object sender, EventArgs e)
{
RunTask(() =>
{
Random rnd = new Random();
List<SysUser> userList = _db.Set<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.SaveChanges();
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;
_db.Set<SysUser>().AddRange(userList);
_db.SaveChanges();
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("循环添加 完成，耗时：" + time + "秒");
});
}
#endregion
#region 查询
private void button9_Click(object sender, EventArgs e)
{
RunTask(() =>
{
_db.Set<SysUser>().Where(t => t.Id == 1).ToList(); //预热
Log("查询 开始");
DateTime dt = DateTime.Now;
for (int i = 0; i < 10; i++)
{
List<SysUser> userList = _db.Set<SysUser>().Where(t => t.Id > 20 && t.RealName.Contains("测试")).OrderByDescending(t => t.CreateTime).ThenBy(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.Set<SysUser>().Where(t => t.Id == 1).ToList(); //预热
Log("分页查询 开始");
DateTime dt = DateTime.Now;
for (int i = 0; i < 10; i++)
{
int total = _db.Set<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.Set<SysUser>().Where(t => t.Id > 20 && t.RealName.Contains("测试")).OrderByDescending(t => t.CreateTime).ThenBy(t => t.Id).Skip(pageSize * (page - 1)).Take(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 = 1; i <= 1000; i++)
{
int index = i;
Task task = RunTask(() =>
{
DatabaseContext db = new DatabaseContext();
List<SysUser> userList = db.Set<SysUser>()
.Where(t => t.Id > 20 && t.RealName.Contains("测试"))
.OrderByDescending(t => t.CreateTime)
.ThenBy(t => t.Id).ToList();
if (index % 50 == 0) Log("第" + index + "次查询结果 count=" + userList.Count);
});
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 =>
{
DatabaseContext db = new DatabaseContext();
db.Set<SysUser>().Add(user);
db.SaveChanges();
}, item);
tasks.Add(task);
}
Task.WaitAll(tasks.ToArray());
string time = DateTime.Now.Subtract(dt).TotalSeconds.ToString("0.000");
Log("并发插入 完成，耗时：" + time + "秒");
});
}
#endregion
}
}
Fast.Framework 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 _printSql = 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 cbxPrintSql_Click
private void cbxPrintSql_Click(object sender, EventArgs e)
{
_printSql = cbxPrintSql.Checked;
}
#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 = "Data Source=localhost;Port=3306;User ID=root;Password=123456;Initial Catalog=fast_framework_test;Charset=utf8mb4;SslMode=none;Allow User Variables=True;"
}
});
if (_printSql)
{
_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;
try
{
foreach (SysUser user in userList)
{
user.Remark = "测试修改用户" + _rnd.Next(1, 10000);
user.UpdateUserid = "1";
user.UpdateTime = DateTime.Now;
}
await GetDbContext().Update(userList).ExceuteAsync();
}
catch
{
//todo:没有rollback?
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;
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 = 1; i <= 1000; i++)
{
int index = 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();
if (index % 50 == 0) Log("第" + index + "次查询结果 count=" + userList.Count);
});
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
}
}
Dapper.LiteSql net6
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 ILiteSqlClient _db;
private bool _printSql = false;
#endregion
#region Form1
public Form1()
{
InitializeComponent();
}
#endregion
#region Form1_Load
private void Form1_Load(object sender, EventArgs e)
{
_db = new LiteSqlClient("Data Source=localhost;Port=3306;User ID=root;Password=123456;Initial Catalog=litesql_test;Charset=utf8mb4;SslMode=none;Allow User Variables=True;",
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 cbxPrintSql_Click
private void cbxPrintSql_Click(object sender, EventArgs e)
{
_printSql = cbxPrintSql.Checked;
}
#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 = 1; i <= 1000; i++)
{
int index = 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();
if (index % 50 == 0) Log("第" + index + "次查询结果 count=" + userList.Count);
});
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
}
}
Java MyBatis-Plus
插入和修改使用MyBatis-Plus，查询写了JdbcTemplate和MyBatis-Plus两个版本
Service层代码
/**
* @filename:SysUserServiceImpl 2022年6月7日
* @project jdbc-template-test V1.0
* Copyright(c) 2018 sux Co. Ltd.
* All right reserved.
*/
package com.sux.jdbctest.service.impl;
import com.sux.jdbctest.entity.SysUser;
import com.sux.jdbctest.dao.SysUserDao;
import com.sux.jdbctest.service.SysUserService;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.stereotype.Service;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.transaction.annotation.Transactional;
import javax.annotation.Resource;
import java.util.*;
/**
* @Description:TODO(用户表服务实现)
* @version: V1.0
* @author: sux
*/
@Service
public class SysUserServiceImpl extends ServiceImpl<SysUserDao, SysUser> implements SysUserService {
@Resource(name = "transactionManager")
private DataSourceTransactionManager transactionManager;
/**
* 循环插入测试
*/
@Transactional(rollbackFor = Exception.class)
public void loopInsert(List<SysUser> userList) {
for (SysUser user : userList) {
save(user);
}
}
/**
* 循环修改测试
*/
@Transactional(rollbackFor = Exception.class)
public void loopUpdate(List<SysUser> userList) {
for (SysUser user : userList) {
updateById(user);
}
}
/**
* 批量插入测试
*/
@Transactional(rollbackFor = Exception.class)
public void batchInsert(List<SysUser> userList) {
saveBatch(userList);
}
/**
* 批量修改测试
*/
@Transactional(rollbackFor = Exception.class)
public void batchUpdate(List<SysUser> userList) {
updateBatchById(userList);
}
}
控制台测试代码
package com.sux.jdbctest.task;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.sux.jdbctest.entity.SysUser;
import com.sux.jdbctest.service.SysUserService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.annotation.Order;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;
import java.util.*;
@Order(1)
@Component
public class OnAppStart implements CommandLineRunner {
private static final Logger log = LoggerFactory.getLogger(OnAppStart.class);
@Autowired
private SysUserService sysUserService;
@Autowired
@Qualifier("primaryJdbcTemplate")
protected JdbcTemplate primaryJdbcTemplate;
private int _count = 10000;
@Override
public void run(String... args) throws Exception {
System.out.println("测试开始");
try {
QueryWrapper<SysUser> wrapper = new QueryWrapper<>();
wrapper.last("limit 0,1");
List<SysUser> list = sysUserService.list(wrapper);
System.out.println("list.size()=" + list.size());
List<SysUser> list2 = primaryJdbcTemplate.query("select * from sys_user limit 0,1", new BeanPropertyRowMapper<>(SysUser.class));
System.out.println("list2.size()=" + list2.size());
} catch (Exception e) {
System.out.println("测试异常：");
e.printStackTrace();
}
System.out.println("预热完成");
Scanner scanner = new Scanner(System.in);
System.out.println("请输入操作 1:循环插入 2:循环修改 3:批量修改 4:使用JdbcTemplate查询 5:使用MyBatis-Plus查询 6:批量插入");
while (true) {
try {
String ope = scanner.next();
if (ope.equals("1")) {
loopInsertTest();
} else if (ope.equals("2")) {
loopUpdateTest();
} else if (ope.equals("3")) {
batchUpdateTest();
} else if (ope.equals("4")) {
queryTestByJdbcTemplate();
} else if (ope.equals("5")) {
queryTestByMybatisPlus();
} else if (ope.equals("6")) {
batchInsertTest();
}
} catch (Exception e) {
System.out.println("测试异常：");
e.printStackTrace();
}
}
}
/**
* 循环插入测试
*/
public void loopInsertTest() {
List<SysUser> userList = new ArrayList<>();
for (int i = 1; i <= _count; i++) {
SysUser user = new SysUser();
user.setUserName("testUser");
user.setRealName("测试插入用户" + i);
user.setPassword("123456");
user.setCreateUserid("1");
user.setCreateTime(new Date());
userList.add(user);
}
System.out.println("循环添加 开始 count=" + userList.size());
long startTime = System.currentTimeMillis();
sysUserService.loopInsert(userList);
double time = (System.currentTimeMillis() - startTime) / 1000.0;
System.out.println("循环添加 完成，耗时：" + String.format("%.3f", time) + " 秒");
}
/**
* 循环修改测试
*/
public void loopUpdateTest() {
QueryWrapper<SysUser> wrapper = new QueryWrapper<>();
wrapper.and(qw -> qw.gt("id", 20));
List<SysUser> userList = sysUserService.list(wrapper);
for (SysUser user : userList) {
user.setRemark("测试修改用户" + String.format("%.0f", Math.random() * 10000));
user.setUpdateUserid("1");
user.setUpdateTime(new Date());
}
System.out.println("循环修改 开始 count=" + userList.size());
long startTime = System.currentTimeMillis();
sysUserService.loopUpdate(userList);
double time = (System.currentTimeMillis() - startTime) / 1000.0;
System.out.println("循环修改 完成，耗时：" + String.format("%.3f", time) + " 秒");
}
/**
* 批量插入测试
*/
public void batchInsertTest() {
List<SysUser> userList = new ArrayList<>();
for (int i = 1; i <= _count; i++) {
SysUser user = new SysUser();
user.setUserName("testUser");
user.setRealName("测试插入用户" + i);
user.setPassword("123456");
user.setCreateUserid("1");
user.setCreateTime(new Date());
userList.add(user);
}
System.out.println("批量添加 开始 count=" + userList.size());
long startTime = System.currentTimeMillis();
sysUserService.batchInsert(userList);
double time = (System.currentTimeMillis() - startTime) / 1000.0;
System.out.println("批量添加 完成，耗时：" + String.format("%.3f", time) + " 秒");
}
/**
* 批量修改测试
*/
public void batchUpdateTest() {
QueryWrapper<SysUser> wrapper = new QueryWrapper<>();
wrapper.and(qw -> qw.gt("id", 20));
List<SysUser> userList = sysUserService.list(wrapper);
for (SysUser user : userList) {
user.setRemark("测试修改用户" + String.format("%.0f", Math.random() * 10000));
user.setUpdateUserid("1");
user.setUpdateTime(new Date());
}
System.out.println("批量修改 开始 count=" + userList.size());
long startTime = System.currentTimeMillis();
sysUserService.batchUpdate(userList);
double time = (System.currentTimeMillis() - startTime) / 1000.0;
System.out.println("批量修改 完成，耗时：" + String.format("%.3f", time) + " 秒");
}
/**
* 查询测试(使用JdbcTemplate)
*/
public void queryTestByJdbcTemplate() {
System.out.println("使用JdbcTemplate查询 开始");
long startTime = System.currentTimeMillis();
MapSqlParameterSource param = new MapSqlParameterSource();
param.addValue("id", 20);
param.addValue("remark", "%测试%");
NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(primaryJdbcTemplate);
for (int i = 0; i < 10; i++) {
List<SysUser> list = namedParameterJdbcTemplate.query("select * from sys_user " +
"where id > (:id) and remark like (:remark)" +
"order by create_time desc, id asc", param, new BeanPropertyRowMapper(SysUser.class));
System.out.println("查询结果 count=" + list.size());
}
double time = (System.currentTimeMillis() - startTime) / 1000.0;
System.out.println("使用JdbcTemplate查询 完成，耗时：" + String.format("%.3f", time) + " 秒");
}
/**
* 查询测试(使用mybatis-plus)
*/
public void queryTestByMybatisPlus() {
System.out.println("使用mybatis-plus查询 开始");
long startTime = System.currentTimeMillis();
QueryWrapper<SysUser> wrapper = new QueryWrapper<>();
wrapper.and(qw -> qw.gt("id", 20))
.like("remark", "%测试%")
.orderByDesc("create_time")
.orderByAsc("id");
for (int i = 0; i < 10; i++) {
List<SysUser> list = sysUserService.list(wrapper);
System.out.println("查询结果 count=" + list.size());
}
double time = (System.currentTimeMillis() - startTime) / 1000.0;
System.out.println("使用mybatis-plus查询 完成，耗时：" + String.format("%.3f", time) + " 秒");
}
}
测试结果
常规测试
Dapper.LiteSql net461
FreeSql net461
SqlSugar net461
Entity Framework
Fast.Framework net6
除了MySql.Data.dll还引用了MySqlConnector.dll (为什么net6？它不支持net framework)
Dapper.LiteSql net6
EFCore net6
Java MyBatis-Plus
插入和修改使用MyBatis-Plus，查询写了JdbcTemplate和MyBatis-Plus两个版本
循环插入
批量插入
循环修改
批量修改
使用JdbcTemplate查询
使用MyBatis-Plus查询
MyBatis-Plus默认开启一级缓存？
结论
循环插入、循环修改、批量插入、批量修改，Java的一点也不慢，总体和C#的一个档次。
Java的查询似乎有缓存，MyBatis-Plus默认是开启一级缓存的，很快；JdbcTemplate应该是没有缓存的，相比C#的普遍在1秒内，是慢一些，但也没有慢太多。
并发查询
Dapper.LiteSql net461
FreeSql net461
SqlSugar net461
Entity Framework
Fast.Framework net6
除了MySql.Data.dll还引用了MySqlConnector.dll (为什么net6？它不支持net framework，环境不同，所以这个测试结果供参考，就不要计较了)
Dapper.LiteSql net6
(为了和Fast.Framework对比，确实慢，慢70%左右，可能是MySqlConnector.dll的功劳，它是真异步，只用MySql.Data.dll是假异步)
EFCore net6
并发插入
Dapper.LiteSql net461
FreeSql net461
SqlSugar net461
Entity Framework
Fast.Framework net6
除了MySql.Data.dll还引用了MySqlConnector.dll (为什么net6？它不支持net framework)
Dapper.LiteSql net6
(为了和Fast.Framework对比)
EFCore net6
使用jMeter测试
非常对不住大家，我写Winform测试并发性能，不专业，我刚下载了jMeter进行了测试，这次只测了Dapper.LiteSql和SqlSugar，都没有问题。
为了更接近真实场景，这次是写的WebApi测试的，服务端没有ThreadPool.SetMinThreads(200, 200)这么变态的设置了。
本次测试查询数据的代码(有模糊查询、有排序、分页查询第10页200条数据，不过份吧？):
var list = _db.Queryable<SysUser>()
.Where(t => t.Id > 20 && t.RealName.Contains("测试"))
.OrderBy(t => t.CreateTime, OrderByType.Desc)
.OrderBy(t => t.Id).ToPageList(10, 200);
jMeter设置
1000个线程，循环10次
Dapper.LiteSql测试结果
SqlSugar测试结果
测试结果说明
反复测试了很多次，结果大差不差。这次只测了查询，没有测试插入和修改，有兴趣的可以自己试试，毕竟jMeter应该比较权威，WebApi代码不多，很好写的。再次说明，测试最主要是为了发现BUG，性能是其次，因为刚开始时，我的Dapper.LiteSql，一压测就报错，所以才有了这个测试。
测试总结
经过我使用jMeter反复测试，得出一个结论：
要想撑的住高并发，
- 如果静态类的静态方法，涉及到锁，尽量不要使用静态类。我太喜欢写静态类了，我写的静态类LiteSqlFactory，按我的测试代码实测，能撑的住200线程的并发，但有时撑不住1000线程的并发，就是当线程数达到1000时，会有一部分请求报异常，一部分请求正常。
-
服务需要进行充分预热，服务启动并简单预热后，使用jMeter进行1000个线程的高并发测试，会有几率撑不住，导致部分请求报异常。此时服务经过一轮高并发请求，已经被充分预热，清空测试记录，再测试一次就正常了，而且性能更好。
补充说明：报异常时，服务并没有崩溃，只有刚开始的小部分请求报异常，稍后的请求全部正常。也就是说高并发情况下，如果有1000个用户并发请求，可能800个用户成功了，200个用户报错了，被告知稍后再试，并且稍后再试就可以成功。
以上是通过使用jMeter对Dapper.LiteSql和SqlSugar进行测试得出的结论。
写在最后
我不偏向任何ORM，有一说一。ORM的选择，性能固然重要，但性能差一点也不影响使用，主要是为了测试有没有硬伤，比如某个操作，一个ORM需要2秒，另一个ORM需要50秒，那50秒这个，慢了一个数量级还多，是不是有问题？再比如说某个测试，别的ORM，有耗时20秒的，有耗时30秒的，也有耗时70秒的，都问题不大，但有一个ORM，它报错了，是不是个问题？
这次并发测试，EF和EFCore我没有测试，因为我比较相信官方，可能它会慢一点，但因为用户量特别多，应该不会有硬伤。有兴趣的可以拿我的测试代码，稍微改一下测试测试EF和EFCore。
我自己写的LiteSql，算是个历史遗留问题，好几年前从上家公司的DBHelper一路改过来的，除非我把它完全删了，否则总想把它改好，这就需要测试，以保证质量。我因为写LiteSql，学了一些东西，技术上多多少少有进步，这就是意义。
出处：https://www.cnblogs.com/s0611163/p/16685697.html
栏目列表
最新更新
nodejs爬虫
Python正则表达式完全指南
爬取豆瓣Top250图书数据
shp 地图文件批量添加字段
爬虫小试牛刀（爬取学校通知公告）
【python基础】函数-初识函数
【python基础】函数-返回值
HTTP请求：requests模块基础使用必知必会
Python初学者友好丨详解参数传递类型
如何有效管理爬虫流量？
2个场景实例讲解GaussDB(DWS)基表统计信息估
常用的 SQL Server 关键字及其含义
动手分析SQL Server中的事务中使用的锁
openGauss内核分析：SQL by pass & 经典执行
一招教你如何高效批量导入与更新数据
天天写SQL，这些神奇的特性你知道吗？
openGauss内核分析：执行计划生成
[IM002]Navicat ODBC驱动器管理器 未发现数据
初入Sql Server 之 存储过程的简单使用
SQL Server -- 解决存储过程传入参数作为s
关于JS定时器的整理
JS中使用Promise.all控制所有的异步请求都完
js中字符串的方法
import-local执行流程与node模块路径解析流程
检测数据类型的四种方法
js中数组的方法,32种方法
前端操作方法
数据类型
window.localStorage.setItem 和 localStorage.setIte
如何完美解决前端数字计算精度丢失与数
