[netCore] how to improvement performance of Bulk in Dapper

2018-05-13

3040
0
netCore
2021-01-02

Dapper雖然有提供Delete、Update、Insert批次處理，但我個人卻很少直接呼叫該函式，

因為這不是我所想要的資料批次處理效能

星期五，和同事討論到Dapper相關Bulk函式，我以前用Dapper經驗告訴同事，

Bulk函式，只是依照IList數量，幫你產生對應insert ,delete,update sql，

另外，依照SQL Server WAL行為，如果client沒有傳入transaction參數，

我們的datahelper建議自行new transaction包起來，一是效能考量，

二是資料一致和正確性(假設你預計結果應該會有10筆資料，但執行結束後結果卻只有8筆)，

下面我實際驗證Dapper所提供Bulk效能和行為。

我的webapi code很簡單

[HttpGet("BulkTest/{max}")]
       public async Task<string> BulkTest(int max)
       {
           var datas = new List<EventLogModule>();
           var dt = new DataTable();
           ProfilingSession.Current.AddTag("BulkTest");
           using (ProfilingSession.Current.Step(() => "Handle Request - /"))
           {
               using (ProfilingSession.Current.Step(() => "generate datas"))
               {
                   datas = generate(max);
               }
               using (ProfilingSession.Current.Step(() => "Convert datatable"))
               {
                   dt = datas.ToDataTable(new string[] { "EventID", "LogLevel", "Message", "Exception" });
               }
               using (ProfilingSession.Current.Step(() => "bulk insert to sql server"))
               {
                   await _eventLogRepository.BulkInsertAsync(datas);
 
                   /**
                   var mytvp = new DynamicParameters();
                   mytvp.AddDynamicParams(new { DataList = dt.AsTableValuedParameter("UT_EventLog") });
                   await _eventLogRepository.ExecuteSPAsync("dbo.usp_InsertEventLogWithTvp", mytvp);
                   **/
 
               }
               return await Task.FromResult($"number of record:{datas.Count}");
           }
       }

Dapper預設BulkInsert新增100筆且未包交易

sql profiler

從sql profiler看的出來，如同我之前所說的，

dapper就只是傳了100句sql給sql server(ORM為了要支援多種資料庫，這樣的行為是可以理解的)，

但交易效能其實相當低落。

CoreProfiler上看BulkInsert執行時間:2470 ms

Dapper預設BulkInsert新增100筆但自行包交易

sql profiler

Dapper雖然還是傳了100句sql給SQL Server，

但因為自行包交易，所以所有transaction ID都相同，

這樣才能達到資料同生共死效果，但交易效能改善有限。

CoreProfiler上看BulkInsert執行時間:2419 ms (從2470減少為2419)

如果要讓SQL Server交易效能飛快，基本上也要避免Dapper進行row by row產生相對應SQL，

因為每句SQL都會進行TDS轉換，這些轉換成本你絕對不能輕忽，

下面我改用TVP方式來改善批次處理效能，

達到我真正想要的批次處理。

Note:ado.net版本可參考我以前文章[C#][SQL SERVER] 提高 Insert 效能

我預先在SQL Server建立 User Table Type和處理Insert的SP，如下

create type dbo.UT_EventLog as table
(
	[EventID] [int] NOT NULL primary key nonclustered,
	[LogLevel] [varchar](20) NOT NULL,
	[Message] [nvarchar](2000) NULL,
	[Exception] [nvarchar](2000) NULL
)
with(memory_optimized=on)

CREATE PROC dbo.usp_InsertEventLogWithTvp
@DataList UT_EventLog READONLY
AS
begin
set nocount on;
begin tran
insert into [dbo].[EventLog]([EventID],[LogLevel],[Message],[Exception])
select [EventID],[LogLevel],[Message],[Exception] from @DataList ;
commit 
end

controller我就改用tvp傳入(避免row by row產生sql)

var mytvp = new DynamicParameters();
                  mytvp.AddDynamicParams(new { DataList = dt.AsTableValuedParameter("UT_EventLog") });
                  await _eventLogRepository.ExecuteSPAsync("dbo.usp_InsertEventLogWithTvp", mytvp);

webapi同樣是新增100筆資料

sql profiler

CoreProfiler上看BulkInsert執行時間:150 ms (從2470減少為150，交易效能提高約16倍)