SQL Server で「scope_identity()」を使用して一括挿入を効率的に実行するにはどうすればよいですか?

ストアド プロシージャを使用してバッチ挿入と scope_identity()

を効率的に実行します

はじめに

この記事では、ストアド プロシージャに基づいて、scope_identity() を含むバッチ挿入のパフォーマンスを向上させる方法を提案します。テーブル値パラメーター、MERGE ステートメント、および OUTPUT 句を利用して、関連テーブルに効率的にレコードを挿入し、マッピング テーブルを使用して生成された ID を取得します。

テーブルの例

まず、サンプルテーブル tblBase と tblRelated を作成します。これらの間には 1 対多の関係があります。

<code class="language-sql">CREATE TABLE tblBase (
    base_id int identity(1,1) primary key, 
    base_data int
); 

CREATE TABLE tblRelated
(
    related_base_id int foreign key references tblBase (base_id), 
    related_Id int identity(1,1) primary key, 
    related_data int
);</code>

ユーザー定義のテーブルタイプ

次に、tblBase テーブルと tblRelated テーブルの入力データを表すユーザー定義テーブル タイプ (UDT) を定義します。

<code class="language-sql">CREATE TYPE udt_base As Table
(
    base_temp_id int,  -- 注意：填充表值参数时，此列保存用于连接`tblBase`和`tblRelated`表的ID。
    base_id int,
    base_data int  
);

CREATE TYPE udt_related As Table
(
    related_base_id int,
    related_data int
);

CREATE TYPE udt_idMap as table
(
    temp_id int,
    id int
);</code>

ストアド プロシージャ

次の stp_InsertMultipleRecordsToMultipleTables ストアド プロシージャは、udt_base および udt_related UDT を入力として受け入れ、一括挿入操作を実行します。

<code class="language-sql">CREATE PROCEDURE stp_InsertMultipleRecordsToMultipleTables
(
    @base as dbo.udt_base readonly,
    @related as dbo.udt_related readonly
)
AS

DECLARE @idMap as dbo.udt_idMap

MERGE INTO tblBase USING @base AS temp ON 1 = 0 -- 始终不匹配
WHEN NOT MATCHED THEN
    INSERT (base_data)
    VALUES (temp.base_data)
    OUTPUT temp.base_temp_id, inserted.base_id -- 此处我们使用`base_temp_id`映射到正确的ID
    INTO @idMap (temp_id, id);

INSERT INTO tblRelated(related_base_id, related_data)
SELECT id, related_data
FROM @related r
INNER JOIN @idMap m ON(r.related_base_id = m.temp_id) -- 此处我们使用映射表插入具有正确`base_id`的相关记录</code>

説明

• このストアド プロシージャは、UDT を入力として受け入れます。
• MERGE ステートメントを使用してレコードを tblBase に挿入します。このメソッドにより、テーブルが更新または挿入されます (一致するレコードが存在する場合は更新されます)。
• OUTPUT 句は、生成された base_id と、関連テーブルを結合するために使用される一時的な base_temp_id をキャプチャします。
• これらの値は @idMap テーブルに保存されます。
• プロセスは、@idMap を使用して関連レコードを tblRelated に挿入し、外部キー関係が維持されるようにします。

テスト

10 個の親レコードと 1000 個の子レコードでテストしたところ、プロセスは 1 秒未満で実行されました。

結論

このストアド プロシージャ ベースのアプローチにより、scope_identity() を使用したバッチ挿入のパフォーマンスが大幅に向上します。これにより、ループ内でクエリを実行する必要がなくなり、データベース呼び出しの数とロック時間が削減されます。さらに、テーブル値パラメーターとテーブル値関数 (UDF) を活用して、データを効率的に操作します。

以上がSQL Server で「scope_identity()」を使用して一括挿入を効率的に実行するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。