Faire des updates efficaces grâce aux Table Valued Parameters

Lorsqu’on travaille sur des projets complexes, il peut arriver que nous ayons à mettre à jour des milliers de lignes d’un coup, avec des valeurs totalement différentes pour chaque. Comment faire ça de façon rapide et efficace ? Grâce aux Table Valued Parameters (ou TVP).

Qu’est ce qu’un Table Valued Parameter ?

Un Table Valued Parameter est un type de table personnalisé défini dans SQL Server.

Ajouté dans ce SGBD à partir de la version 2008, ce type de données personnalisé permet de définir un format de table utilisable en paramètre par des procédures stockées. Cela permet donc de ne pas avoir à passer par une table temporaire ou par du XML pour faire de nombreux updates.

Le Table Valued Parameter offre bien plus de flexibilité que les tables temporaires. Il est fortement typé, réutilisable, indexable, plus performant et peut même contenir une clef primaire ! De plus, l’utiliser depuis une application est très simple, puisqu’il suffit de passer par une DataTable du même format que notre Table Valued Parameter !

Comment ça marche ?

Tout d’abord, côté SQL Server, il va nous falloir créer notre type de données. Imaginons que nous ayons la table suivante pour gérer les URLs réécrites d’un site :

Malheureusement, suite à d’importantes modifications dans le moteur de navigation du site, nous allons devoir réécrire côté C# toutes nos URLs afin qu’elles fonctionnent avec le nouveau moteur.

Nous allons donc ajouter une nouvelle ligne dans notre table, destinée à contenir les nouvelles URLs générées :

Puis nous allons créer notre type Table Valued Parameters qui nous servira de paramètre pour mettre à jour toutes nos URLs :

USE [MyDatabase]
GO

CREATE TYPE TVP_UpdateURL AS TABLE
(
Id INT,
NewURL VARCHAR(1024)
);
GO

Voilà. Nous pouvons maintenant voir notre Table Valued Parameter ici :

Maintenant, nous allons créer la procédure stockée qui se chargera de mettre à jour nos URLs, avec comme paramètre notre Table Valued Parameters :

USE [MyDatabase]
GO

CREATE PROCEDURE [dbo].[PS_UPDATE_URL]
(
@NewUrls TVP_UpdateURL READONLY
)
AS
BEGIN
UPDATE u
SET [URLNewEngine] = tvpu.NewURL
FROM [dbo].[URLRewriting] tu
INNER JOIN @NewUrls tvpu
ON tu.Id = tvpu.Id
END

Simple n’est-ce pas ?

Il est à noter deux choses cependant :

• Un Table Valued Parameter doit toujours être en READONLY, son contenu n’est donc pas modifiable.
• SQL Server Management Studio a parfois du mal avec les Table Valued Parameters. S’il vous indique qu’il ne connaît pas votre type Table Valued Parameter fraîchement créé, quittez-le et relancez-le.

Pour tester notre procédure stockée, nous pouvons créer à la main un objet de type TVP_UpdateURL et le passer en paramètre à l’appel de notre procédure stockée :

DECLARE @tmpNewUrls TVP_UpdateURL

INSERT INTO @tmpNewUrls (Id, NewURL) VALUES (1, ‘https://www.monsite.com/ma-nouvelle-url’);
INSERT INTO @tmpNewUrls (Id, NewURL) VALUES (2, ‘https://www.monsite.com/ma-nouvelle-url-2’);

EXECUTE [dbo].[PS_UPDATE_URL] @tmpNewUrls

Enfin, nous allons appeler notre procédure stockée en C#, en créant une DataTable au format de notre Table Valued Parameter :

using (var cnx = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("PS_UPDATE_URL")
{
Connection = cnx,
CommandType = CommandType.StoredProcedure
})
{
// Tout d'abord nous créons une DataTable au format de notre Table Valued Parameter
DataTable TblNewURL = new DataTable();

TblNewURL.Columns.Add("Id", typeof(int));
TblNewURL.Columns.Add("NewURL", typeof(string));

DataRow TblRow;

// listOfURL étant ma liste d'objets URL avec les ids et les URL à jour
foreach (var url in listOfURL)
{
TblRow = TblNewURL.NewRow();

TblRow[0] = url.Id;
TblRow[1] = url.NewUrl;

TblNewURL.Rows.Add(TblRow);
}

SqlParameter SqlParam = cmd.Parameters.Add("@NewUrls", System.Data.SqlDbType.Structured);
SqlParam.Value = TblNewURL;

cnx.Open();

object result = cmd.ExecuteScalar();

cnx.Close();
}
}

Et voilà, vous savez désormais comment faire vos 10^x updates en un temps record ! Génial non ?

Et qu’en est-il des insert ?

Les Table Valued Parameters sont plus efficaces, en terme de performances pures, que le BULK INSERT, pour toutes les insertions de moins de 1000 lignes. En effet, le BULK INSERT a un coût très élevé au départ, que les Table Valued Parameters n’ont pas. Ainsi, si vous avez régulièrement à insérer d’un coup moins de 1000 lignes, il vaut mieux les insérer à l’aide de Table Valued Parameters. Au delà, utiliser le BULK INSERT sera préférable.

Si vous avez des questions n’hésitez pas !