Wie führt man Masseneinfügungen mit „scope_identity()' in SQL Server effizient durch?

Verwenden Sie gespeicherte Prozeduren, um Batch-Einfügungen effizient durchzuführen und scope_identity()

Einführung

In diesem Artikel wird eine Methode vorgeschlagen, die auf gespeicherten Prozeduren basiert, um die Leistung von Batch-Einfügungen mit scope_identity() zu verbessern. Es nutzt Tabellenwertparameter, MERGE-Anweisungen und OUTPUT-Klauseln, um Datensätze effizient in verknüpfte Tabellen einzufügen und erfasst die generierten IDs mithilfe einer Zuordnungstabelle.

Beispieltabelle

Wir erstellen zunächst Beispieltabellen tblBase und tblRelated, zwischen denen eine Eins-zu-viele-Beziehung besteht.

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

Benutzerdefinierter Tabellentyp

Als nächstes definieren wir benutzerdefinierte Tabellentypen (UDTs), um die Eingabedaten für die Tabellen tblBase und tblRelated darzustellen.

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

Gespeicherte Prozedur

Die folgende stp_InsertMultipleRecordsToMultipleTables gespeicherte Prozedur akzeptiert udt_base und udt_related UDTs als Eingabe und führt Masseneinfügungsvorgänge durch.

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

Beschreibung

• Diese gespeicherte Prozedur akzeptiert UDT als Eingabe.
• Es verwendet die MERGE-Anweisung, um Datensätze in tblBase einzufügen. Diese Methode stellt sicher, dass die Tabelle aktualisiert oder eingefügt wird (aktualisiert, wenn ein passender Datensatz vorhanden ist).
Die
• OUTPUT-Klausel erfasst das generierte base_id und das temporäre base_temp_id, das zum Verknüpfen der zugehörigen Tabellen verwendet wird.
• Diese Werte werden in der @idMap-Tabelle gespeichert.
• Der Prozess verwendet dann @idMap, um die zugehörigen Datensätze in tblRelated einzufügen, um sicherzustellen, dass Fremdschlüsselbeziehungen beibehalten werden.

Testen

Getestet an 10 übergeordneten Datensätzen und 1000 untergeordneten Datensätzen, wurde der Prozess in weniger als 1 Sekunde ausgeführt.

Fazit

Dieser auf gespeicherten Prozeduren basierende Ansatz bietet erhebliche Leistungsverbesserungen für Batch-Einfügungen mit scope_identity(). Dadurch entfällt die Notwendigkeit, Abfragen in einer Schleife durchzuführen, wodurch die Anzahl der Datenbankaufrufe und die Sperrzeit reduziert werden. Darüber hinaus nutzt es Tabellenwertparameter und Tabellenwertfunktionen (UDFs), um Daten effizient zu manipulieren.

Das obige ist der detaillierte Inhalt vonWie führt man Masseneinfügungen mit „scope_identity()' in SQL Server effizient durch?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!