より速いSQL一括挿入

40874 ワード
performance dotnet sql csharp テキストリンク
Twitterで私に従ってくださいNewsletter | もともと公開timdeschryver.dev .
昨年、私は新しいアプリケーションに複数のレガシーアプリケーションを移行しなければなりませんでした.これらの新しいアプリケーションは独自のデータベース構造を持っていたので、レガシーアプリケーションから新しいアプリケーションにデータを移行する必要がありました.
各移行では、コードの品質は、過去の移行からの経験のために改善し、私の全体的な知識.
コード品質が向上しただけでなく、データの量が増加したけれども、非常に顕著な速度差(良い方法で)もありました.
この投稿では、スローインサートをインクリメントして高速インサートにインクリメントします.
あなたは最速の技術に興味を持っている場合は、移動してくださいSQL Bulk Copy .
これは、SQLテーブルにn個の顧客(ID、名前、およびいくつかの連絡先情報を持つ)のコレクションを挿入することで行います.
record Customer (
  Guid Id,
  string FirstName,
  string LastName,
  string Street,
  string City,
  string State,
  string PhoneNumber,
  string EmailAddress )
この例ではCustomers テーブルはC≧オブジェクトと同じ構造です.
CREATE TABLE [dbo].[Customers] (
  [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL
    CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED,
  [LastName] [nvarchar](255) NULL,
  [FirstName] [nvarchar](255) NULL,
  [Street] [nvarchar](255) NULL,
  [City] [nvarchar](255) NULL,
  [State] [nvarchar](255) NULL,
  [PhoneNumber] [nvarchar](255) NULL,
  [EmailAddress] [nvarchar](255) NULL
)
いくつかのベンチマークを見てみましょう.

単純挿入
最も簡単なアプローチはシングルを作成することですSqlCommand コレクション内の各顧客ごとに1つずつ挿入します.
var cmdText = @"
    insert into dbo.Customers (Id, FirstName, LastName, Street, City, State, PhoneNumber, EmailAddress)
    values (@Id, @FirstName, @LastName, @Street, @City, @State, @PhoneNumber, @EmailAddress)";

foreach (var customer in customers)
{
    using (var connection = new SqlConnection(connectionString))
    {
        var command = new SqlCommand(cmdText, connection);
        command.Parameters.AddWithValue("@Id", customer.Id);
        command.Parameters.AddWithValue("@FirstName", customer.FirstName);
        command.Parameters.AddWithValue("@LastName", customer.LastName);
        command.Parameters.AddWithValue("@Street", customer.Street);
        command.Parameters.AddWithValue("@City", customer.City);
        command.Parameters.AddWithValue("@State", customer.State);
        command.Parameters.AddWithValue("@PhoneNumber", customer.PhoneNumber);
        command.Parameters.AddWithValue("@EmailAddress", customer.EmailAddress);

        connection.Open();
        command.ExecuteNonQuery();
    }
}
結果
Simple insert for 1 customer: 11ms
Simple insert for 10 customers: 121ms
Simple insert for 100 customers: 1122ms
Simple insert for 1_000 customers: 10457ms
Simple insert for 10_000 customers: 161930ms
Simple insert for 100_000 customers: SKIPPED
Simple insert for 1_000_000 customers: SKIPPED
これを少し微調整して、SQLコマンドごとに接続を共有すると、少し良い結果を得ることができます.
var cmdText = @"
    insert into dbo.Customers (Id, FirstName, LastName, Street, City, State, PhoneNumber, EmailAddress)
    values (@Id, @FirstName, @LastName, @Street, @City, @State, @PhoneNumber, @EmailAddress)";

using (var connection = new SqlConnection(connectionString))
{
    foreach (var customer in customers)
    {
        var command = new SqlCommand(cmdText, connection);
        command.Parameters.AddWithValue("@Id", customer.Id);
        command.Parameters.AddWithValue("@FirstName", customer.FirstName);
        command.Parameters.AddWithValue("@LastName", customer.LastName);
        command.Parameters.AddWithValue("@Street", customer.Street);
        command.Parameters.AddWithValue("@City", customer.City);
        command.Parameters.AddWithValue("@State", customer.State);
        command.Parameters.AddWithValue("@PhoneNumber", customer.PhoneNumber);
        command.Parameters.AddWithValue("@EmailAddress", customer.EmailAddress);

        connection.Open();
        command.ExecuteNonQuery();
    }
}
結果
Simple insert (shared connection) for 1 customer: 11ms
Simple insert (shared connection) for 10 customers: 119ms
Simple insert (shared connection) for 100 customers: 958ms
Simple insert (shared connection) for 1_000 customers: 7923ms
Simple insert (shared connection) for 10_000 customers: 130205ms
Simple insert (shared connection) for 100_000 customers: SKIPPED
Simple insert (shared connection) for 1_000_000 customers: SKIPPED

SQLコマンドテキストの生成
SQL接続の開始と終了は高価な操作です.
それで、我々が1つのSQLコマンドだけを実行するならば、どうですか?
var cmdText = customers.Aggregate(
    new StringBuilder(),
    (sb, customer) => sb.AppendLine(@$"
        insert into dbo.Customers (Id, FirstName, LastName, Street, City, State, PhoneNumber, EmailAddress)
        values('{customer.Id}', '{customer.FirstName}', '{customer.LastName}', '{customer.Street}', '{customer.City}', '{customer.State}', '{customer.PhoneNumber}', '{customer.EmailAddress}')")
);

using (var connection = new SqlConnection(connectionString))
{
    var command = new SqlCommand(cmdText.ToString(), connection);
    connection.Open();
    command.ExecuteNonQuery();
}
結果
Manual insert for 1 customer: 10ms
Manual insert for 10 customers: 13ms
Manual insert for 100 customers: 39ms
Manual insert for 1_000 customers: 557ms
Manual insert for 10_000 customers: 17006ms
Manual insert for 100_000 customers: 23637ms
Manual insert for 1_000_000 customers: SKIPPED
OK、それはすでによりよいです、しかし、これは欠点を持ちます.
SQLコマンドを手動でエスケープする必要があります.
スポイラー.我々はそれを高速にします.

エンティティ
のパフォーマンスを見てみましょうEntity Framework .
を使用することを忘れないでくださいAddRange メソッドは、コレクション全体を反復処理して、レコードをAdd メソッド.The AddRange 方法はかなり速いのでdisables change detection 自動的に.
using (var context = new CustomersContext())
{
    context.Customers.AddRange(customers);
    context.SaveChanges();
}
生成されたSQLコマンドを使用するとSQL Extended Events ) Entity Frameworkが複数のSQL INSERTステートメントを生成することに注目します.各々の声明は一度に複数の顧客を挿入します.
exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Customers] ([Id], [City], [EmailAddress], [FirstName], [LastName], [PhoneNumber], [State], [Street])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7),
(@p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15),
...;
',N'@p0 uniqueidentifier,@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 nvarchar(4000),@p4 nvarchar(4000),@p5 nvarchar(4000),@p6 nvarchar(4000),@p7 nvarchar(4000),@p8 uniqueidentifier,@p9 nvarchar(4000),@p10 nvarchar(4000),@p11 nvarchar(4000),@p12 nvarchar(4000),@p13 nvarchar(4000),@p14 nvarchar(4000),@p15 nvarchar(4000)...',@p0='FEA8EAE8-58E1-469B-951C-4DABD0FEF48C',@p1=N'City 1',@p2=N'EmailAddress 1',@p3=N'FistName 1',@p4=N'LastName 1',@p5=N'PhoneNumber 1',@p6=N'State 1',@p7=N'Street 1',@p8='E77C70F2-86B5-45C4-8A67-D95F81C42004',@p9=N'City 66672',@p10=N'EmailAddress 66672',@p11=N'FistName 66672',@p12=N'LastName 66672',@p13=N'PhoneNumber 66672',@p14=N'State 66672',@p15=N'Street 66672'...'
結果:
Entity Framework insert for 1 customer: 10ms
Entity Framework insert for 10 customers: 13ms
Entity Framework insert for 100 customers: 38ms
Entity Framework insert for 1_000 customers: 155ms
Entity Framework insert for 10_000 customers: 1015ms
Entity Framework insert for 100_000 customers: 12290ms
Entity Framework insert for 1_000_000 customers: 119269ms

table valueパラメータ
Entity Frameworkを使用すると、挿入を高速化することがわかります.
これはレコードごとにレコードの代わりに一度に複数のレコードを挿入するためです.
これは、Generating the SQL command text このような作業を反映する.
しかし、我々はすぐに実体フレームワークがバッチで挿入文をチョップする理由をすぐに経験することができます.
1000以上のレコードを挿入しようとすると、SQLはエラーをスローします.
The number of row value expressions in the INSERT statement
exceeds the maximum allowed number of 1000 row values
我々は、この制限を回避することができますTable Valued Parameter . この解決策のために、最初のステップは、ユーザー定義のテーブル型を作成することです.
SQLクエリを実行するときに、後でこのテーブル型を使用する(読み取り、それを顧客と共有する).
CREATE TYPE [dbo].[CustomersTableType] AS TABLE (
    [Id] [uniqueidentifier] NOT NULL,
    [LastName] [nvarchar](255) NOT NULL,
    [FirstName] [nvarchar](255) NOT NULL,
    [Street] [nvarchar](255) NOT NULL,
    [City] [nvarchar](255) NOT NULL,
    [State] [nvarchar](255) NOT NULL,
    [PhoneNumber] [nvarchar](255) NOT NULL,
    [EmailAddress] [nvarchar](255) NOT NULL
)
次に、顧客リストをAに変換しなければなりませんDataTable SQLクエリにデータを渡すことができます.SQLパラメーターの型名を上記の表型に指定された名前に割り当てることを忘れないでください.
SQLクエリでは、テーブル型パラメーターに割り当てられたすべての顧客を選択し、Customersテーブルに挿入します.
このようにして、1つのSQLクエリを実行します.
var cmdText = @"
    insert into dbo.Customers (Id, FirstName, LastName, Street, City, State, PhoneNumber, EmailAddress)
    select Id, FirstName, LastName, Street, City, State, PhoneNumber, EmailAddress
    from @customers";

using (var connection = new SqlConnection(connectionString))
{
    var command = new SqlCommand(cmdText, connection);
    var param = command.Parameters.AddWithValue("@customers", ToDataTable(customers));
    param.TypeName = "dbo.CustomersTableType";
    connection.Open();
    command.ExecuteNonQuery();
}
結果
Table Valued Parameter insert for 1 customer: 12ms
Table Valued Parameter insert for 10 customers: 13ms
Table Valued Parameter insert for 100 customers: 15ms
Table Valued Parameter insert for 1_000 customers: 49ms
Table Valued Parameter insert for 10_000 customers: 108ms
Table Valued Parameter insert for 100_000 customers: 2090ms
Table Valued Parameter insert for 1_000_000 customers: 12259ms

SQL一括コピー
SQLには、大量のデータをインポートするための組み込み機構がありますBulk Insert . 幸運にも私たちのために、DotNetはSqlBulkCopy クラス.
他の解決策に対する目に見えるパフォーマンスの利点の他に、我々はまた、いくつかのOptions .
使うSqlBulkCopy , クラスの新しいインスタンスを作成し、SQL送信先テーブルを提供する必要があります.
顧客リストに顧客リストを書くときWriteToServer メソッドSqlBulkCopy このメソッドは、コレクションがDataTable .
using (var copy = new SqlBulkCopy(connectionString))
{
    copy.DestinationTableName = "dbo.Customers";
    // Add mappings so that the column order doesn't matter
    copy.ColumnMappings.Add(nameof(Customer.Id), "Id");
    copy.ColumnMappings.Add(nameof(Customer.FirstName), "FirstName");
    copy.ColumnMappings.Add(nameof(Customer.LastName), "LastName");
    copy.ColumnMappings.Add(nameof(Customer.Street), "Street");
    copy.ColumnMappings.Add(nameof(Customer.City), "City");
    copy.ColumnMappings.Add(nameof(Customer.State), "State");
    copy.ColumnMappings.Add(nameof(Customer.PhoneNumber), "PhoneNumber");
    copy.ColumnMappings.Add(nameof(Customer.EmailAddress), "EmailAddress");

    copy.WriteToServer(ToDataTable(customers));
}
我々がフードの下で何をするかについてのもう一つの観察をするときSQL Extended Events ), 私たちはSqlBulkCopy 次のSQLインポート文を生成します.
select @@trancount; SET FMTONLY ON select * from dbo.Customers SET FMTONLY OFF exec ..sp_tablecollations_100 N'[dbo].[Customers]'
insert bulk dbo.Customers ([Id] UniqueIdentifier, [LastName] NVarChar(255) COLLATE Latin1_General_CI_AS, [FirstName] NVarChar(255) COLLATE Latin1_General_CI_AS, [Street] NVarChar(255) COLLATE Latin1_General_CI_AS, [City] NVarChar(255) COLLATE Latin1_General_CI_AS, [State] NVarChar(255) COLLATE Latin1_General_CI_AS, [PhoneNumber] NVarChar(255) COLLATE Latin1_General_CI_AS, [EmailAddress] NVarChar(255) COLLATE Latin1_General_CI_AS)
結果
Bulk insert for 1 customer: 3ms
Bulk insert for 10 customers: 3ms
Bulk insert for 100 customers: 5ms
Bulk insert for 1_000 customers: 17ms
Bulk insert for 10_000 customers: 187ms
Bulk insert for 100_000 customers: 1921ms
Bulk insert for 1_000_000 customers: 9510ms

結論
我々は、大量の挿入を速くするために複数の戦術があることを見ることができます.
我々は常にそこに最速のソリューションを使用する必要がありますが、私はそれが良い別のシナリオで使用できるオプションを知っていると思う.より小さなコレクションのために、それはおそらく最高の開発者の人間工学を提供するソリューションを使用するのに十分良いです.
を使ってSQL Bulk Copy 私たちの移行では、私たちはそれ以外の場合は、移動を実行して完了を待つ無駄になる多くの時間を獲得しました.
あなたが高速な方法でレコードの数千を挿入する別の方法を知っていれば、私にDMを送信する自由を感じる.
私は、あなたから聞いて、学びたいです.

結果
- 1 Customer -
Simple: 11ms
Simple (shared connection): 11ms
Manual: 10ms
Entity Framework: 10ms
Table Valued Parameter: 12ms
BulkCopy: 3ms

- 10 Customer -
Simple: 121ms
Simple (shared connection): 119ms
Manual: 13ms
Entity Framework: 13ms
Table Valued Parameter: 13ms
BulkCopy: 3ms

- 100 Customer -
Simple: 1122ms
Simple (shared connection): 958ms
Manual: 39ms
Entity Framework: 38ms
Table Valued Parameter: 15ms
BulkCopy: 5ms

- 1_000 Customer -
Simple: 10457ms
Simple (shared connection): 7923ms
Manual: 557ms
Entity Framework: 155ms
Table Valued Parameter: 49ms
BulkCopy: 17ms

- 10_000 Customer -
Simple: 161930ms
Simple (shared connection): 130205ms
Manual: 17006ms
Entity Framework: 1015ms
Table Valued Parameter: 108ms
BulkCopy: 187ms

- 100_000 Customer -
Simple: SKIPPED
Simple (shared connection): SKIPPED
Manual: 23637ms
Entity Framework: 12290ms
Table Valued Parameter: 2090ms
BulkCopy: 1921ms

- 1_000_000 Customer -
Simple: SKIPPED
Simple (shared connection): SKIPPED
Manual: SKIPPED
Entity Framework: 119269ms
Table Valued Parameter: 12259ms
BulkCopy: 9510ms
Twitterで私に従ってくださいNewsletter | もともと公開timdeschryver.dev .
MySQL幻読およびGAP、Next-Key Locking
ft_calloc