Skip to content

性能

Jump to bottom
28810 edited this page Dec 21, 2019 · 41 revisions

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

插入测试

测试结果(52个字段)

| | 18W+52C | 1W+52C | 5K+52C | 2K+52C | 1K+52C | 500+52C | 100+52C | 50+52C | | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- | | MySql 5.5 ExecuteAffrows | 55,497ms | 4,953ms | 2,304ms | 2,554ms | 1,516ms | 1,572ms | 265ms | 184ms | | SqlServer Express ExecuteAffrows | 402,355ms | 24,847ms | 11,465ms | 4,971ms | 2,437ms | 915ms | 138ms | 88ms | | SqlServer Express ExecuteSqlBulkCopy | 21,065ms | 578ms | 326ms | 139ms | 105ms | 79ms | 60ms | 48ms | | PostgreSQL 10 ExecuteAffrows | 46,756ms | 3,294ms | 2,269ms | 1,019ms | 374ms | 209ms | 51ms | 37ms | | PostgreSQL 10 ExecutePgCopy | 10,090ms | 583ms | 337ms | 136ms | 88ms | 61ms | 30ms | 25ms | | Oracle XE ExecuteAffrows | - | - | - | - | 24,528 | 10,648ms | 571ms | 200ms | | Sqlite ExecuteAffrows | 28,554ms | 1,149ms | 701ms | 327ms | 155ms | 91ms | 44ms | 35ms |

18W+52C 解释：插入18万行记录，表字段有52个

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

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

测试结果(10个字段)

18W+10C 1W+10C 5K+10C 2K+10C 1K+10C 500+10C 100+10C 50+10C
MySql 5.5 ExecuteAffrows 15,380ms 1,813ms 1,457ms 1,254ms 563ms 246ms 55ms 21ms
SqlServer Express ExecuteAffrows 47,204ms 2,275ms 1,108ms 488ms 279ms 123ms 35ms 16ms
SqlServer Express ExecuteSqlBulkCopy 4,248ms 127ms 71ms 30ms 48ms 14ms 11ms 10ms
PostgreSQL 10 ExecuteAffrows 9,786ms 568ms 336ms 157ms 102ms 34ms 9ms 6ms
PostgreSQL 10 ExecutePgCopy 4,081ms 167ms 93ms 39ms 21ms 12ms 4ms 2ms
Oracle XE ExecuteAffrows - - - - 2,394 731ms 67ms 33ms
Sqlite ExecuteAffrows 4,524ms 246ms 137ms 94ms 35ms 19ms 14ms 11ms

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

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 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();

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) 131072 623.4959ms 424.2411ms 644.8897ms
FreeSql.Query(sql) 131072 647.0552ms 577.3532ms 944.7454ms
FreeSql.ToList 131072 622.8980ms 435.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

Basic

使用指南

学习与交流

信息

Clone this wiki locally