跳至主要內容

性能

nicye大约 4 分钟约 1097 字

性能

FreeSql 实现了强大功能的同时,性能没有受到影响,项目中使用反射或耗时的操作都经过了缓存处理。读取数据部分采用了 ExpressionTree,使得 FreeSql 解析实体数据的速度与 Dapper 非常接近。

插入测试

测试结果(52 个字段)

18W1W5K2K1K50010050
MySql 5.5 ExecuteAffrows38,4812,2341,1362842391676630
MySql 5.5 ExecuteMySqlBulkCopy28,4051,1426574514355924722
SqlServer Express ExecuteAffrows402,35524,84711,4654,9712,43791513888
SqlServer Express ExecuteSqlBulkCopy21,065578326139105796048
PostgreSQL 10 ExecuteAffrows46,7563,2942,2691,0193742095137
PostgreSQL 10 ExecutePgCopy10,09058333713688613025
Oracle XE ExecuteAffrows----24,52810,648571200
Sqlite ExecuteAffrows28,5541,149701327155914435

18W 解释:插入 18 万行记录,表格中的数字是执行时间(单位 ms)

Oracle 插入性能不用怀疑,可能安装学生版限制较大

提醒:开源数据库测试结果比较有意义,商业数据库版本之间性能可能有较大差距

测试结果(10 个字段)

18W1W5K2K1K50010050
MySql 5.5 ExecuteAffrows11,1718663668083502434
MySql 5.5 ExecuteMySqlBulkCopy6,504399257116871001616
SqlServer Express ExecuteAffrows47,2042,2751,1084882791233516
SqlServer Express ExecuteSqlBulkCopy4,248127713048141110
PostgreSQL 10 ExecuteAffrows9,7865683361571023496
PostgreSQL 10 ExecutePgCopy4,0811679339211242
Oracle XE ExecuteAffrows----2,3947316733
Sqlite ExecuteAffrows4,5242461379435191411

测试结果,是在相同操作系统下进行的,并且都有预热

ExecuteMySqlBulkCopy 方法在 FreeSql.Provider.MySqlConnector 中实现的

//测试实体类
public class TestInsert10c {
    [Column(MapType = typeof(string))]
    public Guid Id { get; set; }

    public string UserName0 { get; set; }
    public string PassWord0 { get; set; }
    public DateTime CreateTime0 { get; set; }

    public string UserName1 { get; set; }
    public string PassWord1 { get; set; }
    public DateTime CreateTime1 { get; set; }

    public string UserName2 { get; set; }
    public string PassWord2 { get; set; }
    public DateTime CreateTime2 { get; set; }
}

//生成测试数据
IFreeSql orm = ...;
var testCount = 10000;
var t10cs = new List<TestInsert10c>();
for (var a = 0; a < testCount; a++) {
    var item = new TestInsert10c();
    for (var b = 0; b <= 2; b++)
    {
        orm.SetEntityValueWithPropertyName(typeof(TestInsert10c), item, "UserName" + b, Guid.NewGuid().ToString("N"));
        orm.SetEntityValueWithPropertyName(typeof(TestInsert10c), item, "PassWord" + b, Guid.NewGuid().ToString("N"));
        orm.SetEntityValueWithPropertyName(typeof(TestInsert10c), item, "CreateTime" + b, DateTime.Now);
    }
    t10cs.Add(item);
}

查询测试

IFreeSql mysql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(FreeSql.DataType.MySql, "Data Source=127.0.0.1;Port=3306;User ID=root;Password=root;Initial Catalog=cccddd;Charset=utf8;SslMode=none;Max pool size=100")
    //由于null会默认输出日志到控制台,影响测试结果。这里传入一个空的日志输出对象
    .UseAutoSyncStructure(false)
    //关闭自动迁移功能
    .Build(); //请务必定义成 Singleton 单例模式

class Song {
    public int Id { get; set; }
    public string Title { get; set; }
    public string Url { get; set; }
    public DateTime Create_time { get; set; }
    public bool Is_deleted { get; set; }
}

测试方法:运行两次,以第二次性能报告,避免了首个运行慢不公平的情况。

测试结果

数量Query<Class>Query<Tuple>Query<dynamic>
Dapper.Query(sql)131072623.4959ms424.2411ms644.8897ms
FreeSql.Query(sql)131072647.0552ms577.3532ms944.7454ms
FreeSql.ToList131072622.8980ms435.3532ms-

FreeSql 以微小的性能差距输了,原因是支持了更多的类型,某些类型解析需要 Parse、递归或循环处理。

由于 Dapper 没有批量插入/更新/删除的功能,并且都是执行一条 SQL 命令,测试结果没有意义。

FreeSql 批量插入使用的命令:INSERT INTO Song (...) VALUES(...),VALUES(...),VALUES(...)...

执行 SQL 返回实体列表 Dapper.Query<Class> VS FreeSql.Query<Class>

[Fact]
public void QueryEntity() {
    var sb = new StringBuilder();
    var time = new Stopwatch();

    time.Restart();
    List<Song> dplist1 = null;
    using (var conn = g.mysql.Ado.MasterPool.Get()) {
        dplist1 = Dapper.SqlMapper.Query<Song>(conn.Value, "select * from song").ToList();
    }
    time.Stop();
    sb.AppendLine($"Elapsed: {time.Elapsed}; Query Entity Counts: {dplist1.Count}; ORM: Dapper");

    time.Restart();
    var t3 = g.mysql.Ado.Query<Song>("select * from song");
    time.Stop();
    sb.AppendLine($"Elapsed: {time.Elapsed}; Query Entity Counts: {t3.Count}; ORM: FreeSql*");
}

执行 SQL 返回元组列表 Dapper.Query<Tuple> VS FreeSql.Query<Tuple>

[Fact]
public void QueryTuple() {
    var sb = new StringBuilder();
    var time = new Stopwatch();

    time.Restart();
    List<(int, string, string)> dplist2 = null;
    using (var conn = g.mysql.Ado.MasterPool.Get()) {
        dplist2 = Dapper.SqlMapper.Query<(int, string, string)>(conn.Value, "select * from song").ToList();
    }
    time.Stop();
    sb.AppendLine($"Elapsed: {time.Elapsed}; Query Tuple Counts: {dplist2.Count}; ORM: Dapper");

    time.Restart();
    var t4 = g.mysql.Ado.Query<(int, string, string)>("select * from song");
    time.Stop();
    sb.AppendLine($"Elapsed: {time.Elapsed}; Query Tuple Counts: {t4.Count}; ORM: FreeSql*");
}

执行 SQL 返回 dynamic 列表 Dapper.Query<dynamic> VS FreeSql.Query<dynamic>

[Fact]
public void QueryDynamic() {
    var sb = new StringBuilder();
    var time = new Stopwatch();

    time.Restart();
    List<dynamic> dplist3 = null;
    using (var conn = g.mysql.Ado.MasterPool.Get()) {
        dplist3 = Dapper.SqlMapper.Query<dynamic>(conn.Value, "select * from song").ToList();
    }
    time.Stop();
    sb.AppendLine($"Elapsed: {time.Elapsed}; Query Dynamic Counts: {dplist3.Count}; ORM: Dapper");

    time.Restart();
    var t5 = g.mysql.Ado.Query<dynamic>("select * from song");
    time.Stop();
    sb.AppendLine($"Elapsed: {time.Elapsed}; Query Dynamic Counts: {t3.Count}; ORM: FreeSql*");
}

Dapper.Query VS FreeSql.ToList

[Fact]
public void QueryList() {
    var sb = new StringBuilder();
    var time = new Stopwatch();

    time.Restart();
    var t3 = g.mysql.Select<Song>().ToList();
    time.Stop();
    sb.AppendLine($"Elapsed: {time.Elapsed}; ToList Entity Counts: {t3.Count}; ORM: FreeSql*");

    time.Restart();
    List<Song> dplist1 = null;
    using (var conn = g.mysql.Ado.MasterPool.Get()) {
        dplist1 = Dapper.SqlMapper.Query<Song>(conn.Value, "select * from song").ToList();
    }
    time.Stop();
    sb.AppendLine($"Elapsed: {time.Elapsed}; Query Entity Counts: {dplist1.Count}; ORM: Dapper");
}

更多测试源码:FreeSql/FreeSql.Tests.PerformanceTests/MySqlAdoTest.cs