C# SQLServerでBulkCopyして高速化

大量のデータをInsertする場合、SQLサーバとの通信がSQLの実行のたびに時間がかかってしまう。

１万件のデータをInsert

1. Insertを1件毎に実行：98.32秒
2. Insertを1000件毎に実行：15.34秒
3. Bulkを使って1000件たまったら実行：0.57秒

BulkCopyを使用すると、速度改善

　データテーブルをそのままInsertすることで20倍以上の速度改善が見込まれる。

　SqlBulkCopy Class (System.Data.SqlClient) | Microsoft Docs

　C# bulkcopy to SQL Server - Stack Overflow

では、１万件をUpdateしたい場合はどうするか？

２通り

　・すべて同一の値で更新する場合は、Update分１回で実行

　・BulkCopy

前者は詳細省く。

BulkCopyでUpdate

　Tempテーブルにデータを一時的にInsertして、ストアド一括更新（フェッチで回す）

　２回SQLが実行される。

　通信のコストが安くなり、効率化。

　Tempテーブルの種類

　・ローカル一時テーブル：トランザクション内で有効なテーブル（＃テーブル名）

　・グローバル一時テーブル：すべてのユーザで使用できるテーブル（＃#テーブル名）

　※一時テーブルを参照するすべてのユーザーのセッションが切断されると削除されます。

　今回使用するのは、ローカル一時テーブル。

CREATE TABLE #TempTable(
     COL1 varchar(10)
    ,COL2 varchar(10)
)

　※Tempテーブルを使用する際は、SQLサーバのTempDbの容量の設計が必要です。

　最大サイズを超えると容量が自動的に拡張されるため、ラッチが発生します。

　https://technet.microsoft.com/ja-jp/library/ms175527(v=sql.105).aspx

BulkCopyのコード

 トランザクション内でTempテーブルを作成して、データを挿入する。

※トランザクションを抜けるとTempテーブルは削除されます。

DataTable dt = new DataTable("Employees");
dt.Columns.Add("Name");
dt.Columns.Add("LastName");

foreach (var employee in context.Employees.AsEnumerable())
{
    dt.Rows.Add(employee.Name, employee.LastName);
}

Console.WriteLine(dt.Rows[0]["Name"]);

Console.WriteLine("Loading data into SQL");

using (SqlConnection conn = new SqlConnection("user id=<ID>;" +
                                              "password=<Password>;server=<Server>;" +
                                              "Trusted_Connection=yes;" +
                                              "database=<DB>; " +
                                              "connection timeout=30"))
{
    using (SqlCommand command = new SqlCommand("", conn))
    {
        try
        {
             conn.Open();

             // Creating temp table on database
             command.CommandText = "CREATE TABLE #TmpTable(...)";
             command.ExecuteNonQuery();

             // Bulk insert into temp table
             using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
             {
                 bulkcopy.BulkCopyTimeout = 660;
                 bulkcopy.DestinationTableName = "#TmpTable";
                 bulkcopy.WriteToServer(dt);
                 bulkcopy.Close();
             }
         }
         catch (Exception ex)
         {
             Console.WriteLine("Bulk load to SQL failed");// Handle exception properly
         }
         finally
         {
             conn.Close();
             Console.WriteLine("table loaded into SQL");
         }
     }
}