性能测试

分别对小数据量和大数据量的数据插入进行测试以SQLSERVER库为样例测试 (不用bulkcopy因为这个方法是底层库提供的)

HiSql 源码(github) https://github.com/tansar/HiSql在新窗口打开

git clone https://github.com/tansar/HiSql.git

当前测试项目源码(github) https://github.com/tansar/HiSqlTestDemo在新窗口打开

git clone https://github.com/tansar/HiSqlTestDemo.git

以下是测试结果

总体测试结果

插入记录数	hisql(耗时)	sqlsugar(耗时)	freesql(耗时)
5条	`0.0107秒`	`0.0312秒`	`0.02675秒`
10条	`0.0111秒`	`0.0307秒`	`0.0271秒`
50条	`0.0174秒`	`0.0364秒`	`0.0430秒`
100条	`0.0281秒`	`0.0472秒`	`0.0922秒`
200条	`0.0310秒`	`0.0584秒`	`0.2319秒`
500条	`0.0712秒`	`0.1127秒`	`0.5996秒`
1000条	`0.1112秒`	`0.1871秒`	`0.8145秒`
10000条	`1.0440秒`	`1.6415秒`	`5.3671秒`
100000条	`10.3279秒`	`15.8685秒`	`46.0261秒`

分析

5条数据时 HiSql比SqlSugar快2.91倍比freesql快2.5倍,从数据可以看得出在这个数据档位sqlsugar表现是慢的 freesql比sqlsugar快14%
10条数量测试三个ORM的线性变化值不大依然是hisql最快 freesql次之,sqlsugar相对比freesql慢一点
50条数据时 hisql比sqlsugar快2.09倍比freesql快2.47倍，此时sqlsugar比freesql快18%,可以看出freesql在数量增长时他的性能也是呈下滑趋势
100条数据时 hisql比sqlsugar快1.67倍比freesql快3.28倍，sqlsugar比freesql快1.95倍,sqlsugar与Hisql差距慢慢在缩小，但freesql与其它两个orm框架相比差距越来越大
当数据达到100000条时 hisql依然比sqlsugar保持在绝对性的优势

从测试结果来看 HiSql的性能测试名列前茅 sqlsugar次之 freesql表现最差

测试源码

using System;
using System.Collections.Generic;
using System.Diagnostics;
using HiSql;
using SqlSugar;

namespace TestProject
{
    class Program
    {
        static void Main(string[] args)
        {
            

            //hisql连接 请先配置好数据库连接
            HiSqlClient sqlClient = Demo_Init.GetSqlClient();
            //hisql需要初始货安装 只需要执行一次
            sqlClient.CodeFirst.InstallHisql();


            //freesql连接
            IFreeSql freeClient = Demo_Init.GetFreeSqlClient();
            
            //sqlsugar连接
            SqlSugarClient sugarClient = Demo_Init.GetSugarClient();

            int _count = 100000;

            sqlClient.CodeFirst.CreateTable(typeof(Table.HTest01));

            Console.WriteLine("初始化hisql专用表成功!");

            sqlClient.CodeFirst.CreateTable(typeof(Table.HTest02));
           
            
            Console.WriteLine("初始化sqlsugar专用表成功!");

 
            sqlClient.CodeFirst.CreateTable(typeof(Table.HTest03));
            Console.WriteLine("初始化freesql专用表成功!");


            Console.WriteLine($"测试场景  向表中插入{_count}条数据,都用各个ORM常用的插入试(不用bulkcopy因为这个方法是底层库提供的)");
            Console.WriteLine($"用常规数据插入最适应日常应用场景");

            

           
            List<object> lstobj = new List<object>();
            List<Table.HTest02> lstobj2 = new List<Table.HTest02>();
            List<Table.HTest03> lstobj3 = new List<Table.HTest03>();
            Random random = new Random();

            //插入的参数值都随机产生 以免数据库执行相同SQL时会有缓存影响测试结果
            for (int i = 0; i < _count; i++)
            {
                //hisql可以用实体类也可以用匿名类
                lstobj.Add( new { SID=(i+1), UName=$"hisql{i}",Age=20+( i % 50), Salary=5000+(i%2000)+ random.Next(10), Descript=$"hisql初始创建" });
                
                //sqlsugar用匿句类报错用实体类
                lstobj2.Add(new Table.HTest02 { SID = (i + 1), UName = $"sqlsugar{i}", Age = 20 + (i % 50), Salary = 5000 + (i % 2000) + random.Next(10), Descript = $"sqlsugar初始创建" });
                lstobj3.Add(new Table.HTest03 { SID = (i + 1), UName = $"freesql{i}", Age = 20 + (i % 50), Salary = 5000 + (i % 2000) + random.Next(10), Descript = $"freesql初始创建" });
            }

            //删除测试表中的数据
            sqlClient.TrunCate("HTest01").ExecCommand();
            sqlClient.TrunCate("HTest02").ExecCommand();
            sqlClient.TrunCate("HTest03").ExecCommand();

            Stopwatch sw = new Stopwatch();

            #region freesql
            sw.Reset();
            Console.WriteLine("------------------------------");
            Console.WriteLine("----------FreeSql 测试----------");
            Console.WriteLine($"FreeSql 预热...{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}");
            var temp3 = freeClient.Queryable<Table.HTest03>().Where(w => w.Age < 0).ToList();
            Console.WriteLine($"FreeSql  正在插入数据\t{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}");
            sw.Start();
            freeClient.Insert<Table.HTest03>(lstobj3).ExecuteAffrows();
            sw.Stop();
            Console.WriteLine($"FreeSql 数据插入{_count}条 耗时{sw.Elapsed}秒");
            sw.Reset();
            #endregion


            #region sqlsugar
            sw.Reset();
            Console.WriteLine("------------------------------");
            Console.WriteLine("----------SqlSugar 测试----------");
            Console.WriteLine($"SqlSugar 预热...{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}");
            var temp2 = sugarClient.Queryable<Table.HTest02>("HTest02").Where(w => w.Age < 1).ToList();
            Console.WriteLine($"sqlsugar  正在插入数据\t{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}");
            sw.Start();
            sugarClient.Insertable(lstobj2).AS("HTest02").ExecuteCommand();
            sw.Stop();
            Console.WriteLine($"sqlsugar 数据插入{_count}条 耗时{sw.Elapsed}秒");
            sw.Reset();
            #endregion




            #region hisql
            sw.Reset();
            Console.WriteLine("------------------------------");
            Console.WriteLine("----------HiSql 测试----------");
            Console.WriteLine($"HiSql 预热...{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}");
            var temp1 = sqlClient.Query("HTest01").Field("*").Take(1).Skip(1).ToDynamic();
            Console.WriteLine($"HiSql  正在插入数据\t{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}");
            sw.Start();
            sqlClient.Insert("HTest01", lstobj).ExecCommand();
            sw.Stop();
            Console.WriteLine($"hisql 数据插入{_count}条 耗时{sw.Elapsed}秒");
            sw.Reset();
            #endregion


            var s = Console.ReadLine();
        }
    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123

实体类 HTest01 源码

using HiSql;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TestProject.Table
{
    [System.Serializable]
    [HiTable(IsEdit = true, TabName = "HTest01")]
    public class HTest01 : StandField
    {
        [HiColumn(FieldDesc = "编号", IsPrimary = true,    IsBllKey = true,  FieldType =HiType.INT, SortNum = 1, IsSys = false, DBDefault = HiTypeDBDefault.EMPTY)]
        public int SID
        {
            get; set;
        }
        [HiColumn(FieldDesc = "姓名", FieldType = HiType.NVARCHAR,FieldLen =50,  IsNull = false, SortNum = 2,  DBDefault = HiTypeDBDefault.EMPTY)]
        public string UName
        {
            get; set;
        }

        [HiColumn(FieldDesc = "年龄", FieldType = HiType.INT, IsNull = false, SortNum = 3, IsSys = true, DBDefault = HiTypeDBDefault.EMPTY)]
        public int Age
        {
            get; set;
        }

        [HiColumn(FieldDesc = "薪水", FieldType = HiType.DECIMAL,FieldDec =2, FieldLen =18,  IsNull = false, SortNum = 4, DBDefault = HiTypeDBDefault.EMPTY)]
        public int Salary
        {
            get; set;
        }
        [HiColumn(FieldDesc = "描述编号", FieldType = HiType.NVARCHAR, FieldLen = 100, IsNull = false, SortNum = 5, IsSys = true, DBDefault = HiTypeDBDefault.EMPTY)]
        public string Descript
        {
            get; set;
        }

    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

实体类 HTest02 源码

using HiSql;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TestProject.Table
{
    [System.Serializable]
    [HiTable(IsEdit = true, TabName = "HTest02")]
    public class HTest02 : StandField
    {
        //[SugarColumn(IsPrimaryKey =true)]
        [HiColumn(FieldDesc = "编号", IsPrimary = true,  IsBllKey = true, FieldType = HiType.INT, SortNum = 1, IsSys = false, DBDefault = HiTypeDBDefault.EMPTY)]
        public int SID
        {
            get; set;
        }
        [HiColumn(FieldDesc = "姓名", FieldType = HiType.NVARCHAR, FieldLen = 50, IsNull = false, SortNum = 2, DBDefault = HiTypeDBDefault.EMPTY)]
        public string UName
        {
            get; set;
        }

        [HiColumn(FieldDesc = "年龄", FieldType = HiType.INT, IsNull = false, SortNum = 3, IsSys = true, DBDefault = HiTypeDBDefault.EMPTY)]
        public int Age
        {
            get; set;
        }

        [HiColumn(FieldDesc = "薪水", FieldType = HiType.DECIMAL, FieldDec = 2, FieldLen = 18, IsNull = false, SortNum = 4, DBDefault = HiTypeDBDefault.EMPTY)]
        public int Salary
        {
            get; set;
        }
        [HiColumn(FieldDesc = "描述编号", FieldType = HiType.NVARCHAR, FieldLen = 100, IsNull = false, SortNum = 5, IsSys = true, DBDefault = HiTypeDBDefault.EMPTY)]
        public string Descript
        {
            get; set;
        }

    }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

实体类 HTest03 源码

using HiSql;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace TestProject.Table
{
    [System.Serializable]
    [HiTable(IsEdit = true, TabName = "HTest03")]
    public class HTest03 : StandField
    {
        //[SugarColumn(IsPrimaryKey =true)]
        [HiColumn(FieldDesc = "编号", IsPrimary = true,  IsBllKey = true, FieldType = HiType.INT, SortNum = 1, IsSys = false, DBDefault = HiTypeDBDefault.EMPTY)]
        public int SID
        {
            get; set;
        }
        [HiColumn(FieldDesc = "姓名", FieldType = HiType.NVARCHAR, FieldLen = 50, IsNull = false, SortNum = 2, DBDefault = HiTypeDBDefault.EMPTY)]
        public string UName
        {
            get; set;
        }

        [HiColumn(FieldDesc = "年龄", FieldType = HiType.INT, IsNull = false, SortNum = 3, IsSys = true, DBDefault = HiTypeDBDefault.EMPTY)]
        public int Age
        {
            get; set;
        }

        [HiColumn(FieldDesc = "薪水", FieldType = HiType.DECIMAL, FieldDec = 2, FieldLen = 18, IsNull = false, SortNum = 4, DBDefault = HiTypeDBDefault.EMPTY)]
        public int Salary
        {
            get; set;
        }
        [HiColumn(FieldDesc = "描述编号", FieldType = HiType.NVARCHAR, FieldLen = 100, IsNull = false, SortNum = 5, IsSys = true, DBDefault = HiTypeDBDefault.EMPTY)]
        public string Descript
        {
            get; set;
        }

    }
}