SQLの挿入処理の高速化 - japan.internet.com デベロッパー

はじめに

　データベースのパフォーマンスは、大部分のアプリケーションの最も重要な特性の1つであり、開発者やDBAにとっては、通常はデータの取得速度に依存します。このため、パフォーマンスの最適化やチューニングに関する多くの書籍は、クエリを高速化する方法について説明しています。RDBMSメーカーでさえも、高速なデータ取得の必要性を理解しており、これを促進するためのさまざまなツール（インデックス、構成オプションなど）が用意されています。しかし、データベースのパフォーマンスは、データ取得の速度で常に表されるわけではありません。場合によっては、データ挿入の速度に依存します。

　例えば、測定結果や調査結果をデータベースに格納する制御測定システムまたは調査サイトを設計する必要があるものとします。比較的単純な作業のように思われますが、その仕様を詳しく見ていくと、必ずしも思っているほど簡単ではありません。

受け取った非常に大量のトランザクションを処理してデータベースに挿入する必要があります。
24時間×7日間に渡って使用可能でなければなりません。
テーブルに格納する必要があるフィールド（パラメータ）数は、それぞれ大きく異なります。例えば、質問数は調査ごとに異なります（つまり、Webページ上のコントロールや要素の数が異なります）。また、制御測定システム内のプロセスごとに、検出器や測定装置の数も異なります。
アプリケーションは、データの挿入だけでなく、少ないとはいえデータの取得でも使用されます（詳細な分析ではOLAPシステムを作成できますが、それは別の問題です）。

　このシナリオに対して考えられるすべてのソリューションを総合的に分析することは、本稿の範囲を超えています。しかし、別の角度から要件に汎用的に取り組むことはできます。例えば、次に示すオプションのすべてまたは一部を使用できます。

高速なインターネットおよび高速ネットワーク
高速なCPUと多くのRAMを搭載した強力なサーバー
高速なディスクとパフォーマンスに優れたRAID
Webサーバーの負荷分散
データベースサーバーのフェールオーバークラスタリング
テーブルのパーティション分割
大量のストレージ容量（SAN）など

　パーティション分割を除く上記のすべてのソリューションは、非常にコストがかかります。たとえコスト的な問題がなかったとしても、依然としていくつかの難問を解決する必要があります。例えば、どんなデータベースでも定期的な保守が必要であり、インデックス、バックアップ、旧データの削除、断片化などに関する保守作業を行わなければなりません。データをアプリケーション（Web）サーバーからデータベースに直接供給する場合は、データベースの保守を行っている間に、挿入用のデータが失われたり、アプリケーションサーバー（またはデータベースサーバー）がクラッシュすることがあります。このため、データベースサーバー上でリソースを大量に使用している間（言い換えれば低速な挿入を行っている間）に、データを一時的に保持する一種のバッファを用意する必要があります。もちろん、複数のデータベースサーバーを追加しておけば、保守時間を十分に確保することができます。しかし、この場合は、異なるサーバー上のデータを1つのデータベースに統合するという別の問題が生じます。

　Berkeley DBは、バッファに適しています。反復的な静的クエリが非常に効率的に行われ、データがキー/値ペアで格納されます（調査サイトや制御測定システムの例では、データをコントロール（要素）の名前/値ペア、または検出器の位置/値ペアとして送信することに注意してください）。しかし、無限に増大するバッファなどはなく、標準のデータベースに十分に早くデータを送信できなければ、サーバーは結局クラッシュしてしまいます。

　このように、挿入の速度は、サンプルアプリケーションにとって、重大な要因の一つになります。

データの格納方法

　データベースの設計は、挿入のパフォーマンスに大きく影響します。そのため、データベース（ストレージ）構造を選択する場合は、十分な注意が必要です。例えば、データをXMLとして保存することも可能です。この方法は非常に魅力的ですが、前述の調査サイトまたは制御測定システムなどの例では、挿入の速度が低下し、多くのストレージ容量が占有されます。また、非常に伝統的なデータベース設計でデータベースを構築することもできます。この場合は、各テーブルが現実の世界のオブジェクトを再現し、テーブル内の各列がオブジェクトのプロパティに対応します。しかし、調査サイトや制御測定システムなどの例では、プロパティ（列）の数は動的です。10～1000の範囲で変動するため、伝統的な設計には適しません。

　そこで、おそらく次のソリューションを選ぶことになるでしょう。それは、データを名前/値のペアで格納する方法です。これは、HTMLコントロール（要素）の名前/値ペア、およびBerkeley DBのフィールド/値ペアに完全に対応します。大部分の調査（制御装置）データ値は整数として解釈できるため、データを型で分けると便利でしょう。例えば、「tbl_integerData」と「tbl_textData」という2つのテーブルを作成します。両方のテーブルの構造は、value列のデータ型を除けば、まったく同じです。value列のデータ型は、前者のテーブルでは整数型、後者のテーブルではテキスト（varchar）型です。

挿入の比較

　テーブルにデータを挿入するには、さまざまな方法があります。ANSI準拠の方法もあれば、RDBMS固有の方法もあります。しかしどの方法も、1行の挿入か、または複数行の挿入です。言うまでもなく、複数行の挿入の方が1行の挿入を繰り返すよりずっと高速ですが、では、どの程度高速なのでしょうか。この疑問を解決すべく、リスト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ダウンロードセンターからこれをダウンロードできます）。次に、各ループの反復によって、テーブル「testInsertfs」の行数が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は、挿入の数だけ割り当てメカニズムを起動します。複数行挿入の場合は、すべての挿入行を扱えるだけの領域が即時に割り当てられます。インデックス付きのテーブルの場合は、クラスタ化インデックスに合わせてデータページを分割したり、非クラスタ化インデックスに合わせてインデックス更新を行うことができます。

挿入を高速化する方法

　挿入を高速化するには、1行挿入の繰り返しを、複数行挿入に置き換えればよいのは明らかです。ここでは、リスト2の例を使ってその方法を示します。

リスト2　1行挿入の繰り返しを複数行挿入に置き換える方法

SET NOCOUNT ON
GO
-- Create test table
IF EXISTS (SELECT * FROM sysobjects
             WHERE id = OBJECT_ID(’tmpInserts’) AND type IN (’U’))
DROP TABLE tmpInserts;
GO
CREATE TABLE tmpInserts(c1 int NOT NULL);
GO
-- Create trigger on tmpInserts table
IF EXISTS (SELECT name FROM   sysobjects
   WHERE name = N’ti_tmpInserts’ AND type = ’TR’)
DROP TRIGGER ti_tmpInserts;
GO
CREATE TRIGGER ti_tmpInserts
ON tmpInserts
FOR INSERT
AS
BEGIN
   SELECT ’Hello’
END
GO
-- One-by-one inserts
INSERT INTO tmpInserts VALUES(1);
INSERT INTO tmpInserts VALUES(2);
INSERT INTO tmpInserts VALUES(3);
SELECT * FROM tmpInserts;
GO
DECLARE @str varchar(1000);
SELECT @str =
  ’INSERT INTO tmpInserts SELECT a=55 UNION ALL SELECT 66 UNION ALL SELECT 77’;
EXECUTE (@str);
SELECT * FROM tmpInserts;

-- Result:
-----
Hello

-----
Hello

-----
Hello

c1
-----------
1
2
3

-----
Hello

c1
-----------
1
2
3
55
66
77

　挿入をトレースするために、テーブル「tmpInsertsTo」に対してINSERTトリガを作成しました。トリガが起動するたびに、「Hello」という単語が出力されます。また、1行挿入を複数行挿入に変換するために、INSERT... SELECT文を実行しました。このSELECT部分は、UNION（ALL）で結合された多くの単純なSELECT文で構成されています。ここでは、文全体を1つの文字列変数に格納し、動的に実行するという方法をとっています。お分かりのように、行単位の挿入の場合は、指定されている挿入の数だけトリガが起動します（この例の場合は3回）。複数行挿入の場合、トリガが起動されるのは1回だけです。

　さて、この挿入方法を、制御測定システムやWebサイト（大量のトランザクションが発生する調査サイトなど）のアプリケーションに、どのように適用できるのでしょうか。ユーザーがフォームを送信すると、アプリケーション（Web）サーバーは、これをフォーム上のコントロール（要素）に対応する名前と値のペアの並びとして受け取ります。必要な操作は、その並びを少しだけ変更し、それを挿入に対処するデータベースサーバーに転送するだけです。

　リスト3およびリスト4の例は、データベースサーバーに転送される文字列の状態と、それを処理してテーブルに挿入する方法を示しています。

リスト3　テストスクリプト

SET NOCOUNT ON;
GO
SET QUOTED_IDENTIFIER OFF
GO
DECLARE @str varchar(4000), @i int, @numElements int, @pos int;
SELECT @i = 2, @numElements = 250, @pos = 0;
SELECT @str = ’a=’ + CAST(insID AS varchar(10)) +
’,b=’ + CAST(OrderID AS varchar(10)) + ’x’
  FROM testInserts
  WHERE insID = 1;
WHILE (1=1)
BEGIN
  SELECT @str  = @str + CAST(insID AS varchar(10)) + ’,’ +
  CAST(OrderID AS varchar(10)) + ’x’
    FROM testInserts
    WHERE insID = @i;
  IF @@ROWCOUNT = 0
  BEGIN
    SELECT @str = SUBSTRING (@str, 1, LEN(@str)-1);
    SELECT ’spu_InsertStrings ’ + ’"’ + @str + ’"’ + CHAR(10) + ’GO’;
    BREAK;
  END
  IF @i % @numElements = 0
  BEGIN
    SELECT @str = SUBSTRING (@str, 1, LEN(@str)-1);
    SELECT ’spu_InsertStrings ’ + ’"’ + @str + ’"’ + CHAR(10) + ’GO’;
    SELECT @i = @i + 1;
    SELECT @str = ’’;
    SELECT @str = ’a=’ + CAST(insID AS varchar(10)) + ’,b=’ +
    CAST(OrderID AS varchar(10)) + ’x’
      FROM testInserts
      WHERE insID = @i;
  END
  SELECT @i = @i + 1;    
END

-- Result:
spu_InsertStrings "a=1,b=10249x2,10251x . . . . . x249,11071x250,10250"
GO
. . . . . . . . . . . . . . . . . . . . . . . . . . . . .
spu_InsertStrings "a=3251,b=44050x3252,44053x . . . x3319,44289x3320,44292"
GO

リスト4　複数行挿入を実行するストアドプロシージャ

-- Create table t3
IF EXISTS (SELECT * FROM sysobjects
             WHERE id = OBJECT_ID(’t3’) AND type IN (’U’))
DROP TABLE t3;
GO
CREATE TABLE t3(
   c1 int NOT NULL,
   c2 int NOT NULL);
GO
-- Create stored procedure spu_InsertStrings
IF EXISTS (SELECT name FROM sysobjects
   WHERE name = N’spu_InsertStrings’ AND type = ’P’)
DROP PROCEDURE spu_InsertStrings
GO
CREATE PROCEDURE spu_InsertStrings
  @str varchar(8000)
AS
SELECT @str = ’INSERT INTO t3 SELECT ’ +
REPLACE(@str,’x’,’ UNION ALL SELECT ’)
EXEC(@str);
GO

　ここでは、リスト1（バッチ1）で作成して読み込んだテーブル「testInserts」を使用しました。変数@numElementsの値は、名前と値のペア数を定義し、これが生成される文字列の長さになります。文字xは、プレースホルダとして機能します（その目的については後述します）。

　リスト4では、データベースサーバーに送信されたデータを処理して挿入するストアドプロシージャを作成しています。

　ここが重要なポイントです。このストアドプロシージャは、文字列パラメータ内の各プレースホルダ（x）をUNION ALL SELECTのフレーズに置き換え、修正後の文字列を実行します。

　これで、ソリューションをテストすることができます。テストを行うには次のようにします。

テーブル「testInserts」がまだない場合は、これを作成して読み込みます（リスト1のバッチ1を参照）。リスト3内のスクリプトを実行します。
テストテーブル「t3」を作成し、ストアドプロシージャspu_insertStringsを作成します（リスト4）。
ステップ2の結果を新しいクエリアナライザ（Management Studio）ウィンドウにコピーアンドペーストします。次のようなスクリプトが得られます。

SET NOCOUNT ON
GO
SET QUOTED_IDENTIFIER OFF

spu_InsertStrings "a=1,b=10249x2,10251x . . . . . x249,11071x250,10250"
GO
. . . . . . . . . . . . . . . . . . . . . . . . . . . . .
spu_InsertStrings "a=3251,b=44050x3252,44053x . . . x3319,44289x3320,44292"
GO

　スクリプトの先頭で、NOCOUNTとQUOTED_IDENTIFIERの2つのSET文を忘れずに指定します。次に、このスクリプトを実行し、実行時間をメモしておきます。

　私のテスト結果では、文字列挿入の方法を使用した場合は3,320行をテーブル「t3」に2秒で挿入できました。これは、行挿入を繰り返すより18倍も高速です（この数値はSS2000で実行した結果です。SS2005の場合は、60～70%の範囲で向上が見られました）。

制限事項

　複数行挿入には、1つの重大な副作用があります。それは、長時間にわたってテーブルがロックされる可能性です。これは、マルチユーザーの環境では許されません。しかし、今回のサンプルは1回に数百行だけが挿入されるというシナリオだったので、この問題は無視できます。この程度の行数であれば、ロックの問題は生じません。

　もう1つの問題は、varchar変数の長さが8,000バイトに制限されていることです。しかしこの問題は、ある工夫をすれば解決できます。具体的には、受信した文字列を独立したテーブルに格納し、同じ調査およびユーザー送信に属するすべての文字列セットを別のプロセスでチェックするようにします。その後、この文字列セットを作業テーブルに非同期的に挿入します。

　2GBまで格納できるvarchar(max)データ型を持つSS2005ならば、もっと柔軟な処理が可能です。文字列の長さを最大2GBまで調整して、文字列挿入のパフォーマンスの最適化を試みることができます。

　最後にひとこと申し添えますが、データの検証はストアドプロシージャの本体で行ってください。ストアドプロシージャの動作は重くなりますが、1行挿入を繰り返すよりも依然として高速です。

著者紹介

Alex Kozak（Alex Kozak）

SAP Canadaの上級DBA/アナリスト。データベースとプログラミングに15年以上従事。MSDNライブラリにも多数投稿。