SQL Server & C# : comment faire de multiples insertions rapidement en une seule transaction ?
J’ai été confronté à une problématique intéressante récemment : comment faire plusieurs insertions en base de données (SQL Server) en une seule transaction et rapidement ?
Le problème est relativement simple : un web service doit insérer plusieurs lignes dans la même table.
La solution de base consisterait à faire quelque chose qui ressemble à ça :
using (SqlConnection con = new SqlConnection("connectionString") ) { con.Open(); foreach(var produit in produitList) { try { using (SqlCommand command = new SqlCommand( "INSERT INTO Product (Id, Model, CreationDate) VALUES(@Id, @Model, @CreationDate)", con)) { command.Parameters.Add(new SqlParameter("@Id", produit.Id)); command.Parameters.Add(new SqlParameter("@Model", produit.Model)); command.Parameters.Add(new SqlParameter("@CreationDate", produit.CreationDate)); command.ExecuteNonQuery(); } } } }
Avantage :
- Rapide et simple.
Problèmes :
- on subit toutes les latences réseau entre le serveur d’application et la base de données à chaque insertion. Dans le cadre d’une base de donnée avec un failover, il faut en plus le délais de réplication sur les serveurs miroirs à chaque insertion…
- Pas transactionné : si le service a un problème en milieu de traitement, il est difficile de le reprendre.
Tout ça fait que cette solution sera peu performante avec une volumétrie importante à insérer. Cette méthode sera donc à réserver pour un nombre limité d’insertions.
Pour réduire le temps de traitement, il faut supprimer les latences réseaux et tout insérer en un appel.
L’idée d’un insert de 5km construit par concaténation de chaines de caractères ne me plaisant pas, j’ai essayé de chercher un peu sur le net, et j’ai découvert une méthode bien plus élégante : utiliser une procédure stockée avec un paramètre au format xml.
Les procédures stockées ne pouvant pas prendre de listes ou tableaux en paramètre d’entrée, l’utilisation du XML permet de contourner ce problème. Avec relativement peu de lignes de codes, on peut sérialiser et désérialiser les données en XML coté C# et SQL :
C# :
using (SqlConnection con = new SqlConnection("connectionString") ) { con.Open(); var sqlCmd = new SqlCommand(@"[ProcStock_Insertion_produits]", connection); sqlCmd.sqlCmd.CommandType = CommandType.StoredProcedure; //Serialisation en XML de mes produits XElement xmlProductsArg = new XElement("Products", from p in produitList select new XElement("Product", new XAttribute("Id", p.Id), new XAttribute("Model", p.Model ), new XAttribute("CD", p.CreationDate.ToString("YYYY-MM-dd"))) ); //Creation du paramètre SqlParameter ProduitsParam = new SqlParameter("@Produits", System.Data.SqlDbType.Xml); ProduitsParam.Value = new SqlXml(xmlProductsArg.CreateReader()); sqlCmd.Parameters.Add(ProduitsParam); //Execution command.ExecuteNonQuery(); }
Et la procédure stockée :
CREATE PROCEDURE [dbo].[ProcStock_Insertion_produits] ( @Produits xml ) AS BEGIN --Creation d'une table temporaire où copier les données issues du XML DECLARE @Products TABLE (ProduitId int, Modele Varchar(200), DateCreation date ) INSERT INTO @Products (ID) SELECT Tbl.Col.value('@Id', 'decimal'), Tbl.Col.value('@Model', 'VARCHAR(120)'), Tbl.Col.value('@CreationDate', 'NVARCHAR(50)') FROM @Produits.nodes('/Products/Product') Tbl(Col) --éventuel traitement avant import pour assurer la cohérence des données : --suppression des produits existants déjà avant import DELETE FROM @Products WHERE ProduitId not in (Select Id From Product) --insertion des données INSERT INTO Product (Id, Model, CreationDate) SELECT ProduitId, Modele , CreationDate FROM @Products END GO
Cela demande donc un peu plus de travail qu’un insert basique mais les perfs sont extras : 30 000 insertion en moins d’une seconde, pas de long lock sur la table, c’est plaisant 
