SQLServer Support France

Le blog de l'équipe de support francophone des technologies de base de données Microsoft : SQL Server, Reporting Services (SSRS), Integration Services (SSIS), Analysis Services (SSAS). Echanges, bonnes pratiques, retours d'expérience...

Les « Tables Valued Parameters »

Les « Tables Valued Parameters »

Rate This
  • Comments 1

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

 

Leave a Comment
  • Please add 7 and 4 and type the answer here:
  • Post
  • 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....

Page 1 of 1 (1 items)