SQLの挿入処理の高速化

複数行挿入によるデータベースのパフォーマンス向上

挿入を高速化する方法

　挿入を高速化するには、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のフレーズに置き換え、修正後の文字列を実行します。

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

1. テーブル「testInserts」がまだない場合は、これを作成して読み込みます（リスト1のバッチ1を参照）。リスト3内のスクリプトを実行します。
2. テストテーブル「t3」を作成し、ストアドプロシージャspu_insertStringsを作成します（リスト4）。
3. ステップ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行挿入を繰り返すよりも依然として高速です。