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 引数は無視され、インデックス登録前に強制的にソートされるので指定しない。