挿入の比較
テーブルにデータを挿入するには、さまざまな方法があります。ANSI準拠の方法もあれば、RDBMS固有の方法もあります。しかしどの方法も、1行の挿入か、または複数行の挿入です。言うまでもなく、複数行の挿入の方が1行の挿入を繰り返すよりずっと高速ですが、では、どの程度高速なのでしょうか。この疑問を解決すべく、リスト1のテストを実行します。
/*----------------------------------------------------------------- Batch 1 Create and populate table testInserts ------------------------------------------------------------------ */ IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('testInserts') AND type in ('U')) DROP TABLE testInserts; GO SET NOCOUNT ON; CREATE TABLE testInserts( insID int IDENTITY(1,1) NOT NULL, OrderID int NOT NULL); INSERT INTO testInserts(OrderID) SELECT OrderID FROM Northwind..Orders; DECLARE @i as int, @LoopMax int, @MaxID int; SELECT @i = 1, @LoopMax = 2, @MaxID = 0; WHILE (@i <= @LoopMax) BEGIN INSERT INTO testInserts(OrderID) SELECT OrderID + @MaxID FROM testInserts; SELECT @MaxID = MAX(OrderID) FROM testInserts; SELECT @i = @i + 1; END; GO /*----------------------------------------------------------------- Batch 2 Test many-rows insert ------------------------------------------------------------------ */ IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('t1') AND type IN ('U')) DROP TABLE t1; GO CREATE TABLE t1( c1 int NOT NULL, c2 int NOT NULL); INSERT INTO t1 SELECT insID, OrderID FROM testInserts; GO /*----------------------------------------------------------------- Batch 3 Test one-row inserts ------------------------------------------------------------------ */ IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('t2') AND type IN ('U')) DROP TABLE t2; GO CREATE TABLE t2( c1 int NOT NULL, c2 int NOT NULL); -- Generate INSERT statements SELECT 'INSERT INTO t2 VALUES(' + CAST(insID AS VARCHAR(15)) + ',' + CAST(OrderID AS VARCHAR(15)) + ');' + CHAR(10) + 'GO' FROM testInserts; -- Result: INSERT INTO t2 VALUES(1,10249); GO INSERT INTO t2 VALUES(2,10251); GO . . . . . . . . . . . . . . . . INSERT INTO t2 VALUES(3320,44292); GO (3320 row(s) affected)
リスト1のすべてのバッチを個別に実行します。バッチ1は、テーブル「testInserts」を作成して、データを読み込みます。最初のINSERT（ループの前）では、テーブル「Northwind..Orders」からOrderIDを選択して、830行を読み込みます（SQL Server 2005（SS2005）を使用していて、Northwindデータベースをインストールしていない場合は、Microsoftダウンロードセンターからこれをダウンロードできます）。次に、各ループの反復によって、テーブル「testInserts」の行数が2倍になります。2回の反復によって、行数は最終的に3,320になります。
1行挿入をテストするには、バッチ3の結果をクエリアナライザまたはManagement Studioの新しいウィンドウにコピーして、実行します。さまざまなハードウェア構成のいくつかのマシンで試したところ、複数行挿入（バッチ2）の実行時間は約46ミリ秒でしたが、1行挿入（バッチ3）の実行時間は約36ミリ秒でした（SS2000関連での実行結果）。つまり、複数行挿入の方が、1行挿入を繰り返すよりも数倍高速です。
1行挿入の繰り返しは、さまざまな要因で低速になります。例えば、反復的な1行挿入の場合は、膨大な数のロックや実行プランが必要になりますし、SQL Serverが発行する実行文もかなりの数に上ります。また、個々の挿入操作（バッチ）で、オブジェクトのアクセス許可の取得、BEGINトランザクションとCOMMITトランザクション、トランザクションログへのデータの書き込みが必要です（単純なリカバリモデルの場合でも同様です）。
プロファイラを使って挿入をトレースした結果の一部を、以下に示します。
| 1行挿入 | 複数行挿入 | |
| BEGIN...COMMITトランザクションのペア | 7,265 | 1 |
| トランザクションログへの書き込み | 11,045 | 6,360 |
| ロック | 26,986 | 11,670 |
SQL Serverは、新しい行の領域を見つけるメカニズムがやや複雑であることにも注意してください。リスト1に示すように、ヒープテーブルの場合、SQL ServerはIAM（Index Allocation Map）ページとPFS（Page Free Space）ページを使って、テーブルに既に割り当てられているページの中から、空き領域を持つデータページを見つけます。すべてのページが埋まっている場合は、GAM（Global Allocation Map）とSGAM（Shared Global Allocation Map）を使って、混合エクステント内で空きページを見つけるか、新しい均一エクステントをテーブルに割り当てることを試みます。リスト1の例は、ヒープテーブルであり削除は行わないので、テーブルに割り当てられている最終ページの末尾にデータが挿入されます。そのため、マルチユーザー環境や、複数のアプリケーションサーバーが1つのデータベースを使用している場合は、テーブルの末尾に「ホットスポット」が生じる可能性があります。
反復的な1行挿入の場合、SQL Serverは、挿入の数だけ割り当てメカニズムを起動します。複数行挿入の場合は、すべての挿入行を扱えるだけの領域が即時に割り当てられます。インデックス付きのテーブルの場合は、クラスタ化インデックスに合わせてデータページを分割したり、非クラスタ化インデックスに合わせてインデックス更新を行うことができます。
