BULK INSERT 時のソート操作

SQL Server で大量データを一括登録するときは BULK INSERT を用いますが、並び順を気にしておかないと、状況によっては内部的にソートが発生したり、登録後のテーブルが断片化したりします。
ただ私も詳細はあまりよく把握していないので、いろいろ検証してみます。

下準備

まずは一括登録用に、以下の 3 つのテーブルを作成します。ヒープというのは、クラスタ化インデックスを持たないテーブルのことです。

• クラスタ化インデックスを持つテーブル
• 非クラスタ化インデックスのキーを持つヒープ
• 一意でないインデックスを持つヒープ

-- クラスタ化インデックス
CREATE TABLE [dbo].[BulkCopy_ClusteredIndex]
(
    [ID] [int] NOT NULL,
    [Column1] [float] NOT NULL,
    [Column2] [float] NOT NULL,
    [Column3] [float] NOT NULL,
    [Column4] [float] NOT NULL,
    [Column5] [float] NOT NULL,
    [Column6] [float] NOT NULL,
    [Column7] [float] NOT NULL,
    [Column8] [float] NOT NULL,
    [Column9] [float] NOT NULL,
    [Column10] [float] NOT NULL,
    CONSTRAINT [PK_BulkCopy_ClusteredIndex] PRIMARY KEY CLUSTERED
    (
        [ID] ASC
    )
)
GO

-- ヒープ 1（キーあり）
CREATE TABLE [dbo].[BulkCopy_Heap1]
(
    [ID] [int] NOT NULL,
    [Column1] [float] NOT NULL,
    [Column2] [float] NOT NULL,
    [Column3] [float] NOT NULL,
    [Column4] [float] NOT NULL,
    [Column5] [float] NOT NULL,
    [Column6] [float] NOT NULL,
    [Column7] [float] NOT NULL,
    [Column8] [float] NOT NULL,
    [Column9] [float] NOT NULL,
    [Column10] [float] NOT NULL,
    CONSTRAINT [PK_BulkCopy_Heap1] PRIMARY KEY NONCLUSTERED
    (
        [ID] ASC
    )
)
GO

-- ヒープ 2（非一意インデックスあり）
CREATE TABLE [dbo].[BulkCopy_Heap2]
(
    [ID] [int] NOT NULL,
    [Column1] [float] NOT NULL,
    [Column2] [float] NOT NULL,
    [Column3] [float] NOT NULL,
    [Column4] [float] NOT NULL,
    [Column5] [float] NOT NULL,
    [Column6] [float] NOT NULL,
    [Column7] [float] NOT NULL,
    [Column8] [float] NOT NULL,
    [Column9] [float] NOT NULL,
    [Column10] [float] NOT NULL
)
CREATE NONCLUSTERED INDEX [IX_BulkCopy_Heap2] ON [dbo].[BulkCopy_Heap2]
(
    [ID] ASC
)
GO

お次は、BULK INSERT 用のデータを作ります。Excel を使う手もありますが、ここは一度 SQL で登録してからテキストファイルに書き出す方式にします。
T-SQL – 1 million records in 1 second というポストを真似て、先ほど作成したテーブルに 100 万件ほど登録します。一応やってることを説明すると、まずは再帰 CTE を用いて目的の件数の平方根以上の最小の整数件分の共通テーブル式　(Base) を作って、次に Base 2 つの直積を取ったテーブル式 (Expand) を作る、という感じです。

DECLARE @rows int = 1000000;

WITH Base AS
(
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM Base WHERE n < CEILING(SQRT(@rows))
),
Expand AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY b1.n) AS n
    FROM Base AS b1, Base AS b2
)

INSERT INTO [dbo].[BulkCopy_ClusteredIndex]
SELECT
    n,
    RAND(CHECKSUM(NEWID())),
    RAND(CHECKSUM(NEWID())),
    RAND(CHECKSUM(NEWID())),
    RAND(CHECKSUM(NEWID())),
    RAND(CHECKSUM(NEWID())),
    RAND(CHECKSUM(NEWID())),
    RAND(CHECKSUM(NEWID())),
    RAND(CHECKSUM(NEWID())),
    RAND(CHECKSUM(NEWID())),
    RAND(CHECKSUM(NEWID()))
FROM Expand
WHERE n <= @rows
OPTION (MAXRECURSION 0)

GO

登録できたら、インポートおよびエクスポートウィザード を用いて、CSV 形式で出力します。主キーである ID 列の昇順でソートしたものと、ランダムに並べ替えたものの 2 種類を用意します。

-- ID 列の昇順 (BulkCopy_Ordered.csv)
SELECT * FROM [dbo].[BulkCopy_ClusteredIndex] ORDER BY ID

-- ランダムに並べ替え (BulkCopy_Random.csv)
SELECT * FROM [dbo].[BulkCopy_ClusteredIndex] ORDER BY NEWID()

あとは一時的に登録したデータを TRUNCATE でクリアして準備完了です。

データソースの並び順と BULK INSERT の ORDER 引数

SQL Server でよく使われるクラスタ化インデックスでは、データがキーの順序で物理的かつ連続的に並べられた状態で格納される、...というだけの説明をかつては書籍やネットの記事でよく見かけた気がしますが、これにはかなり語弊があります。
実際は極力連続的に並べようとしますが、それができない場合は、前後のページがポインタで連結されます（この状態が断片化です）。本当にすべてが連続的だとしたら、大量データの途中で追加や削除をしようとしたら大変なことになってしまいます。

少し話が逸れましたが、クラスタ化インデックスでは、大部分のデータがキーの順序で連続的に並んでいます。
BULK INSERT においては、基本的に BATCHSIZE 引数を指定した場合はその値の行数ごと、指定しない場合はデータソース全体を、連続した領域に割り当てようとします。そのため、読み込んだデータを登録する前に、クラスタ化インデックスのキーの順番に従ってソートする操作が発生します。
ただ、データソースが元からクラスタ化インデックスのキーと同じ順番で並んでいる場合は、改めてソートする必要はないはずです。こういう場合のために BULK INSERT では ORDER 引数が用意されており、これでデータソースの並び順をオプティマイザに対して明示的に示すことで、不要なソートを抑えることができます。

クラスタ化インデックスに ORDER 引数なしで BULK INSERT する場合

まずはクラスタ化インデックスに ORDER 引数なしで BULK INSERT してみましょう。SQL と実際の実行プランはこんな感じになります。

BULK INSERT testdb.dbo.BulkCopy_ClusteredIndex
FROM 'C:\tmp\BulkCopy_Random.csv'
WITH
(
    CHECK_CONSTRAINTS,
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    FIRSTROW = 2
)
GO

INSERT の前にソートが行われていますね。ではここで、登録先のクラスタ化インデックスの断片化状況を見てみましょう。

SELECT
    index_level, avg_fragmentation_in_percent, fragment_count, avg_fragment_size_in_pages,
    page_count, avg_page_space_used_in_percent, record_count
FROM sys.dm_db_index_physical_stats(
        DB_ID(),
        OBJECT_ID(N'dbo.BulkCopy_ClusteredIndex'),
        OBJECT_ID(N'dbo.BulkCopy_ClusteredIndex.PK_BulkCopy_ClusteredIndex'),
        NULL,
        'DETAILED')
GO

index_level が 0 のものがクラスタ化インデックスのリーフレベル、つまりレコードのデータそのものを表します。avg_fragmentation_in_percent が断片化率で 0.39% です。ランダムに並んだデータを登録したにもかかわらず、事前のソートのおかげでほとんど断片化していないことがわかります。

今度はキーの順番でソート済みのデータを登録してみましょう。

BULK INSERT testdb.dbo.BulkCopy_ClusteredIndex
FROM 'C:\tmp\BulkCopy_Ordered.csv'
WITH
(
    CHECK_CONSTRAINTS,
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    FIRSTROW = 2
)
GO

BulkCopy_Ordered.csv はクラスタ化インデックスと同じ順番で並んでいますが、それとは関係なく INSERT の前にソートが行われています。

クラスタ化インデックスに ORDER 引数ありで BULK INSERT する場合

では今度は OREDR 引数ありでやってみましょう。登録するのはソート済のほうです。

BULK INSERT testdb.dbo.BulkCopy_ClusteredIndex
FROM 'C:\tmp\BulkCopy_Ordered.csv'
WITH
(
    CHECK_CONSTRAINTS,
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    FIRSTROW = 2,
    ORDER (ID)
)
GO

確かに事前ソートは行われていません。念のため断片化率もチェックしてみましょう。

断片化もしていません。やったね！期待通りです。

...ただここで疑問が。今のはもともとソート済のデータを使ったので ORDER 引数が有効に働きましたが、並び順がランダムな方のデータで ORDER を付けるとどうなるんでしょう。リファレンスには次のような記述があります。

データ ファイルが異なる順序で並んでいる場合、つまりクラスター化インデックス キーの順序以外の順で並んでいるか、テーブルにクラスター化インデックスが存在しない場合、ORDER 句は無視されます。

無視される、ということは強制的にソートされるという意味でしょうか。だとすると、本当にデータソースが ORDER で指定したとおりに並んでいるかどうかを確認するってこと？その場合の計算量は最大でも O(n) なのでソートよりは負荷が低いだろうけど、実際の登録前にデータをどこかにバッファしておくか二度読みが必要になるので、ちょっと考えにくい気がします。とりあえずやってみましょう。

BULK INSERT testdb.dbo.BulkCopy_ClusteredIndex
FROM 'C:\tmp\BulkCopy_Random.csv'
WITH
(
    CHECK_CONSTRAINTS,
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    FIRSTROW = 2,
    ORDER (ID)
)
GO

ソートされませんね。ちょっと嫌な予感がしつつも断片化率を見てみます。

99.25%(!) というか、fragment_count と page_count が同じ値なので、連続したページがひとつもないことになります。
この結果を見る限り、オプティマイザは実際のデータソースの並び順に関係なく、ORDER で指定されたとおりに並んでいると仮定してソートの有無を決めているようです。ORDER を使うときは、データソースの並び順を担保してあげることが必要ということですね。

では、ORDER で指定した並び順とキーの並び順が違っている場合は？
こちらも試してみましょう。ORDER でキーの逆順を指定してみます。

BULK INSERT testdb.dbo.BulkCopy_ClusteredIndex
FROM 'C:\tmp\BulkCopy_Ordered.csv'
WITH
(
    CHECK_CONSTRAINTS,
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    FIRSTROW = 2,
    ORDER (ID DESC)
)
GO

ソートが行われています。さらに、ソート操作のアイコンにマウスポインタを持っていくと、詳細がポップアップされます。

最後の「並べ替え」に注目してください。ORDER では ID の降順を指定しましたが、実際は昇順でソートが行われています。
ここでもう一度さっきの引用を繰り返しますが、

データ ファイルが異なる順序で並んでいる場合、つまりクラスター化インデックス キーの順序以外の順で並んでいるか、テーブルにクラスター化インデックスが存在しない場合、ORDER 句は無視されます。

データファイルの実際の並び順はチェックしないので、ORDER の指定とクラスタ化インデックスの順序が異なる場合、ORDER は無視される、の方がしっくり来ますね。

キーを持つヒープに BULK INSERT する場合

今度は、非クラスタ化インデックスのキーを持つヒープへの BULK INSERT を見てみます。
非クラスタ化インデックスも、クラスタ化インデックスほどではないにしてもリーフレベルでは断片化が気になります。

まずは ORDER なしから。

BULK INSERT testdb.dbo.BulkCopy_Heap1
FROM 'C:\tmp\BulkCopy_Ordered.csv'
WITH
(
    CHECK_CONSTRAINTS,
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    FIRSTROW = 2
)
GO

やはり非クラスタ化インデックスの登録前にソートが発生しています。では ORDER を付けるとどうでしょう。

BULK INSERT testdb.dbo.BulkCopy_Heap1
FROM 'C:\tmp\BulkCopy_Ordered.csv'
WITH
(
    CHECK_CONSTRAINTS,
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    FIRSTROW = 2,
    ORDER (ID)
)
GO

ソートがなくなっています。ヒープに BULK INSERT する場合でも、ORDER で指定したのと同じ並び順を持つ非クラスタ化インデックスのキーの登録前にはソートが発生しない、ということが言えそうです。

非一意インデックスを持つヒープに BULK INSERT する場合

先ほどの非クラスタ化インデックスはキーでしたが、では非一意のインデックスしか持たないヒープの場合はどうでしょう。今回は最初から ORDER 付きで実行してみます。

BULK INSERT testdb.dbo.BulkCopy_Heap2
FROM 'C:\tmp\BulkCopy_Ordered.csv'
WITH
(
    CHECK_CONSTRAINTS,
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    FIRSTROW = 2,
    ORDER (ID)
)
GO

ORDER の指定とインデックスの順番が一致しているにもかかわらず、インデックスの登録前にソートが発生しています。キーのないテーブルに BULK INSERT する場合は、ORDER は無視されるようです。

まとめ

BULK INSERT を行う際の ORDER の指定ポリシーは、次のようにするのが良さそうです。

• 登録先のテーブルにキーがある場合、クラスタ化インデックスであってもヒープであっても、可能であればデータソースをキーの順番どおりにあらかじめソートしておき、その通りに ORDER 引数を指定する。
• 登録先のテーブルにキーがない場合、ORDER 引数は無視され、インデックス登録前に強制的にソートされるので指定しない。

IEnumerable<T> を DataTable に変換する

このご時世に DataTable かよ、という感じもしますし、私自身実案件では Dapper をメインで使用しているわけですが、少しは使える場面もあるかもしれません。
例えばデバッガで使える DataSet ビジュアライザ。こういうやつですね。

これなら複数レコードのプロパティ／フィールドの値を一覧で見ることができます。単に CSV やタブ区切りテキストに吐き出すという手もありますが、デバッグ中にシームレスに見られるというのはやはり便利です。少々重いですが。

他はまぁ、WinForms や WebForms を使っている場合ですかね。特に前者の場合、DataGridView のソートやフィルタを使うには IBindingList や IBindingListView インターフェースが必要なわけですが、これらを実装しているのはベースクラスライブラリでは DataView だけという状況で、自前で実装するのも相当骨が折れるので。
というわけで、いわゆる業務ロジックや DB アクセスには POCO を使い、UI でグリッドを使う時だけ DataTable に変換するという使い方ができるかもしれません（？）

というわけで作ってみましょう。まずはプロパティやフィールドの情報を基に DataColumn を作成するメソッドを用意します。

private static DataColumn ToDataColumn(this PropertyInfo propInfo)
{
    return ToDataColumnCore(propInfo.PropertyType, propInfo.Name);
}

private static DataColumn ToDataColumn(this FieldInfo fieldInfo)
{
    return ToDataColumnCore(fieldInfo.FieldType, fieldInfo.Name);
}

private static DataColumn ToDataColumnCore(Type type, String name)
{
    var isNullable = (type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>));
    var columnType = isNullable ? Nullable.GetUnderlyingType(type) : type;
    var column = new DataColumn(name, columnType);
    column.AllowDBNull = (isNullable || !type.IsValueType);

    return column;
}

私はわりとカジュアルに拡張メソッド作っちゃう派です。どうせ private だし。
型が Nullable ないし参照型の場合は DBNull を許すようにしてます。

あとは IEnumerable の要素を DataRow に変換してあげれば出来上がり。パフォーマンスに配慮して、毎回リフレクションを利用するのではなく、ExpressionTree でコンパイル済み getter を作成しています。
引数に BindingFlags を指定することで、プロパティかフィールドのどちらか片方だけ出力することもできるようなオーバーロードも付けてます。

public static DataTable ToDataTable<T>(this IEnumerable<T> entities)
{
    return entities.ToDataTable(BindingFlags.GetField | BindingFlags.GetProperty);
}

public static DataTable ToDataTable<T>(this IEnumerable<T> entities, BindingFlags bindingAttr)
{
    if ((bindingAttr & ~BindingFlags.GetField & ~BindingFlags.GetProperty) != BindingFlags.Default)
    {
        throw new ArgumentException(nameof(bindingAttr));
    }

    var table = new DataTable(typeof(T).Name);
    var getters = new Dictionary<DataColumn, Func<T, object>>();
    var hasItem = entities.Any();

    Action<Func<DataColumn>, Func<ParameterExpression, MemberExpression>> buildConverter = (getColumn, getMember) =>
    {
        var column = getColumn();
        table.Columns.Add(column);

        if (hasItem)
        {
            var param = Expression.Parameter(typeof(T), "entity");
            var member = Expression.Convert(getMember(param), typeof(object));
            var lambda = Expression.Lambda<Func<T, object>>(member, param);
            getters.Add(column, lambda.Compile());
        }
    };

    if ((bindingAttr & BindingFlags.GetProperty) == BindingFlags.GetProperty)
    {
        foreach (var property in typeof(T).GetProperties())
        {
            buildConverter(() => property.ToDataColumn(), (param) => Expression.Property(param, property));
        }
    }

    if ((bindingAttr & BindingFlags.GetField) == BindingFlags.GetField)
    {
        foreach (var field in typeof(T).GetFields())
        {
            buildConverter(() => field.ToDataColumn(), (param) => Expression.Field(param, field));
        }
    }

    foreach (var entity in entities)
    {
        var row = table.NewRow();
        row.BeginEdit();
        foreach (var getter in getters)
        {
            row[getter.Key] = getter.Value(entity) ?? DBNull.Value;
        }
        row.EndEdit();
        table.Rows.Add(row);
    }

    table.AcceptChanges();
    return table;
}

本当はもう少しオーバーロード追加したり、テストメソッドも用意した上で GitHub に上げたかったんですが、ただの Git すらよくわかってないマンなので、今日のところはこれまで。

ExpressionTree の PropertyOrField

ExpressionTree でプロパティやフィールドの値を取得するとき、Expression.Property や Expression.Field を使えば良いんですが、Expression.PropertyOrField なんてのもあり、こっちだとその名の通りプロパティでもフィールドでもどっちでも OK みたいです。
じゃあプロパティかフィールドかは気にせず常に PropertyOrField 使えば楽チンじゃん、っと思いたいところですが、なんかのオーバーヘッドがあって Property や Field 使うよりちょっとだけ遅いんじゃ？という気もします。というわけで調べてみましょ。

こんなコードを書いて、それぞれの式がどんな風にコンパイルされるかを見てみました。

class Program
{
    static void Main(string[] args)
    {
        var foo = Expression.Constant(new Foo { Property1 = 1, Field1 = 2});

        // Property でプロパティにアクセス
        var prop1 = Expression.Property(foo, "Property1");

        // PropertyOrField でプロパティにアクセス
        var prop2 = Expression.PropertyOrField(foo, "Property1");

        // Field でフィールドにアクセス
        var fld1 = Expression.Field(foo, "Field1");

        // PropertyOrField でフィールドにアクセス
        var fld2 = Expression.PropertyOrField(foo, "Field1");

        Console.ReadKey();
    }
}

class Foo
{
    public int Property1 { get; set; }
    public int Field1;
}

式ツリーのデバッグによると、DebugView プロパティで ExressionTree の中身が見られるらしいですね。というわけで早速見てみましょう。

あの、見えないんですけど...。なんでなんでなんで？？？

どうやら DebugView を使用するためには、.Net Framework のバージョンを 4 以上にしないといけないらしいです。SIer に勤めてると .Net 3.5 縛りの案件がまだまだ多くて、そのノリを引きずってしまっていました。
というわけで、気を取り直して現在の最新である .Net 4.6.1 に設定して再チャレンジ。

今度は OK ですね。この調子で他の式も見てみましょう。結果は次の通り。

prop1(Property)

.Constant(PropertyOrField.Foo).Property1

prop2(PropertyOrField)

.Constant(PropertyOrField.Foo).Property1

fld1(Field)

.Constant(PropertyOrField.Foo).Field1

fld2(PropertyOrField)

.Constant(PropertyOrField.Foo).Field1

Property/Field を使い分けた場合も PropertyOrField の場合も作られる式に特に違いはないみたいですね。
もしかすると式自体を構築するときにごくわずかな性能差があるかもしれないですが、コンパイルした式が場合によっては何百万回とか呼ばれるのに対し、式の構築は最初の 1 回だけなので気にするレベルじゃないですね。
というわけで、積極的に PropertyOrField を使って OK ですね。

2016/2/21 追記

パフォーマンスとは別の問題で PropertyOrField が適さないケースがあったので追記します。
大文字の使用規則ガイドラインに反するので普通はやらないし、VB だとそもそもできなかったりしますが、型のメンバに例えば Foo と foo のように大文字と小文字だけが異なるメンバが混在している場合、PropertyOrField でアクセスしようとすると AmbiguousMatchException が発生してしまいます。大文字小文字を区別させるオーバーロードは残念ながら存在しません。
そのような場合は Property(Expression, PropertyInfo) などを使う必要があります。