.NETでSQL Serverへのデータ登録を高速化する方法

この記事は公開から3年以上経過しています。

C#やVB.NETを使った開発でMicrosoft® SQL Serverに大量のデータを登録する際に、ADO.NETのバルクコピー機能を使いデータの登録を高速化する方法を紹介します。

動作検証で利用するDBの定義

テーブル構成は、以下の通りです。

CREATE TABLE BULK_COPY_TBL(
  id int IDENTITY(0, 1) NOT NULL,
  name nvarchar(100),
  age tinyint
)

動作検証サンプルソースコード（C#)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Linq;

namespace BulkCopyTest211019
{
    internal static class Program
    {
        private static void Main(string[] args)
        {
            Console.Title = "SQL Server BulkCopy Test";

            // テストデータ100,000件生成
            var rndAge = new Random();
            var data = Enumerable.Range(1, 100000).Select(n => new Person { Name = $"User{n}", Age = (byte)rndAge.Next(128) }).ToArray();

            using (var con = new SqlConnection("Server=localhost;Database=TestDB;Integrated Security=true;"))
            {
                con.Open();
                var sw = new Stopwatch();

                // 1. Insert文で1件ずつ登録する場合
                Console.WriteLine("① Insert文で1件ずつ登録");
                sw.Start();
                foreach (var datum in data)
                {
                    using (var cmd = new SqlCommand("INSERT INTO BULK_COPY_TBL(Name, Age) VALUES(@Name, @Age)", con))
                    {
                        cmd.Parameters.AddRange(new[]
                        {
                            new SqlParameter("@Name", SqlDbType.NVarChar){Value = datum.Name },
                            new SqlParameter("@Age", SqlDbType.TinyInt){Value = datum.Age },
                        });
                        cmd.ExecuteNonQuery();
                    }
                }
                sw.Stop();
                Console.WriteLine($"経過時間 {sw.Elapsed.TotalMilliseconds}ms");

                Console.WriteLine();

                // 2. バルクコピーで一括登録する場合
                Console.WriteLine("② BulkCopyで一括登録");
                sw.Restart();
                var dt = new DataTable();
                dt.Columns.AddRange(new[]
                {
                    new DataColumn("id", typeof(int)){AutoIncrement = true},
                    new DataColumn("name", typeof(string)),
                    new DataColumn("age", typeof(byte))
                });
                foreach (var datum in data)
                {
                    dt.Rows.Add(null, datum.Name, datum.Age);
                }
                using (var bulkCopy = new SqlBulkCopy(con))
                {
                    // コピー先テーブル名
                    bulkCopy.DestinationTableName = "BULK_COPY_TBL";
                    // データテーブルをコピー
                    bulkCopy.WriteToServer(dt);
                }
                sw.Stop();
                Console.WriteLine($"経過時間 {sw.Elapsed.TotalMilliseconds}ms");
            }

            Console.ReadKey();
        }

        // DTO
        private class Person
        {
            public string Name { get; set; }

            public byte Age { get; set; }
        }
    }
}

説明

以下の2方法で10万件のデータをDB登録して、それぞれの経過時間を表示します。

1. Insert文で1件ずつ登録
2. バルクコピーで一括登録

実行結果

デバッガ上での実行結果は以下のとおりです。

検証環境上ではバルクコピーを用いると1行ずつInsertする場合と比べて120倍以上の速度で登録ができていることが確認できます。但し、この例ではDatatableを使いインメモリで処理を行うことからメモリ使用量は増えることに注意が必要です(インメモリにしない場合は、自前でカスタムDbDataReaderを実装)。

参考ウェブサイトなど

• Microsoft Docs
  バルク コピー操作の単一実行

以上です。