Les « Tables Valued Parameters »

Technorati Tags: Katmai,SQL Server,sql2k8,sql2008,MSSQL

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

• Anonymous
  December 25, 2011
  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....