SQL Server에서 `scope_identity()`를 사용하여 대량 삽입을 효율적으로 수행하는 방법은 무엇입니까?

저장 프로시저를 사용하여 일괄 삽입을 효율적으로 수행하고 scope_identity()

소개

이 글에서는 scope_identity()과 관련된 일괄 삽입의 성능을 향상시키기 위해 저장 프로시저 기반의 방법을 제안합니다. 테이블 반환 매개 변수, MERGE 문 및 OUTPUT 절을 활용하여 관련 테이블에 레코드를 효율적으로 삽입하고 매핑 테이블을 사용하여 생성된 ID를 캡처합니다.

예시표

먼저 일대다 관계를 갖는 예제 테이블 tblBase과 tblRelated을 만듭니다.

<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 중국어 웹사이트의 기타 관련 기사를 참조하세요!