一括インポートのバッチの管理

  • [アーティクル]

ここでは、一括インポート操作に対するバッチ サイズの決定方法の管理について説明します。既定では、データ ファイル内のすべての行は、単一のトランザクションでサイズの不明な単一のバッチとしてインポートされます。この場合、インポート操作が完了前に失敗すると、トランザクション全体がロールバックされ、インポート先のテーブルにはデータは追加されません。失敗した操作は、その後、データ ファイルの最初から再開する必要があります。

大きなデータ ファイルを単一のバッチとしてインポートすると、問題が発生する場合があります。したがって、bcp と BULK INSERT では、それぞれがデータ ファイルより小さい一連のバッチとして、データをインポートすることができます。各バッチは個別のトランザクションでインポートおよびログ記録されます。また、あるトランザクションがコミットされた後で、そのトランザクションによってインポートされた行がコミットされます。操作が失敗した場合は、現在のバッチからインポートされた行だけがロールバックされるので、データ ファイルの最初からではなく、失敗したバッチの最初からデータのインポートを再開できます。

ms188267.note(ja-jp,SQL.90).gifメモ :
バッチのしくみに関する詳細については、「バッチ」を参照してください。

また、バッチのサイズを制限しない選択をする場合でも、コマンドでデータ ファイルのサイズを推定することによって、パフォーマンスを向上できます。推定サイズは、操作のクエリ プランを作成するときに、クエリ プロセッサで使用されます。

ms188267.note(ja-jp,SQL.90).gifメモ :
バッチ サイズまたはデータ ファイルのサイズを指定する際、正確さはそれほど重要ではありません。

次の表に、これらの方法をサポートする修飾子の概要を示します。

コマンド

バッチ サイズ

バッチあたりに送信される行数

バッチあたりに送信されるサイズ (KB 単位)

bcp1

-bbatch_size

-h "ROWS_PER_BATCH = bb"

-h "KILOBYTES_PER_BATCH = cc"

BULK INSERT2

BATCHSIZE = batch_size

ROWS_PER_BATCH = rows_per_batch

KILOBYTES_PER_BATCH = kilobytes_per_batch

ms188267.note(ja-jp,SQL.90).gifメモ :

このオプションを BATCHSIZE と一緒に使用すると、エラーが発生します。

INSERT ...SELECT * FROM OPENROWSET (BULK...)

—3

ROWS_PER_BATCH = rows_per_batch

—3

1bcp コマンドでは、-bbatch_size スイッチを ROWS_PER_BATCH ヒントまたは KILOBYTES_PER_BATCH ヒントと一緒に使用しないでください。これらを組み合わせて使用すると、エラーが発生します。

2 BULK INSERT コマンドでは、BATCHSIZE を ROWS_PER_BATCH または KILOBYTES_PER_BATCH と一緒に使用した場合、BATCHSIZE が優先されます。

3 OPENROWSET では、BATCHSIZE オプションも KILOBYTES_PER_BATCH オプションも使用できません。

次のセクションでは、これらの修飾子の使用方法について説明します。

推定バッチ サイズの指定

膨大な数の行をインポートする場合、データをバッチに分割すると、有益な場合あります。1 つのバッチが完了するたびに、そのトランザクションがログに記録されます。なんらかの理由により一括コピー操作が完了前に終了した場合には、現在のトランザクション (バッチ) だけがロールバックされます。

ms188267.note(ja-jp,SQL.90).gifメモ :
一括ログ記録により、トランザクション ログには、インポートされたデータのコピーが記録されます。そのため、ログのサイズがすぐに大きくなりますが、1 つのバッチが終了するたびに、ログをバックアップして、ログ領域を再利用することができます。

バッチのだいたいのサイズを指定して、データ ファイルを一連のバッチでインポートするには、次の修飾子を使用します。

  • bcp の場合 : -b
  • BULK INSERT の場合 : BATCHSIZE

各バッチのデータ行は、個別のトランザクションとして挿入されます。なんらかの理由により一括インポート操作が完了前に終了した場合には、現在のトランザクションだけがロールバックされます。たとえば、データ ファイルが 1,000 行から構成され、バッチ サイズを 100 行の場合、この操作は Microsoft SQL Server により 10 個の個別のトランザクションとしてログに記録され、各トランザクションではインポート先のテーブルに 100 行が挿入されます。一括インポート操作が 750 行目をインポート中に終了した場合、SQL Server では現在のトランザクションがロールバックされるので、それ以前の 49 行だけが削除されます。最初の 700 行は、インポート先のテーブルに残ります。

SQL Server では、バッチ サイズの値に合わせて自動的に読み込み処理が最適化されるため、パフォーマンスが向上することがあります。一般には、バッチ サイズは、実用的な範囲で、できるだけ大きくする必要があります。通常、バッチ サイズが大きいほど、一括インポート操作のパフォーマンスが向上します。ただし、いくつかの例外があります。インポート先のテーブルに 1 つ以上のインデックスが存在する場合は、並べ替え操作が実行されるため、バッチ サイズを大きくするとメモリに負荷がかかる場合があります。また、TABLOCK オプションを使用せずに並列読み込みを実行している間は、バッチ サイズが大きいほど、発生するブロックが多くなることがあります。

ms188267.note(ja-jp,SQL.90).gifメモ :
バッチ サイズは、SQL Server のインスタンスからデータ ファイルにデータを一括エクスポートする場合には使用できません。

データファイルの推定サイズの指定

一括インポート操作でバッチ サイズを指定しない場合、データ ファイルの推定サイズを指定し、クエリ プロセッサのクエリ プランでリソースを有効に割り当てることができます。データ ファイルの推定サイズを指定するには、次の方法を使用して、行数か、データのサイズ (KB 単位) のいずれかを推定します。

  • バッチあたりの行数の推定
    行数を推定するには、ROWS_PER_BATCH ヒントまたは ROWS_PER_BATCH オプションを使用します。0 より大きな値を指定すると、クエリ プロセッサでは ROWS_PER_BATCH の値がクエリ プランでリソースを割り当てるためのヒントとして使用されます。この値は、実際の行数と同じ次数になる必要があります。
    データ ファイルのすべての行は、1 バッチで SQL Server のインスタンスにコピーされますが、bcp からは、1,000 行コピーされるごとに、"SQL Server に 1,000 行が送信されました。" というメッセージが表示されます。このメッセージは、情報提供を目的としており、バッチ サイズに関係なく表示されます。
  • バッチあたりのサイズ (KB 単位) の推定
    データ ファイルのサイズ (KB 単位) を推定するには、KILOBYTES_PER_BATCH ヒントまたは KILOBYTES_PER_BATCH オプションを使用します。SQL Server では、指定した値に従って一括インポート操作が最適化されます。
ms188267.note(ja-jp,SQL.90).gifメモ :
サイズの大きなデータ ファイルを、バッチ サイズまたは一括ログ記録の最適化オプションを指定せずに一括インポートした場合、一括インポート操作が完了する前に、トランザクション ログがいっぱいになることがあります。このような状況は、トランザクション ログのサイズを大きくするか、自動的に拡張するように設定して回避できます。

参照

概念

一括インポートのパフォーマンスの最適化

その他の技術情報

BACKUP (Transact-SQL)
bcp ユーティリティ
BULK INSERT (Transact-SQL)
ImportRowsPerBatch Property
OPENROWSET (Transact-SQL)
sp_dboption (Transact-SQL)

ヘルプおよび情報

SQL Server 2005 の参考資料の入手