オンライン・バッチ処理高速化 測定

Dapperを使って10万件のデータの追加・更新・削除を比べてみました。

結果は以下の通りでBulkCopyが1/10から1/20倍高速になっています。

意外なのがDapperのListを渡した更新処理が思った以上に速い点です。
10万件をループして更新すると測定するのが大変なくらい遅くなるのですが、MiniProfilerで発行されているSQLを見ると10万回実行されているにもかかわらずストアドの2から4倍程度で処理できてきます。

処理	時間(秒)
Dapperで10万件のListのINSERT文の実行	38.10
ストアドでINSERT文の実行 10万件	11.79
BulkCopy 10万件	0.55
Dapperで10万件のListのUPDATE文の実行 10万件	37.87
ストアドでカーソルを利用したUPDATE文の実行 10万件	17.81
BulkCopyで一時テーブルに10万件出力後、一時テーブルを利用したUPDATE文	0.74
Dapperで10万件のListのDELETE文の実行	37.96
ストアドでカーソルを利用したDELETE文の実行 10万件	17.38
BulkCopyで一時テーブルに10万件出力後、一時テーブルを利用したDELETE文	1.36

参考までにUPDATE処理のプログラムを載せておきます

Dapperで10万件のListのUPDATE文実行

var list = connection.Query("SELECT * FROM TestTable");
connection.Execute("UPDATE TestTable SET Value = @Value WHERE Id = @Id", list);

ストアドでカーソルを利用したUPDATE文の実行 10万件

Create PROCEDURE dbo.UpdateData
AS
DECLARE @Val nvarchar(50)
DECLARE @Id int
  
DECLARE TestCur cursor FOR SELECT Id,[Value] FROM TestTable 
OPEN TestCur
 
FETCH NEXT FROM TestCur INTO @Id,@Val
WHILE (@@fetch_status = 0)
BEGIN
	UPDATE TestTable SET TestTable.Value = @val WHERE CURRENT OF TestCur;
    FETCH NEXT FROM TestCur INTO @Id,@Val
end 
CLOSE TestCur
DEALLOCATE TestCur
RETURN

BulkCopyで一時テーブルに10万件出力後、一時テーブルを利用したUPDATE文

connection.Execute("SELECT * INTO #Temp FROM TestTable WHERE 1=0");
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(baseConnection))
{
   bulkCopy.DestinationTableName = "#Temp";
   bulkCopy.WriteToServer(
　    connection.Query("SELECT * FROM TestTable")
      .Select(x => new { id = x.Id, value = x.Value })
      .ToDataReader()
  );
}
connection.Execute("UPDATE TestTable SET TestTable.Value = #Temp.Value FROM TestTable INNER JOIN #Temp ON TestTable.Id = #Temp.Id");

MiniProfilerの結果

3.3ms
SELECT * INTO #Temp FROM TestTable WHERE 1=0

250.2ms for 2 query duplicates
SELECT * FROM TestTable

458ms
UPDATE TestTable SET TestTable.Value = #Temp.Value FROM TestTable INNER JOIN #Temp ON TestTable.Id = #Temp.Id

18147.5ms
UpdateData3

32383.7ms for 100000 query duplicates
UPDATE TestTable SET Value = @Value WHERE Id = @Id

今回のテストはノートPCでDBとアプリが同じPCなので実際の環境とは異なりますが、参考にはなると思います。