2018-05-19

使用 EntityFramework Insert 大量資料

這是參加 黃忠成老師的 Entity Framework 全線開發 課程時他提出讓學員思考的問題:如何使用 EntityFramework Insert 大量資料,我當下立馬想起印象中以前同事也被這個問題困擾過,而我自己本身則沒有遇到相同困擾,剛好透過這個機會來了解其中的差異也順便嘗試看看不同解法

立馬動手模擬看看,親身體驗實際情況吧


基本環境及情境說明

  1. 使用 EntityFramework 6.1.3 新增資料至 SQL Server 2017
  2. SQL Server 與 EntityFramework Client 都在本機電腦上 ,減少 network io
  3. 測試新增 10,100,1000,10000,100000 筆資料至資料庫所需時間
  4. 測試的 EF 使用方式有:
    • 逐筆 Add 並逐筆 SaveChanges
    • 逐筆 Add 及一次 SaveChanges
    • 批次 Add 及 SaveChanges
    • 批次 Add 及 SaveChanges 並 dispose context

測試內容

  1. 逐筆 Add 並逐筆發動 SaveChanges

    • 程式碼
      TestNestedEF.Models.TestEntities db = new TestEntities();
      
      var count = 100000;
      Stopwatch sw = new Stopwatch();
      sw.Reset();
      sw.Start();
      for (int i = 0; i < count; i++)
      {
          Parent parent = new Parent() { Name = i.ToString() };
          parent.Child = new List<Child>() { new Child() { Name = $"{i}_Son" } };
      
          db.Parent.Add(parent);
          db.SaveChanges();
      }
      sw.Stop();
      sw.ElapsedMilliseconds.Dump();
      
    • 測試筆數
      • 10:11 ms

        1saveeverytime10

      • 100:187 ms

        2saveeverytime100

      • 1000:4282 ms

        3saveeverytime1000

      • 10000:401177 ms

        4saveeverytime10000

      • 100000:44827184 ms

        5saveeverytime100000

  2. 逐筆 Add 及一次 SaveChanges

    • 程式碼
      TestNestedEF.Models.TestEntities db = new TestEntities();
      
      var count = 10000;
      Stopwatch sw = new Stopwatch();
      sw.Reset();
      sw.Start();
      for (int i = 0; i < count; i++)
      {
          Parent parent = new Parent() { Name = i.ToString() };
          parent.Child = new List<Child>() { new Child() { Name = $"{i}_Son" } };
      
          db.Parent.Add(parent);
      }
      db.SaveChanges();
      sw.Stop();
      sw.ElapsedMilliseconds.Dump();
      
    • 測試筆數
      • 10:7 ms

        6batchsave10

      • 100:79 ms

        7batchsave100

      • 1000:2309 ms

        8batchsave1000

      • 10000:195678 ms

        9batchsave10000

      • 100000:19916508 ms

        10batchsave100000

  3. 批次 Add 及 SaveChanges

    • 程式碼
      TestNestedEF.Models.TestEntities db = new TestEntities();
      
      var count = 100000;
      Stopwatch sw = new Stopwatch();
      sw.Reset();
      sw.Start();
      var batchcount = 100;
      for (int i = 0; i < (count / batchcount); i++)
      {
          for (int j = 0; j < batchcount; j++)
          {
              Parent parent = new Parent() { Name = $"{i * batchcount + j}" };
              parent.Child = new List<Child>() { new Child() { Name = $"{i * batchcount + j}_Son" } };
      
              db.Parent.Add(parent);
          }
          db.SaveChanges();
      }
      sw.Stop();
      sw.ElapsedMilliseconds.Dump();
      
    • 測試筆數
      • 10:9 ms

        11batchsave10

      • 100:77 ms

        12batchsave100

      • 1000:1955 ms

        13batchsave1000

      • 10000:179073 ms

        14batchsave10000

      • 100000:16710546 ms

        15batchsave100000

  4. 批次 Add 及 SaveChanges 並 dispose context

    • 程式碼
      var count = 100000;
      Stopwatch sw = new Stopwatch();
      sw.Reset();
      sw.Start();
      var batchcount = 100;
      
      for (int i = 0; i < (count / batchcount); i++)
      {
          using (TestNestedEF.Models.TestEntities db = new TestEntities())
          {
              for (int j = 0; j < batchcount; j++)
              {
                  Parent parent = new Parent() { Name = $"{i * batchcount + j}" };
                  parent.Child = new List<Child>() { new Child() { Name = $"{i * batchcount + j}_Son" } };
      
                  db.Parent.Add(parent);
              }
              db.SaveChanges();
          }
      }
      
      sw.Stop();
      sw.ElapsedMilliseconds.Dump();
      
    • 測試筆數
      • 10:12 ms

        16dispose10

      • 100:91 ms

        17dispose100

      • 1000:717 ms

        18dispose1000

      • 10000:9401 ms

        19dispose10000

      • 100000:81462 ms

        20dispose100000

測試結果

處理方式\筆數 10 100 1,000 10,000 100,000
逐筆 Add 並逐筆 SaveChanges 11 187 4,282 401,177 44,827,184
逐筆 Add 及一次 SaveChanges 7 79 2,309 195,678 19,916,508
批次 Add 及 SaveChanges 9 77 1,955 179,073 16,710,546
批次 Add 及 SaveChanges
並 dispose context
12 91 717 9,401 81,462

單位 ms

心得

實驗時間超長的,有些情境的執行時間遠超出我的想像 (使用 逐筆 Add 並逐筆發動 SaveChanges 處理 10 萬筆資料時竟花超過 12 個小時),為了避免造成實驗數據誤差,還不能同時執行其他情境,真的很花時間。 原本打算各個情境都測個三、五次取平均值,讓數字相對準確點,無奈某些情境實在太耗時,不得不放棄,實際執行時間的數字部份就勉強當做趨勢比較參考吧

實際測試前,原本就料想到一定是 逐筆 Add 並逐筆 SaveChanges 速度最慢,原以為 逐筆 Add 及一次 SaveChanges 會有巨大的效能優化,想不到還是極度緩慢,批次 Add 及 SaveChanges 已獲得倍數級改善,而 批次 Add 及 SaveChanges 並 dispose context 竟然出現令人咋舌的速度優化,還好有來參加忠成老師的課程,學到 EntityFramework 這等調校手法就值回票價了

假設沒有這次學習經驗,我想我應該會直接透過 dapper 或是 ado.net 來處理,在效能與便利性上的取捨我個人意見跟忠成老師很接近:沒有放諸四海皆適合的技術及架構,依不同情境選擇最合適的技術才是正確做法,不該一昧堅持特定做法而畫地自限。

參考資訊

3 則留言:

  1. 最後的表格第六欄 header 好像是 100,000
    之前專案也有遇到要塞數十萬筆
    後來也是 try 到用最後的 dispose context
    你可以放 12 小時也是很有耐心 XD

    dapper, ado.net 適合大量資料處理?
    dapper 目前還沒有用過
    ado 的話是指用 SQLCommand 塞?

    在之前有找到使用 SqlBulkCopy 的方式
    覺得蠻不錯的,速度也是非常的快

    回覆刪除
    回覆
    1. 感謝 siuon
      錯誤已改。
      dapper, ado.net 都有 bulk insert 的相關功能,或是用 sp 接 user define table 處理應該也會比較快

      SqlBulkCopy 這是 ef 功能還是擴充套件嗎?


      刪除
    2. SqlBulkCopy 在 System.Data.SqlClient 下
      .Net 很久以前就有的東西,只是最近才知道有這個 XD
      https://msdn.microsoft.com/zh-tw/library/ac44f8yy(v=vs.110).aspx

      刪除