如何在 SQL Server 中使用「scope_identity()」高效率執行批次插入？

利用預存程序高效率執行批次插入及scope_identity()

引言

本文提出一種基於預存程序的方法，以提高涉及scope_identity()的批次插入效能。它利用表值參數、MERGE語句和OUTPUT子句，有效率地將記錄插入相關表中，並使用映射表擷取產生的ID。

範例表

我們先建立範例表tblBase和tblRelated，它們之間存在一對多關係。

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
);

使用者自訂表格類型

接下來，我們定義使用者自訂表格類型 (UDT) 來表示tblBase和tblRelated表的輸入資料。

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
);

預存程序

以下stp_InsertMultipleRecordsToMultipleTables預存程序接受udt_base和udt_related UDT作為輸入，並執行批次插入操作。

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`的相关记录

說明

• 此預存程序接受UDT作為輸入。
• 它使用MERGE語句將記錄插入tblBase。此方法可確保表格已更新或插入（如果存在符合記錄則更新）。
• OUTPUT子句捕獲產生的base_id和用於連接相關表的臨時base_temp_id。
• 這些值儲存在@idMap表中。
• 然後，該過程使用@idMap將相關記錄插入tblRelated，確保維護外鍵關係。

檢定

在10筆父記錄和1000筆子記錄上進行測試，該過程在不到1秒的時間內執行完畢。

結論

這種基於預存程序的方法為使用scope_identity()的批次插入提供了顯著的效能改進。它消除了在循環中查詢的需要，減少了資料庫呼叫次數和鎖定時間。此外，它利用表值參數和表值函數 (UDF) 來有效地操作資料。

以上是如何在 SQL Server 中使用「scope_identity()」高效率執行批次插入？的詳細內容。更多資訊請關注PHP中文網其他相關文章！