[C#.NET][Entity Framework] 使用 Bulk 改善 Insert/Update/Delete 效能,異動大資料性能比較

[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,要注意的是各個不同的資料庫廠商都要自行處理,我不太想要處理這種事,便找到了幾個套件

    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. 資料表: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;
    }
}

 

 

這看似沒有問題,但卻發生了例外,原因是筆數太多了

image

 

原來是超過十萬筆就會掛掉

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 調用端

完整測試程式碼:

測試參數:

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 筆

image

 

總表現:

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

image

 

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

image

 

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

image

 

 

 

 

 

 

測試:執行 Updtae 1次,處理最大筆數 776286 筆

image

 

泡杯咖啡上個廁所再來看結果………

總結果:

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

image

 

 

EF SaveChanges 表現:

Test Name:    EF6_Update_Test
Test Outcome:    Passed
Result StandardOutput:   
執行 EF Update 測試:
第 001 次執行 EF Update 測試,花費:217409.0850 ms,成功筆數:776286

image\

 

BulkUpdate 表現:

Test Name:    ZZZ_ProjectUpdate_Test
Test Outcome:    Passed
Result StandardOutput:   
執行 ZP Update 測試:
第 001 次執行 ZP Update 測試,花費:18841.3898 ms,成功筆數:776286

image

 

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

image

 

到公司樓下跑個十圈再回來…

 

 

 

沒意外的話,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

image

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

image

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

image

結論:

資料量少,百筆內,只需要用原生 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 差不多。

所以,如果把資料一次丟跟分批丟來比較的話,執行時間是差不多的,如下圖,萬筆資料差沒多少..

image

 

十萬筆才有很明顯的的差距

image


本文出自: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

Image result for microsoft+mvp+logo