余小章 @ 大內殿堂

續上篇：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/

1. 1. 已支援的資料庫：http://www.zzzprojects.com/guides/entity-framework-extensions/index.html
   2. 使用手冊：http://www.zzzprojects.com/entity-framework-extensions/#anchor-section-features

 

於是採用 Z.EntityFramework.Extensions，來跟原生的 EF SaveChanges 較量

本文章節

準備環境

Update for EF6

Update for ZZZ.Project

Update 調用端

測試：執行 Update 10次，每次處理 100 筆

測試：執行 Updtae 1次，處理最大筆數 776286 筆

測試：執行所以測試 10 次，每次處理最大筆數 776286 筆

 

---

準備環境

資料庫：http://msftdbprodsamples.codeplex.com/releases/view/105902 

1. 1. 資料表：FactProductInventory，它裡面有 776286 筆
   2. Entity Framework 版本 6.1.3
   3. Windows 8.1 x64

存取接口：

{
    int RowCount { get; set; }

    int Insert(int? rowCount = null);

    int Delete(int? rowCount = null);

    int Update(int? rowCount = null);
}

 

測試報告程式碼如下

https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.BatchUpdate/Simple.ORM.BatchUpdate/TestInfo.cs

{
    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

---

Update for EF6

@EF6.cs

EF6 class 實作了 IAccess

1. 完整程式碼：https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.BatchUpdate/Simple.ORM.BatchUpdate/EF6.cs
2. 為了動態找出有哪些資料要更新，所以必須要對 SQL 查詢一次，使用 AsNoTracking。
3. 若已經知道有哪些資料，就可以不必查詢，所以我使用 EntityState，參考上篇：http://www.dotblogs.com.tw/yc421206/archive/2015/03/18/150757.aspx。
4. SaveChanges 筆數太多會跳出例外 out of memory exception，CUD 都是如此，為解決此問題則使用分批存檔，並使用 BeginTransaction。
5. 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;
}

 

 

---

Update for ZZZ.Project

@ZZZProject.cs

再來看 ZZZProject class，同樣的也實作了 IAccess，個人覺得這個擴充方法相當容易操作

1. 完整程式碼：https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.BatchUpdate/Simple.ORM.BatchUpdate/ZZZProject.cs
2. 新增：BulkInsert
3. 刪除：BulkDelete
4. 修改：BulkUpdate
5. 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;
    }
}

---

Update 調用端

完整測試程式碼：

• 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);
}

 

---

測試：執行 Update 10次，每次處理 100 筆

 

總表現：

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

https://dotblogsamples.codeplex.com/SourceControl/latest#Simple.ORM.BatchUpdate/Simple.ORM.BatchUpdate/DapperAccess.cs

 

由於我功力極差，只會動態把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