c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

tansar 發表於 2022-06-22
SqlServer C# 框架 SQL

在2022年1月份本人做過一次sqlsugar,hisql,freesql三個框架的效能測試,上次主要是測的sqlserver下的常規插入(非bulkcopy的方式資料插入),hisql與目前比較流行的ORM框架效能測試對比 時間已經過半年,這次我就測的更完整點測試方案如下

測試方案

  1. 測試四種資料庫(sqlserver,mysql,oralce,postgresql) 每種資料庫寫一遍測試文章
  2. 常規插入和批量插入
  3. 資料量從5條到100W間的資料插入
  4. 資料列從5列和50列的各種資料型別場景
  5. 開源測試程式碼供各位網友檢視稽核是否測試公平公正

測試原始碼https://github.com/tansar/HiSqlTestDemo

測試環境

作業系統環境

c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

sqlserver 環境

c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

mysql 環境

c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

oracle 環境

c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

postgresql 環境

c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

sqlserver 常規資料插入測試

10列以下欄位測試程式碼如下

  public static void TestSqlServerInsert(int _count)
        {

      //hisql連線 請先配置好資料庫連線
      HiSqlClient sqlClient = Demo_Init.GetSqlClient();
      //hisql需要初始貨安裝 只需要執行一次
      sqlClient.CodeFirst.InstallHisql();


      //freesql連線
      IFreeSql freeClient = Demo_Init.GetFreeSqlClient();

      //sqlsugar連線
      SqlSugarClient sugarClient = Demo_Init.GetSugarClient();

      

      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($"測試場景 Sqlserver  向表中插入{_count}條資料 常規資料插入)");
      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 Table.HTest01 { 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 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

50列欄位測試程式碼如下

  public static void TestSqlServer50ColInsert(int _count)
        {

            //hisql連線 請先配置好資料庫連線
          HiSqlClient sqlClient = Demo_Init.GetSqlClient();
          //hisql需要初始貨安裝 只需要執行一次
          sqlClient.CodeFirst.InstallHisql();


          //freesql連線
          IFreeSql freeClient = Demo_Init.GetFreeSqlClient();

          //sqlsugar連線
          SqlSugarClient sugarClient = Demo_Init.GetSugarClient();



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

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

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


          Console.WriteLine("初始化sqlsugar專用表成功!");


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


          Console.WriteLine($"測試場景 Sqlserver  向表中插入{_count}條資料 50列 常規資料插入)");
          Console.WriteLine($"用常規資料插入最適應日常應用場景");




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

          //插入的引數值都隨機產生 以免資料庫執行相同SQL時會有快取影響測試結果
          for (int i = 0; i < _count; i++)
          {
              //hisql可以用實體類也可以用匿名類
              lstobj.Add(new Table.H_Test50C01 { 
                  Material=(900000+i).ToString(),
                  Batch=(30000000+i).ToString(),
                  TestNum1= random.Next(10,100),
                  TestNum2 = random.Next(10, 100),
                  TestNum3 = random.Next(10, 100),
                  TestNum4 = random.Next(10, 100),
                  TestNum5 = random.Next(10, 100),
                  TestNum6 = random.Next(10, 100),
                  TestNum7 = random.Next(10, 100),
                  TestNum8 = random.Next(10, 100),
                  TestNum9 = random.Next(10, 100),
                  TestNum10 = random.Next(10, 100),
                  TestNum11 = random.Next(10, 100),
                  TestNum12 = random.Next(10, 100),
                  TestNum13= random.Next(10, 100),
                  TestNum14= random.Next(10, 100),
                  TestNum15= random.Next(10, 100),
                  TestStr1 = $"hisql{random.Next(1, 100).ToString()}",
                  TestStr2 = $"hisql{random.Next(1, 100).ToString()}",
                  TestStr3 = $"hisql{random.Next(1, 100).ToString()}",
                  TestStr4 = $"hisql{random.Next(1, 100).ToString()}",
                  TestStr5 = $"hisql{random.Next(1, 100).ToString()}",
                  TestStr6 = $"hisql{random.Next(1, 100).ToString()}",
                  TestStr7 = $"hisql{random.Next(1, 100).ToString()}",
                  TestStr8 = $"hisql{random.Next(1, 100).ToString()}",
                  TestStr9 = $"hisql{random.Next(1, 100).ToString()}",
                  TestStr10 = $"hisql{random.Next(1, 100).ToString()}",
                  TestStr11 = $"hisql{random.Next(1, 100).ToString()}",
                  TestStr12 = $"hisql{random.Next(1, 100).ToString()}",
                  TestStr13 = $"hisql{random.Next(1, 100).ToString()}",
                  TestStr14 = $"hisql{random.Next(1, 100).ToString()}",
                  TestStr15 = $"hisql{random.Next(1, 100).ToString()}",
                  TestDec1 = i+ random.Next(1, 10000)/3,
                  TestDec2 = i + random.Next(1, 10000) / 3,
                  TestDec3 = i + random.Next(1, 10000) / 3,
                  TestDec4 = i + random.Next(1, 10000) / 3,
                  TestDec5 = i + random.Next(1, 10000) / 3,
                  TestDec6 = i + random.Next(1, 10000) / 3,
                  TestDec7 = i + random.Next(1, 10000) / 3,
                  TestDec8 = i + random.Next(1, 10000) / 3,
                  TestDec9 = i + random.Next(1, 10000) / 3,
                  TestDec10 = i + random.Next(1, 10000) / 3,
                  TestDec11 = i + random.Next(1, 10000) / 3,
                  TestDec12 = i + random.Next(1, 10000) / 3,
                  TestDec13 = i + random.Next(1, 10000) / 3,
                  TestDec14 = i + random.Next(1, 10000) / 3,
                  TestDec15 = i + random.Next(1, 10000) / 3,
                  Salary = 5000 + (i % 2000) + random.Next(10), Descript = $"hisql初始建立" });

              //sqlsugar用匿句類報錯用實體類
              lstobj2.Add(new Table.H_Test50C02
              {
                  Material = (900000 + i).ToString(),
                  Batch = (30000000 + i).ToString(),
                  TestNum1 = random.Next(10, 100),
                  TestNum2 = random.Next(10, 100),
                  TestNum3 = random.Next(10, 100),
                  TestNum4 = random.Next(10, 100),
                  TestNum5 = random.Next(10, 100),
                  TestNum6 = random.Next(10, 100),
                  TestNum7 = random.Next(10, 100),
                  TestNum8 = random.Next(10, 100),
                  TestNum9 = random.Next(10, 100),
                  TestNum10 = random.Next(10, 100),
                  TestNum11 = random.Next(10, 100),
                  TestNum12 = random.Next(10, 100),
                  TestNum13 = random.Next(10, 100),
                  TestNum14 = random.Next(10, 100),
                  TestNum15 = random.Next(10, 100),
                  TestStr1 = $"sqlsugar{random.Next(1, 100).ToString()}",
                  TestStr2 = $"sqlsugar{random.Next(1, 100).ToString()}",
                  TestStr3 = $"sqlsugar{random.Next(1, 100).ToString()}",
                  TestStr4 = $"sqlsugar{random.Next(1, 100).ToString()}",
                  TestStr5 = $"sqlsugar{random.Next(1, 100).ToString()}",
                  TestStr6 = $"sqlsugar{random.Next(1, 100).ToString()}",
                  TestStr7 = $"sqlsugar{random.Next(1, 100).ToString()}",
                  TestStr8 = $"sqlsugar{random.Next(1, 100).ToString()}",
                  TestStr9 = $"sqlsugar{random.Next(1, 100).ToString()}",
                  TestStr10 = $"sqlsugar{random.Next(1, 100).ToString()}",
                  TestStr11 = $"sqlsugar{random.Next(1, 100).ToString()}",
                  TestStr12 = $"sqlsugar{random.Next(1, 100).ToString()}",
                  TestStr13 = $"sqlsugar{random.Next(1, 100).ToString()}",
                  TestStr14 = $"sqlsugar{random.Next(1, 100).ToString()}",
                  TestStr15 = $"sqlsugar{random.Next(1, 100).ToString()}",
                  TestDec1 = i + random.Next(1, 10000) / 3,
                  TestDec2 = i + random.Next(1, 10000) / 3,
                  TestDec3 = i + random.Next(1, 10000) / 3,
                  TestDec4 = i + random.Next(1, 10000) / 3,
                  TestDec5 = i + random.Next(1, 10000) / 3,
                  TestDec6 = i + random.Next(1, 10000) / 3,
                  TestDec7 = i + random.Next(1, 10000) / 3,
                  TestDec8 = i + random.Next(1, 10000) / 3,
                  TestDec9 = i + random.Next(1, 10000) / 3,
                  TestDec10 = i + random.Next(1, 10000) / 3,
                  TestDec11 = i + random.Next(1, 10000) / 3,
                  TestDec12 = i + random.Next(1, 10000) / 3,
                  TestDec13 = i + random.Next(1, 10000) / 3,
                  TestDec14 = i + random.Next(1, 10000) / 3,
                  TestDec15 = i + random.Next(1, 10000) / 3,
                  Salary = 5000 + (i % 2000) + random.Next(10),
                  Descript = $"sqlsugar初始建立"
              });
              lstobj3.Add(new Table.H_Test50C03
              {
                  Material = (900000 + i).ToString(),
                  Batch = (30000000 + i).ToString(),
                  TestNum1 = random.Next(10, 100),
                  TestNum2 = random.Next(10, 100),
                  TestNum3 = random.Next(10, 100),
                  TestNum4 = random.Next(10, 100),
                  TestNum5 = random.Next(10, 100),
                  TestNum6 = random.Next(10, 100),
                  TestNum7 = random.Next(10, 100),
                  TestNum8 = random.Next(10, 100),
                  TestNum9 = random.Next(10, 100),
                  TestNum10 = random.Next(10, 100),
                  TestNum11 = random.Next(10, 100),
                  TestNum12 = random.Next(10, 100),
                  TestNum13 = random.Next(10, 100),
                  TestNum14 = random.Next(10, 100),
                  TestNum15 = random.Next(10, 100),
                  TestStr1 = $"freesql{random.Next(1, 100).ToString()}",
                  TestStr2 = $"freesql{random.Next(1, 100).ToString()}",
                  TestStr3 = $"freesql{random.Next(1, 100).ToString()}",
                  TestStr4 = $"freesql{random.Next(1, 100).ToString()}",
                  TestStr5 = $"freesql{random.Next(1, 100).ToString()}",
                  TestStr6 = $"freesql{random.Next(1, 100).ToString()}",
                  TestStr7 = $"freesql{random.Next(1, 100).ToString()}",
                  TestStr8 = $"freesql{random.Next(1, 100).ToString()}",
                  TestStr9 = $"freesql{random.Next(1, 100).ToString()}",
                  TestStr10 = $"freesql{random.Next(1, 100).ToString()}",
                  TestStr11 = $"freesql{random.Next(1, 100).ToString()}",
                  TestStr12 = $"freesql{random.Next(1, 100).ToString()}",
                  TestStr13 = $"freesql{random.Next(1, 100).ToString()}",
                  TestStr14 = $"freesql{random.Next(1, 100).ToString()}",
                  TestStr15 = $"freesql{random.Next(1, 100).ToString()}",
                  TestDec1 = i + random.Next(1, 10000) / 3,
                  TestDec2 = i + random.Next(1, 10000) / 3,
                  TestDec3 = i + random.Next(1, 10000) / 3,
                  TestDec4 = i + random.Next(1, 10000) / 3,
                  TestDec5 = i + random.Next(1, 10000) / 3,
                  TestDec6 = i + random.Next(1, 10000) / 3,
                  TestDec7 = i + random.Next(1, 10000) / 3,
                  TestDec8 = i + random.Next(1, 10000) / 3,
                  TestDec9 = i + random.Next(1, 10000) / 3,
                  TestDec10 = i + random.Next(1, 10000) / 3,
                  TestDec11 = i + random.Next(1, 10000) / 3,
                  TestDec12 = i + random.Next(1, 10000) / 3,
                  TestDec13 = i + random.Next(1, 10000) / 3,
                  TestDec14 = i + random.Next(1, 10000) / 3,
                  TestDec15 = i + random.Next(1, 10000) / 3,
                  Salary = 5000 + (i % 2000) + random.Next(10),
                  Descript = $"freesql初始建立"
              });
          }

          //刪除測試表中的資料
          sqlClient.TrunCate("H_Test50C01").ExecCommand();
          sqlClient.TrunCate("H_Test50C02").ExecCommand();
          sqlClient.TrunCate("H_Test50C03").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.H_Test50C03>().Where(w => w.TestDec1 < 0).ToList();
          Console.WriteLine($"FreeSql  正在插入資料\t{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}");
          sw.Start();
          freeClient.Insert<Table.H_Test50C03>(lstobj3).ExecuteAffrows();

          sw.Stop();
          Console.WriteLine($"FreeSql 資料插入{_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("H_Test50C01").Field("*").Take(1).Skip(1).ToDynamic();
          Console.WriteLine($"HiSql  正在插入資料\t{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}");
          sw.Start();
          sqlClient.Insert("H_Test50C01", lstobj).ExecCommand();
          sw.Stop();
          Console.WriteLine($"hisql 資料插入{_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.H_Test50C03>("H_Test50C03").Where(w => w.TestDec1 < 1).ToList();
          Console.WriteLine($"sqlsugar  正在插入資料\t{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}");
          sw.Start();
          sugarClient.Insertable(lstobj2).AS("H_Test50C03").ExecuteCommand();
          sw.Stop();
          Console.WriteLine($"sqlsugar 資料插入{_count}條 耗時{sw.Elapsed}秒");
          sw.Reset();
          #endregion
      }

測試結果

5條記錄10列以下測試結果截圖

c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

5條記錄50列測試結果截圖
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

50條記錄10列以下測試結果截圖
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

50條記錄50列測試結果截圖
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

100條記錄10列以下測試結果截圖
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

100條記錄50列測試結果截圖
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

500條記錄10列以下測試結果截圖
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

500條記錄50列測試結果截圖
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

1000條記錄10列以下測試結果截圖
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

1000條記錄50列測試結果截圖
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

5000條記錄10列以下測試結果截圖
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

5000條記錄50列測試結果截圖
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

10000條記錄10列以下測試結果截圖
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

10000條記錄50列測就結果截圖
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

50000條記錄10列以下測試結果截圖
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

50000條記錄50列測試結果截圖
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

100000條記錄10列以下測試結果截圖
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

100000條記錄50列測試結果截圖
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

sqlsugar 在執行此操作時丟擲異常
錯誤提示:System.Data.SqlClient.SqlException:“資源池“default”沒有足夠的系統記憶體來執行此查詢
在單獨只執行sqlsugar的測試樣例依然報此錯誤 通過分析sqlsugar的底層執行邏輯是因為它把所有的插入都拼成了一個插入sql文件造成的

c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

測試結果

c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

sqlserver 批量(bulkcopy)資料插入測試

可能會有很多人認為如果大批量資料插入肯定不會用常規的方式插入應該用BulkCopy的方式插入,好,那麼我們再來測測基於資料庫廠商提供的SDK驅動自帶的BulkCopy再來對比一下三個框架的效能

10列以下批量插入的bulkcopy測試程式碼

public static void TestSqlServerBulkCopy(int _count)
        {

            //hisql連線 請先配置好資料庫連線
            HiSqlClient sqlClient = Demo_Init.GetSqlClient();
            //hisql需要初始貨安裝 只需要執行一次
            sqlClient.CodeFirst.InstallHisql();


            //freesql連線
            IFreeSql freeClient = Demo_Init.GetFreeSqlClient();

            //sqlsugar連線
            SqlSugarClient sugarClient = Demo_Init.GetSugarClient();



            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($"測試場景 Sqlserver  向表中插入{_count}條資料 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 Table.HTest01 { 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();
            freeClient.Insert<Table.HTest03>(lstobj3).ExecuteSqlBulkCopy();
            sw.Stop();
            Console.WriteLine($"FreeSql 資料插入{_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();

            sqlClient.BulkCopyExecCommand("HTest01", lstobj);
            sw.Stop();
            Console.WriteLine($"hisql 資料插入{_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();
            sugarClient.Fastest<Table.HTest02>().BulkCopy(lstobj2);
            sw.Stop();
            Console.WriteLine($"sqlsugar 資料插入{_count}條 耗時{sw.Elapsed}秒");
            sw.Reset();
            #endregion
        }

50列批量插入的bulkcopy測試程式碼

public static void TestSqlServer50ColInsert(int _count)
        {

            //hisql連線 請先配置好資料庫連線
            HiSqlClient sqlClient = Demo_Init.GetSqlClient();
            //hisql需要初始貨安裝 只需要執行一次
            sqlClient.CodeFirst.InstallHisql();


            //freesql連線
            IFreeSql freeClient = Demo_Init.GetFreeSqlClient();

            //sqlsugar連線
            SqlSugarClient sugarClient = Demo_Init.GetSugarClient();



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

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

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


            Console.WriteLine("初始化sqlsugar專用表成功!");


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


            Console.WriteLine($"測試場景 Sqlserver  向表中插入{_count}條資料 50列 常規資料插入)");
            Console.WriteLine($"適用於大量資料匯入場景");




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

            //插入的引數值都隨機產生 以免資料庫執行相同SQL時會有快取影響測試結果
            for (int i = 0; i < _count; i++)
            {
                //hisql可以用實體類也可以用匿名類
                lstobj.Add(new Table.H_Test50C01 { 
                    Material=(900000+i).ToString(),
                    Batch=(30000000+i).ToString(),
                    TestNum1= random.Next(10,100),
                    TestNum2 = random.Next(10, 100),
                    TestNum3 = random.Next(10, 100),
                    TestNum4 = random.Next(10, 100),
                    TestNum5 = random.Next(10, 100),
                    TestNum6 = random.Next(10, 100),
                    TestNum7 = random.Next(10, 100),
                    TestNum8 = random.Next(10, 100),
                    TestNum9 = random.Next(10, 100),
                    TestNum10 = random.Next(10, 100),
                    TestNum11 = random.Next(10, 100),
                    TestNum12 = random.Next(10, 100),
                    TestNum13= random.Next(10, 100),
                    TestNum14= random.Next(10, 100),
                    TestNum15= random.Next(10, 100),
                    TestStr1 = $"hisql{random.Next(1, 100).ToString()}",
                    TestStr2 = $"hisql{random.Next(1, 100).ToString()}",
                    TestStr3 = $"hisql{random.Next(1, 100).ToString()}",
                    TestStr4 = $"hisql{random.Next(1, 100).ToString()}",
                    TestStr5 = $"hisql{random.Next(1, 100).ToString()}",
                    TestStr6 = $"hisql{random.Next(1, 100).ToString()}",
                    TestStr7 = $"hisql{random.Next(1, 100).ToString()}",
                    TestStr8 = $"hisql{random.Next(1, 100).ToString()}",
                    TestStr9 = $"hisql{random.Next(1, 100).ToString()}",
                    TestStr10 = $"hisql{random.Next(1, 100).ToString()}",
                    TestStr11 = $"hisql{random.Next(1, 100).ToString()}",
                    TestStr12 = $"hisql{random.Next(1, 100).ToString()}",
                    TestStr13 = $"hisql{random.Next(1, 100).ToString()}",
                    TestStr14 = $"hisql{random.Next(1, 100).ToString()}",
                    TestStr15 = $"hisql{random.Next(1, 100).ToString()}",
                    TestDec1 = i+ random.Next(1, 10000)/3,
                    TestDec2 = i + random.Next(1, 10000) / 3,
                    TestDec3 = i + random.Next(1, 10000) / 3,
                    TestDec4 = i + random.Next(1, 10000) / 3,
                    TestDec5 = i + random.Next(1, 10000) / 3,
                    TestDec6 = i + random.Next(1, 10000) / 3,
                    TestDec7 = i + random.Next(1, 10000) / 3,
                    TestDec8 = i + random.Next(1, 10000) / 3,
                    TestDec9 = i + random.Next(1, 10000) / 3,
                    TestDec10 = i + random.Next(1, 10000) / 3,
                    TestDec11 = i + random.Next(1, 10000) / 3,
                    TestDec12 = i + random.Next(1, 10000) / 3,
                    TestDec13 = i + random.Next(1, 10000) / 3,
                    TestDec14 = i + random.Next(1, 10000) / 3,
                    TestDec15 = i + random.Next(1, 10000) / 3,
                    Salary = 5000 + (i % 2000) + random.Next(10), Descript = $"hisql初始建立" });

                //sqlsugar用匿句類報錯用實體類
                lstobj2.Add(new Table.H_Test50C02
                {
                    Material = (900000 + i).ToString(),
                    Batch = (30000000 + i).ToString(),
                    TestNum1 = random.Next(10, 100),
                    TestNum2 = random.Next(10, 100),
                    TestNum3 = random.Next(10, 100),
                    TestNum4 = random.Next(10, 100),
                    TestNum5 = random.Next(10, 100),
                    TestNum6 = random.Next(10, 100),
                    TestNum7 = random.Next(10, 100),
                    TestNum8 = random.Next(10, 100),
                    TestNum9 = random.Next(10, 100),
                    TestNum10 = random.Next(10, 100),
                    TestNum11 = random.Next(10, 100),
                    TestNum12 = random.Next(10, 100),
                    TestNum13 = random.Next(10, 100),
                    TestNum14 = random.Next(10, 100),
                    TestNum15 = random.Next(10, 100),
                    TestStr1 = $"sqlsugar{random.Next(1, 100).ToString()}",
                    TestStr2 = $"sqlsugar{random.Next(1, 100).ToString()}",
                    TestStr3 = $"sqlsugar{random.Next(1, 100).ToString()}",
                    TestStr4 = $"sqlsugar{random.Next(1, 100).ToString()}",
                    TestStr5 = $"sqlsugar{random.Next(1, 100).ToString()}",
                    TestStr6 = $"sqlsugar{random.Next(1, 100).ToString()}",
                    TestStr7 = $"sqlsugar{random.Next(1, 100).ToString()}",
                    TestStr8 = $"sqlsugar{random.Next(1, 100).ToString()}",
                    TestStr9 = $"sqlsugar{random.Next(1, 100).ToString()}",
                    TestStr10 = $"sqlsugar{random.Next(1, 100).ToString()}",
                    TestStr11 = $"sqlsugar{random.Next(1, 100).ToString()}",
                    TestStr12 = $"sqlsugar{random.Next(1, 100).ToString()}",
                    TestStr13 = $"sqlsugar{random.Next(1, 100).ToString()}",
                    TestStr14 = $"sqlsugar{random.Next(1, 100).ToString()}",
                    TestStr15 = $"sqlsugar{random.Next(1, 100).ToString()}",
                    TestDec1 = i + random.Next(1, 10000) / 3,
                    TestDec2 = i + random.Next(1, 10000) / 3,
                    TestDec3 = i + random.Next(1, 10000) / 3,
                    TestDec4 = i + random.Next(1, 10000) / 3,
                    TestDec5 = i + random.Next(1, 10000) / 3,
                    TestDec6 = i + random.Next(1, 10000) / 3,
                    TestDec7 = i + random.Next(1, 10000) / 3,
                    TestDec8 = i + random.Next(1, 10000) / 3,
                    TestDec9 = i + random.Next(1, 10000) / 3,
                    TestDec10 = i + random.Next(1, 10000) / 3,
                    TestDec11 = i + random.Next(1, 10000) / 3,
                    TestDec12 = i + random.Next(1, 10000) / 3,
                    TestDec13 = i + random.Next(1, 10000) / 3,
                    TestDec14 = i + random.Next(1, 10000) / 3,
                    TestDec15 = i + random.Next(1, 10000) / 3,
                    Salary = 5000 + (i % 2000) + random.Next(10),
                    Descript = $"sqlsugar初始建立"
                });
                lstobj3.Add(new Table.H_Test50C03
                {
                    Material = (900000 + i).ToString(),
                    Batch = (30000000 + i).ToString(),
                    TestNum1 = random.Next(10, 100),
                    TestNum2 = random.Next(10, 100),
                    TestNum3 = random.Next(10, 100),
                    TestNum4 = random.Next(10, 100),
                    TestNum5 = random.Next(10, 100),
                    TestNum6 = random.Next(10, 100),
                    TestNum7 = random.Next(10, 100),
                    TestNum8 = random.Next(10, 100),
                    TestNum9 = random.Next(10, 100),
                    TestNum10 = random.Next(10, 100),
                    TestNum11 = random.Next(10, 100),
                    TestNum12 = random.Next(10, 100),
                    TestNum13 = random.Next(10, 100),
                    TestNum14 = random.Next(10, 100),
                    TestNum15 = random.Next(10, 100),
                    TestStr1 = $"freesql{random.Next(1, 100).ToString()}",
                    TestStr2 = $"freesql{random.Next(1, 100).ToString()}",
                    TestStr3 = $"freesql{random.Next(1, 100).ToString()}",
                    TestStr4 = $"freesql{random.Next(1, 100).ToString()}",
                    TestStr5 = $"freesql{random.Next(1, 100).ToString()}",
                    TestStr6 = $"freesql{random.Next(1, 100).ToString()}",
                    TestStr7 = $"freesql{random.Next(1, 100).ToString()}",
                    TestStr8 = $"freesql{random.Next(1, 100).ToString()}",
                    TestStr9 = $"freesql{random.Next(1, 100).ToString()}",
                    TestStr10 = $"freesql{random.Next(1, 100).ToString()}",
                    TestStr11 = $"freesql{random.Next(1, 100).ToString()}",
                    TestStr12 = $"freesql{random.Next(1, 100).ToString()}",
                    TestStr13 = $"freesql{random.Next(1, 100).ToString()}",
                    TestStr14 = $"freesql{random.Next(1, 100).ToString()}",
                    TestStr15 = $"freesql{random.Next(1, 100).ToString()}",
                    TestDec1 = i + random.Next(1, 10000) / 3,
                    TestDec2 = i + random.Next(1, 10000) / 3,
                    TestDec3 = i + random.Next(1, 10000) / 3,
                    TestDec4 = i + random.Next(1, 10000) / 3,
                    TestDec5 = i + random.Next(1, 10000) / 3,
                    TestDec6 = i + random.Next(1, 10000) / 3,
                    TestDec7 = i + random.Next(1, 10000) / 3,
                    TestDec8 = i + random.Next(1, 10000) / 3,
                    TestDec9 = i + random.Next(1, 10000) / 3,
                    TestDec10 = i + random.Next(1, 10000) / 3,
                    TestDec11 = i + random.Next(1, 10000) / 3,
                    TestDec12 = i + random.Next(1, 10000) / 3,
                    TestDec13 = i + random.Next(1, 10000) / 3,
                    TestDec14 = i + random.Next(1, 10000) / 3,
                    TestDec15 = i + random.Next(1, 10000) / 3,
                    Salary = 5000 + (i % 2000) + random.Next(10),
                    Descript = $"freesql初始建立"
                });
            }

            //刪除測試表中的資料
            sqlClient.TrunCate("H_Test50C01").ExecCommand();
            sqlClient.TrunCate("H_Test50C02").ExecCommand();
            sqlClient.TrunCate("H_Test50C03").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.H_Test50C03>().Where(w => w.TestDec1 < 0).ToList();
            Console.WriteLine($"FreeSql  正在插入資料\t{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}");
            sw.Start();
            freeClient.Insert<Table.H_Test50C03>(lstobj3).ExecuteAffrows();

            sw.Stop();
            Console.WriteLine($"FreeSql 資料插入{_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("H_Test50C01").Field("*").Take(1).Skip(1).ToDynamic();
            Console.WriteLine($"HiSql  正在插入資料\t{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}");
            sw.Start();
            sqlClient.Insert("H_Test50C01", lstobj).ExecCommand();
            sw.Stop();
            Console.WriteLine($"hisql 資料插入{_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.H_Test50C03>("H_Test50C03").Where(w => w.TestDec1 < 1).ToList();
            Console.WriteLine($"sqlsugar  正在插入資料\t{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")}");
            sw.Start();
            sugarClient.Insertable(lstobj2).AS("H_Test50C03").ExecuteCommand();
            sw.Stop();
            Console.WriteLine($"sqlsugar 資料插入{_count}條 耗時{sw.Elapsed}秒");
            sw.Reset();
            #endregion
        }

測試結果

1W條資料10列以下欄位bulkcopy插入測試
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

1W條資料50列欄位bulkcopy插入測試
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

5W條資料10列以下欄位bulkcpy插入測試
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

5W條資料50列欄位bulkcopy插入測試
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

10W條資料10列以下欄位Bulkcopy插入測試
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

10W條資料50列欄位bulkcopy插入測試
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

20w條資料10列以下欄位bulkcopy插入測試
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

20w條資料50列欄位bulkcopy插入測試
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

50w條資料10列欄位Bulkcopy插入測試
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

50w條資料50列欄位BulkCopy插入測試
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

100W條10列以下欄位bulkCopy插入測試
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

100W條資料50列欄位bulkcopy插入測試
c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

測試結果

c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試

總結

我想不用總結了 三個ORM不管是按常規插入和bulkcopy插入誰快認慢一目瞭然,不過我還是想貼上以下圖

c# sqlsugar,hisql,freesql orm框架全方位效能測試對比 sqlserver 效能測試