Les « Tables Valued Parameters »



Avant la version SQL Server 2008 il n’était pas possible de passer une table comme paramètre d’une procédure stockée. Si nous prenons l’exemple d’une base de données musicale simplifiée, elle est constituée de trois tables, une pour les artistes, une pour les albums et une pour les chansons. Ajouter un album à la base revient à ajouter des données à chacune de ces tables. On pouvait utiliser différentes procédure stockées pour chaque table, faire des boucles dans l’application ce qui impliquait des allers-retours avec le client.


SQL Server 2008 a introduit les « Tables Valued Parameters » (paramètres table) qui nous permettent de passer un nombre inconnu de données à une procédure stockée.


 


Pour l’exemple voici les tables nécessaires:


 


Create Table Artistes (ArtisteID int identity, Artiste  varchar(120))


Create Table Albums (AlbumID int identity, ArtistID int,  Album varchar(120))


Create Table Chansons (ChansonsID int identity, Titre varchar(120), Track int, ArtistID int,  Album varchar(120))


 


Pour commencer il faut définir un Type de table :


 


Create Type Tbl_ChansonType as Table


(Titre varchar(120) not null, Track int)


Go


 


Voici la procédure stockée qui va nous permettre de tout réaliser en une passe :


 


create procedure AddChanson(


 @Artiste varchar(120), @Album varchar(120), @Chanson Tbl_ChansonType  READONLY)


as


begin


 set nocount on


 — Ajouter Artiste


 Declare @ArtisteID int


 insert into Artistes values (@Artiste)


 select @ArtisteID = SCOPE_IDENTITY()


 


 — Ajouter Album


 Declare @AlbumID int


 insert into Albums values (@ArtisteID, @Album )


 select @AlbumID = SCOPE_IDENTITY()


 


 — Ajouter Chanson


 insert into Chansons


 select Titre, Track, @ArtisteID, @AlbumID


 from @Chanson


end


 


Voici un exemple d’utilisation depuis T-SQL :


 


declare @New_Album  as Tbl_ChansonType;


insert  @New_Album values (‘Highway Star’, 1);


insert  @New_Album values (‘Maybe I”m a Leo’, 2);


insert  @New_Album values (‘Pictures of Home’, 3);


insert  @New_Album values (‘Never Before’, 4);


insert  @New_Album values (‘Smoke on the Water’, 5);


insert  @New_Album values (‘Lazy’, 6);


insert  @New_Album values (‘Space Truckin’, 7);


 


exec AddChanson ‘Deep Purple’, ‘Machine Head ‘, @New_Album


 


Voici un exemple en Visual Basic .Net:


 


Dim Chansons As New DataTable


Chansons = New DataTable(“Tbl_ChansonType”)


 


Dim Row1, Row2 As DataRow


 


Dim Titre As DataColumn = New DataColumn(“Titre”)


Titre.DataType = System.Type.GetType(“System.String”)


Chansons.Columns.Add(Titre)


 


‘definir les colonnes de la table


Dim Track As DataColumn = New DataColumn(“Track”)


Track.DataType = System.Type.GetType(“System.Int32”)


Chansons.Columns.Add(Track)


 


Row1 = Chansons.NewRow()


Row1.Item(“Titre”) = “Highway Star”


Row1.Item(“Track”) = “1”


Chansons.Rows.Add(Row1)


 


Row2 = Chansons.NewRow()


Row2.Item(“Titre”) = “Smoke on the Water”


Row2.Item(“Track”) = “5”


Chansons.Rows.Add(Row2)


 


Dim cmd As New SqlCommand()


cmd.Connection = conn


cmd.CommandType = CommandType.StoredProcedure


cmd.CommandText = “AddChanson”


 


‘ Ajout des parameters de la procedure


cmd.Parameters.AddWithValue(“@Artiste”, “Deep Purple”)


cmd.Parameters.AddWithValue(“@Album”, “Machine Head”)


cmd.Parameters.AddWithValue(“@Chanson”, Chansons)


 


conn.Open()


 


cmd.ExecuteNonQuery()


 


conn.Close()


 


Nous pouvons constater avec SQL Profiler que nous avons réduit les échanges entre le client et le serveur à une seule exécution RPC pour tous les enregistrements.


 


declare @p3 dbo.Tbl_ChansonType


insert into @p3 values(N’Highway Star’,1)


insert into @p3 values(N’Smoke on the Water’,5)


 


Dernier point, quand doit-on utiliser les opérations BULK ou les « Valued Parameters » ?


En général l’expérience montre que les Bulk Insert sont plus efficace si le nombre de lignes a inséré est supérieur à 1000. Le tableau ci-après résume les différentes configurations :

















































Source


Logique d’insertion


Nombre de lignes


Meilleure technologie


Fichier


Insertion directe


< 1000


BULK INSERT


Fichier


Insertion directe


> 1000


BULK INSERT


Fichier


Insertion complexe


< 1000


TPV


Fichier


Insertion complexe


> 1000


BULK INSERT


Client


Insertion directe


< 1000


TPV


Client


Insertion directe


> 1000


BULK INSERT


Client


Insertion complexe


< 1000


TPV


Client


Insertion complexe


> 1000


TPV


Fichier = les données proviennent d’un fichier structuré sur le serveur


Client = L’application cliente fournis les données


Complexe = de la logique doit être appliquée aux données avant l’insertion


 


 Fred Pichaut


 


Comments (1)

  1. Patrick Lambin says:

    Excellent article de la part de Frédéric Pichault, aussi bon que sa conférence aux Journées SQL Server 2012 (Optimisation et TroubleShooting SQL Server)

    Je fréquente plutôt le psssql blog , mais c'est du même niveau de clarté et profondeur ( idem pour les autres articles de ce blog français et qui n'ont pas été écrits par Frédéric Pichault car j'ai bien l'impression que plusieurs se cachent derrière le nom de Docteur Watson1 )

    Pour l'auteur de cet article, je m'étais trompé en disant que cet article datait de 9 mois, c'est 23 en réalité.

    Continuez tous à nous sortir d'aussi bons articles : c'est un régal….