Comment effectuer efficacement des insertions en masse avec `scope_identity()` dans SQL Server ?

Utilisez des procédures stockées pour effectuer efficacement des insertions par lots et scope_identity()

Présentation

Cet article propose une méthode basée sur des procédures stockées pour améliorer les performances des insertions batch impliquant scope_identity(). Il utilise des paramètres table, des instructions MERGE et des clauses OUTPUT pour insérer efficacement des enregistrements dans des tables associées et capture les ID générés à l'aide d'une table de mappage.

Exemple de tableau

Nous créons d'abord des exemples de tables tblBase et tblRelated, qui ont une relation un-à-plusieurs entre elles.

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

Type de table défini par l'utilisateur

Ensuite, nous définissons des types de tables définis par l'utilisateur (UDT) pour représenter les données d'entrée des tables tblBase et tblRelated.

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

Procédure stockée

La stp_InsertMultipleRecordsToMultipleTables procédure stockée suivante accepte les UDT udt_base et udt_related en entrée et effectue des opérations d'insertion en masse.

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

Description

• Cette procédure stockée accepte UDT en entrée.
• Il utilise l'instruction MERGE pour insérer des enregistrements dans tblBase. Cette méthode garantit que la table est mise à jour ou insérée (mise à jour si un enregistrement correspondant existe).
La clause
• OUTPUT capture le base_id généré et le base_temp_id temporaire utilisé pour joindre les tables associées.
• Ces valeurs sont stockées dans le tableau @idMap.
• Le processus utilise ensuite @idMap pour insérer les enregistrements associés dans tblRelated, garantissant ainsi le maintien des relations de clé étrangère.

Test

Testé sur 10 enregistrements parents et 1000 enregistrements enfants, le processus s'est exécuté en moins d'1 seconde.

Conclusion

Cette approche basée sur des procédures stockées offre des améliorations significatives des performances pour les insertions par lots à l'aide de scope_identity(). Il élimine le besoin d'interroger en boucle, réduisant ainsi le nombre d'appels à la base de données et le temps de verrouillage. De plus, il exploite les paramètres table et les fonctions table (UDF) pour manipuler efficacement les données.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!