[C#.NET][Entity Framework] 使用 Bulk 改善 Insert/Update/Delete 效能,異動大資料性能比較
續上篇:http://www.dotblogs.com.tw/yc421206/archive/2015/03/18/150757.aspx
得知 EF 在處理多筆資料異動的時候,會把資料分批處理,這會造成流量的損耗,一但面臨大資料,就很難突破效能瓶頸
WHERE (([OrderID] = @0) AND ([ProductID] = @1))',N'@0 int,@1 int',@0=11106,@1=1 exec sp_executesql N'DELETE [dbo].[Order Details] WHERE (([OrderID] = @0) AND ([ProductID] = @1))',N'@0 int,@1 int',@0=11106,@1=2 exec sp_executesql N'DELETE [dbo].[Order Details] WHERE (([OrderID] = @0) AND ([ProductID] = @1))',N'@0 int,@1 int',@0=11106,@1=3
為了解決這問題找到了幾個 EF 的擴充套件,它們具有Bulk Insert/Update/Delete 的功能,也都可以在 Nuget 上找到,它們的實作原理就是改寫 EF 所產生的 T-SQL ,有興趣自行實作的請參考http://www.dotblogs.com.tw/code6421/archive/2010/09/02/17526.aspx,要注意的是各個不同的資料庫廠商都要自行處理,我不太想要處理這種事,便找到了幾個套件
- EntityFramework.Extended:https://github.com/loresoft/EntityFramework.Extended,問世時間較久,但好像沒有 Bulk Insert,API使用起來好像也沒有那麼順,於是放棄,支援資料庫較少
- EntityFramework.BulkInsert:https://efbulkinsert.codeplex.com/,故名思義只有 Insert..
- Z.EntityFramework.Extensions:http://www.zzzprojects.com/entity-framework-extensions/
於是採用 Z.EntityFramework.Extensions,來跟原生的 EF SaveChanges 較量
本文章節
資料庫:http://msftdbprodsamples.codeplex.com/releases/view/105902
-
- 資料表:FactProductInventory,它裡面有 776286 筆
- Entity Framework 版本 6.1.3
- Windows 8.1 x64
存取接口:
{ int RowCount { get; set; } int Insert(int? rowCount = null); int Delete(int? rowCount = null); int Update(int? rowCount = null); }
測試報告程式碼如下
{ private Func<IAccess> _func = null; public string FunctionName { get; set; } public int RowCount { get; set; } public int RunTimes { get; set; } public double CostTime { get; set; } public string Message { get; set; } public TestInfo(Func<IAccess> func, string functionName) { this._func = func; this.FunctionName = functionName; } public void Run(int times) { Console.WriteLine("執行 {0} 測試:", this.FunctionName); while (times-- > 0) { var watch = Stopwatch.StartNew(); var dao = this._func(); watch.Stop(); this.RunTimes++; this.RowCount += dao.RowCount; this.CostTime += watch.Elapsed.TotalMilliseconds; var msg = string.Format("第 {0} 次執行 {1} 測試,花費:{2} ms,成功筆數:{3}", this.RunTimes.ToString("000"), this.FunctionName, watch.Elapsed.TotalMilliseconds.ToString("000.0000"), dao.RowCount); Console.WriteLine(msg); } this.Message = String.Format("執行 {0} 測試,共花費:{1} ms,共執行 {2} 次,總成功筆數:{3}", this.FunctionName, this.CostTime, this.RunTimes, this.RowCount); //Console.WriteLine(this.Message); //return watch.Elapsed; } }EF 組態設定
targetDbContext.Configuration.ValidateOnSaveEnabled = false; targetDbContext.Configuration.LazyLoadingEnabled = false; targetDbContext.Configuration.ProxyCreationEnabled = false;
查詢
為了不讓EF Track,使用了AsNoTracking
@EF6.cs
EF6 class 實作了 IAccess
- 完整程式碼:https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.BatchUpdate/Simple.ORM.BatchUpdate/EF6.cs
- 為了動態找出有哪些資料要更新,所以必須要對 SQL 查詢一次,使用 AsNoTracking。
- 若已經知道有哪些資料,就可以不必查詢,所以我使用 EntityState,參考上篇:http://www.dotblogs.com.tw/yc421206/archive/2015/03/18/150757.aspx。
- SaveChanges 筆數太多會跳出例外 out of memory exception,CUD 都是如此,為解決此問題則使用分批存檔,並使用 BeginTransaction。
- Update 核心片斷程式碼如下:
{ using (var targetDbContext = new TargetDbContext()) { targetDbContext.Configuration.AutoDetectChangesEnabled = false; targetDbContext.Configuration.ValidateOnSaveEnabled = false; targetDbContext.Configuration.LazyLoadingEnabled = false; targetDbContext.Configuration.ProxyCreationEnabled = false; List<FactProductInventory> targets; if (rowCount.HasValue) { targets = targetDbContext.FactProductInventories .OrderBy(o => o.ProductKey) .Skip(0) .Take(rowCount.Value) .AsNoTracking() .ToList(); } else { targets = targetDbContext.FactProductInventories .AsNoTracking() .ToList(); } var random = new Random(Guid.NewGuid().GetHashCode()); var randomInts = new List<int>(Enumerable.Range(1, 1000)); foreach (var target in targets) { var resultInts = randomInts.OrderBy(o => random.Next()).Take(3).ToList(); target.UnitCost = (decimal)random.NextDouble(); target.UnitsBalance = resultInts[0]; target.UnitsIn = resultInts[1]; target.UnitsOut = resultInts[2]; target.MovementDate = DateTime.Now; } foreach (var item in targets) { targetDbContext.Entry(item).State = EntityState.Modified; } try { this.RowCount = targetDbContext.SaveChanges(); //this.RowCount = SaveChanges(targetDbContext, targets, EntityState.Modified); } catch (Exception) { throw; } return this.RowCount; } }
這看似沒有問題,但卻發生了例外,原因是筆數太多了
原來是超過十萬筆就會掛掉
http://stackoverflow.com/questions/5940225/fastest-way-of-inserting-in-entity-framework
解法:分批調用 SaveChanges 儲存,每一百筆存一次,然後再外層使用 BeginTransaction,一但分批 SaveChanges 就必須要加上 BeginTransaction,以免資料出問題
{ var baseCount = 100; var loopCount = sources.Count / baseCount; if (loopCount == 0) { loopCount++; } else if (sources.Count % loopCount != 0) { loopCount++; } var rowCount = 0; using (var beginTarn = targetDbContext.Database.BeginTransaction()) { try { for (int i = 0; i < loopCount; i++) { var targets = sources.Skip(i * baseCount).Take(baseCount); foreach (var item in targets) { targetDbContext.Entry(item).State = state; } rowCount += targetDbContext.SaveChanges(); } beginTarn.Commit(); } catch (Exception) { beginTarn.Rollback(); rowCount = 0; throw; } } return rowCount; }
@ZZZProject.cs
再來看 ZZZProject class,同樣的也實作了 IAccess,個人覺得這個擴充方法相當容易操作
- 完整程式碼:https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.BatchUpdate/Simple.ORM.BatchUpdate/ZZZProject.cs
- 新增:BulkInsert
- 刪除:BulkDelete
- 修改:BulkUpdate
- Update核心片斷程式碼如下:
{ using (var targetDbContext = new TargetDbContext()) { targetDbContext.Configuration.AutoDetectChangesEnabled = false; targetDbContext.Configuration.ValidateOnSaveEnabled = false; targetDbContext.Configuration.LazyLoadingEnabled = false; targetDbContext.Configuration.ProxyCreationEnabled = false; List<FactProductInventory> targets; if (rowCount.HasValue) { targets = targetDbContext.FactProductInventories .OrderBy(o => o.ProductKey) .Skip(0) .Take(rowCount.Value) .AsNoTracking() .ToList(); } else { targets = targetDbContext.FactProductInventories .AsNoTracking() .ToList(); } foreach (var target in targets) { target.UnitCost = 2.2m; target.UnitsBalance = 888; target.UnitsIn = 1; target.UnitsOut = 1; target.MovementDate = DateTime.Now; } targetDbContext.BulkUpdate(targets); this.RowCount = targets.Count; return this.RowCount; } }
完整測試程式碼:
- https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.BatchUpdate/Simple.ORM.BatchUpdate/DeleteUnitTest.cs
- https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.BatchUpdate/Simple.ORM.BatchUpdate/UpdateUnitTest.cs
- https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.BatchUpdate/Simple.ORM.BatchUpdate/InsertUnitTest.cs
測試參數:
- https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.BatchUpdate/Simple.ORM.BatchUpdate/Core.cs
- 若 s_rowCount = null 表示,執行最大筆數 776286
Update 調用端片斷試程式碼如下:
public void EF6_Update_Test() { IAccess EF6 = new EF6(); var test1 = new TestInfo(() => { var datas = EF6.Update(Core.s_rowCount); return EF6; }, "EF Update"); test1.Run(Core.s_runTimes); s_testInfos.Add(test1); } [TestMethod] public void ZZZ_ProjectUpdate_Test() { IAccess ZZZProject = new ZZZProject(); var tset = new TestInfo(() => { var datas = ZZZProject.Update(Core.s_rowCount); return ZZZProject; }, "ZP Update"); tset.Run(Core.s_runTimes); s_testInfos.Add(tset); }
總表現:
Test Name: ZZZ_UpdateResult
Test Outcome: Passed
Result StandardOutput:
Update Test:
執行 ZP Update 測試,共花費:181.7704 ms,共執行 10 次,總成功筆數:1000
執行 EF Update 測試,共花費:357.4071 ms,共執行 10 次,總成功筆數:1000
EF SaveChanges 表現:
Test Name: EF6_Update_Test
Test Outcome: Passed
Result StandardOutput:
執行 EF Update 測試:
第 001 次執行 EF Update 測試,花費:133.5594 ms,成功筆數:100
第 002 次執行 EF Update 測試,花費:023.4115 ms,成功筆數:100
第 003 次執行 EF Update 測試,花費:030.0628 ms,成功筆數:100
第 004 次執行 EF Update 測試,花費:023.2798 ms,成功筆數:100
第 005 次執行 EF Update 測試,花費:023.6131 ms,成功筆數:100
第 006 次執行 EF Update 測試,花費:027.4675 ms,成功筆數:100
第 007 次執行 EF Update 測試,花費:023.9225 ms,成功筆數:100
第 008 次執行 EF Update 測試,花費:023.2349 ms,成功筆數:100
第 009 次執行 EF Update 測試,花費:024.1234 ms,成功筆數:100
第 010 次執行 EF Update 測試,花費:024.7322 ms,成功筆數:100
BulkUpdate 表現:
Test Name: ZZZ_ProjectUpdate_Test
Test Outcome: Passed
Result StandardOutput:
執行 ZP Update 測試:
第 001 次執行 ZP Update 測試,花費:041.3984 ms,成功筆數:100
第 002 次執行 ZP Update 測試,花費:015.5589 ms,成功筆數:100
第 003 次執行 ZP Update 測試,花費:015.4360 ms,成功筆數:100
第 004 次執行 ZP Update 測試,花費:015.5365 ms,成功筆數:100
第 005 次執行 ZP Update 測試,花費:015.4627 ms,成功筆數:100
第 006 次執行 ZP Update 測試,花費:015.5719 ms,成功筆數:100
第 007 次執行 ZP Update 測試,花費:015.6213 ms,成功筆數:100
第 008 次執行 ZP Update 測試,花費:015.3660 ms,成功筆數:100
第 009 次執行 ZP Update 測試,花費:015.8363 ms,成功筆數:100
第 010 次執行 ZP Update 測試,花費:015.9824 ms,成功筆數:100
測試:執行 Updtae 1次,處理最大筆數 776286 筆
泡杯咖啡上個廁所再來看結果………
總結果:
Test Name: ZZZ_UpdateResult
Test Outcome: Passed
Result StandardOutput:
Update Test:
執行 ZP Update 測試,共花費:18841.3898 ms,共執行 1 次,總成功筆數:776286
執行 EF Update 測試,共花費:217409.085 ms,共執行 1 次,總成功筆數:776286
EF SaveChanges 表現:
Test Name: EF6_Update_Test
Test Outcome: Passed
Result StandardOutput:
執行 EF Update 測試:
第 001 次執行 EF Update 測試,花費:217409.0850 ms,成功筆數:776286
BulkUpdate 表現:
Test Name: ZZZ_ProjectUpdate_Test
Test Outcome: Passed
Result StandardOutput:
執行 ZP Update 測試:
第 001 次執行 ZP Update 測試,花費:18841.3898 ms,成功筆數:776286
測試:執行所以測試 10 次,每次處理最大筆數 776286 筆
到公司樓下跑個十圈再回來…
沒意外的話,InsertTest 會因為資料已經存在而發生例外;DeleteTest 會因為因為沒資料所以只處理一次,所以重點只能擺在 UpdateTest
總表現:
Test Name: ZZZ_Delete_Result
Test Outcome: Passed
Result StandardOutput:
Update Test:
執行 ZP Update 測試,共花費:186649.3746 ms,共執行 10 次,總成功筆數:7762860
執行 EF Update 測試,共花費:2589044.9464 ms,共執行 10 次,總成功筆數:7762860
Insert Test:
Detete Test:
執行 EF Delete 測試,共花費:261648.1299 ms,共執行 10 次,總成功筆數:776286
ZZZ_Project_Delete_Test 因不明原因 Fail,我也不打算再執行這一次的測試
Update-EF SaveChanges 表現:
Test Name: EF6_Update_Test
Test Outcome: Passed
Result StandardOutput:
執行 EF Update 測試:
第 001 次執行 EF Update 測試,花費:215241.9320 ms,成功筆數:776286
第 002 次執行 EF Update 測試,花費:213875.3260 ms,成功筆數:776286
第 003 次執行 EF Update 測試,花費:214664.0922 ms,成功筆數:776286
第 004 次執行 EF Update 測試,花費:213866.5512 ms,成功筆數:776286
第 005 次執行 EF Update 測試,花費:217631.0672 ms,成功筆數:776286
第 006 次執行 EF Update 測試,花費:217410.1443 ms,成功筆數:776286
第 007 次執行 EF Update 測試,花費:224070.8331 ms,成功筆數:776286
第 008 次執行 EF Update 測試,花費:361389.6587 ms,成功筆數:776286
第 009 次執行 EF Update 測試,花費:356093.4159 ms,成功筆數:776286
第 010 次執行 EF Update 測試,花費:354801.9258 ms,成功筆數:776286
Update-BulkUpdate 表現:
Test Name: ZZZ_ProjectUpdate_Test
Test Outcome: Passed
Result StandardOutput:
執行 ZP Update 測試:
第 001 次執行 ZP Update 測試,花費:19262.5489 ms,成功筆數:776286
第 002 次執行 ZP Update 測試,花費:18617.8749 ms,成功筆數:776286
第 003 次執行 ZP Update 測試,花費:18662.7457 ms,成功筆數:776286
第 004 次執行 ZP Update 測試,花費:18364.5483 ms,成功筆數:776286
第 005 次執行 ZP Update 測試,花費:18454.8157 ms,成功筆數:776286
第 006 次執行 ZP Update 測試,花費:19023.4126 ms,成功筆數:776286
第 007 次執行 ZP Update 測試,花費:18514.5328 ms,成功筆數:776286
第 008 次執行 ZP Update 測試,花費:18735.1422 ms,成功筆數:776286
第 009 次執行 ZP Update 測試,花費:18435.3989 ms,成功筆數:776286
第 010 次執行 ZP Update 測試,花費:18578.3546 ms,成功筆數:776286
結論:
資料量少,百筆內,只需要用原生 SaveChanges,Z 還發揮不了明顯作用,千筆內,可以看出差距越來越大,不過尚可接受,萬筆測試 Z Project 就狠甩原生 EF
謎樣的 Z,大幅度的改善 EF Batch更新的問題,除了能讓我們繼續享受 EF 對資料庫的快速操作,同時也能品嘗到高效的甜頭
補充:
加入 Dapper
由於我功力極差,只會動態把SQL串起來一次丟出去,寫不出像 Z project 那樣的高效動態批次異動,所以效能跟 EF 差不多。
所以,如果把資料一次丟跟分批丟來比較的話,執行時間是差不多的,如下圖,萬筆資料差沒多少..
十萬筆才有很明顯的的差距
本文出自:http://www.dotblogs.com.tw/yc421206/archive/2015/03/20/150791.aspx
專案連結:https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.BatchUpdate/
若有謬誤,煩請告知,新手發帖請多包涵
Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET