faire passer une collection de uniqueidentifier en paramètre dans une PS

**thor76160** · 06/09/2010, 12h22

Bonjour,
j'ai une procédure stockée, et j'y fais passer en paramètre un uniqueidentifier. seulement j'aimerais y faire passer toute une liste, est-ce possible ?

je vous montre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
CREATE FUNCTION get_distance_metres(@latitudeDepart FlOAT, @longitudeDepart FLOAT, @latitudeArrivee FLOAT, @longitudeArrivee FLOAT)
RETURNS FLOAT
AS
BEGIN
	DECLARE @radLongitudeDepart FLOAT, @radLatitudeDepart FLOAT, @radLongitudeArrivee FLOAT, @radLatitudeArrivee FLOAT, @dlo FLOAT, @dla FLOAT, @a FLOAT
 
	SET @radLongitudeDepart = RADIANS(@longitudeDepart)
    SET @radLatitudeDepart = RADIANS(@latitudeDepart)
    SET @radLongitudeArrivee = RADIANS(@longitudeArrivee)
    SET @radLatitudeArrivee = RADIANS(@latitudeArrivee)
 
	SET @dlo = (@radLongitudeArrivee - @radLongitudeDepart) / 2
    SET @dla = (@radLatitudeArrivee - @radLatitudeDepart) / 2
 
    SET @a = SIN(@dla) * SIN(@dla) + COS(@radLatitudeDepart) * COS(@radLatitudeArrivee) * SIN(@dlo) * SIN(@dlo)
    RETURN (6378137 * 2 * ATN2(SQRT(@a), SQRT(1 - @a)))
END
GO
 
CREATE PROCEDURE SearchInZone
@id_region uniqueidentifier, @id_departement uniqueidentifier, @id_chef_lieu_departemental uniqueidentifier, @id_canton uniqueidentifier, @id_ville_depart uniqueidentifier, @rayon INT
AS
	/* nous partons d'un point de départ, le centre de la zone de recherche */
	DECLARE @latitude_depart FLOAT, @longitude_depart FLOAT
 
	SELECT
		@latitude_depart = latitude,
		@longitude_depart = longitude
	FROM
		Ville
	WHERE
		id_ville = @id_ville_depart
 
	/* variable de construction de la requete */
	DECLARE @requete VARCHAR(1000), @req_GDM VARCHAR(100),  @i INT
 
	SET @req_GDM = 'dbo.get_distance_metres(' + CONVERT(VARCHAR, @latitude_depart) + ', ' + CONVERT(VARCHAR, @longitude_depart) + ', latitude, longitude)'
	SET @requete = 'SELECT id_ville, nom_ville, arrondissement_ville, ROUND(' + @req_GDM + ' / 1000 , 2) AS distance
					FROM Ville V '
	SET @i = 0
 
	/* construction de la requete */
	IF @id_canton IS NOT NULL
		BEGIN
			SET @i = @i + 1
			SET @requete = @requete + 'WHERE V.id_canton = ''' + CONVERT(CHAR(36), @id_canton) + ''''
		END
	ELSE
		BEGIN
			IF @id_chef_lieu_departemental IS NOT NULL
				BEGIN
					SET @i = @i + 1
					SET @requete = @requete + 'WHERE V.id_chef_lieu_departemental = ''' + CONVERT(CHAR(36), @id_chef_lieu_departemental) + ''''
				END
			ELSE
				BEGIN
					IF @id_departement IS NOT NULL
						BEGIN
							SET @i = @i + 1
							SET @requete = @requete + 'WHERE V.id_departement = ''' + CONVERT(CHAR(36), @id_departement) + ''''
						END
					ELSE
						BEGIN
							IF @id_region IS NOT NULL
							BEGIN
								SET @i = @i + 1
								SET @requete = @requete + 'INNER JOIN Departement D ON V.id_departement = D.id_departement
												WHERE D.id_region = ''' + CONVERT(CHAR(36), @id_region) + ''''
							END
						END
				END
		END
 
	/* intégration du périmètre de recherche */
	IF @i = 0
		BEGIN
			SET @requete = @requete + ' WHERE '
		END
	ELSE
		BEGIN
			SET @requete = @requete + ' AND '
		END
 
	SET @requete = @requete + @req_GDM + ' < ' + CONVERT(VARCHAR, @rayon)
 
	/* order by */
	SET @requete = @requete + ' ORDER BY distance ASC'
 
	/* exécution de la reqiête */
	EXEC(@requete)
GO

j'aimerais faire passer plusieurs villes ou canton en paramètre. avez-vous une solution à me proposer svp ? merci

**SQLpro** · 06/09/2010, 14h58

Voulez-vous une solution, pratique simple et peu performante ou une solution performante mais plus complexe à mettre en œuvre ?

A +

**thor76160** · 06/09/2010, 15h47

Envoyé par SQLpro

Voulez-vous une solution, pratique simple et peu performante ou une solution performante mais plus complexe à mettre en œuvre ?

A +

j'opte pour la performance ! je ne veux pas d'une solution qui sera tellement lourde que mon SGBD en sera ralenti ! (car j'appelle ça du gachi ^^)

j'ai d'ailleurs pensé à une solution :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
CREATE TABLE #lstRegions (
	id_region	uniqueidentifier
)
go
 
CREATE TABLE #lstDepartements (
	id_departement	uniqueidentifier
)
go
 
CREATE TABLE #lstChefs_lieu_departemental (
	id_chef_lieu_departemental	uniqueidentifier
)
go
 
CREATE TABLE #lstCantons (
	id_canton	uniqueidentifier
)
go
 
INSERT INTO #lstRegions VALUES
('{74836C6C-0FE5-4C8C-90FB-70BA4D73F505}'),
('{50B205A4-F2C4-4CBD-A0B2-1B2C8234608E}')
go
 
INSERT INTO #lstDepartements VALUES
('{2D471A5C-2F55-4A6D-91EC-B267913B1CE0}'),
('{F64C2CE6-0B6E-4AF0-B952-6BB929D7D909}'),
('{165A177A-36DE-4F49-8058-1D9E3B2E8CD5}')
go
 
INSERT INTO #lstChefs_lieu_departemental VALUES
('{28482FDF-E4E1-43FC-A83C-FF1F2A2CDC4D}'),
('{A517EE39-A1C0-4263-BC63-458E01A10BE1}'),
('{122ECC87-9589-47D0-90E2-E7E81CC04322}'),
('{660695DE-8206-49C3-B806-5D5E8CABE9CD}')
go
 
INSERT INTO #lstCantons VALUES
('{29A9C3D4-9951-4FDF-BCF5-23CC2D8C12E1}'),
('{0393C085-0449-4687-8F0D-9807937876F7}'),
('{314587BB-3750-41FF-B711-7A122CC432CD}'),
('{ACC49B3A-41E7-4A4D-857A-C8A9932B6A0F}'),
('{2DC22471-C8AA-4798-B49B-92638A38CCB4}'),
('{AF434B69-9E4A-4300-8CAB-1DB62EC2DB22}'),
('{6449874E-7EE0-45C5-AAB8-65A907B529D6}')
go

j'ai créé des tables de collection, leur contenu n'est que temporaire !
quand je serais dans mon projet (notamment c#), je remplirais mes tables avec les infos dont j'ai besoin. voila le nouveau code de ma Procédure stockée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
CREATE PROCEDURE SearchInZone
@region BIT, @departement BIT, @chef_lieu_departemental BIT, @canton BIT, @id_ville_depart uniqueidentifier, @rayon INT
AS
	/* nous partons d'un point de départ, le centre de la zone de recherche */
	DECLARE @latitude_depart FLOAT, @longitude_depart FLOAT
 
	SELECT
		@latitude_depart = latitude,
		@longitude_depart = longitude
	FROM
		Ville
	WHERE
		id_ville = @id_ville_depart
 
	/* variable de construction de la requete */
	DECLARE @requete VARCHAR(1000), @req_GDM VARCHAR(100),  @i INT
 
	SET @req_GDM = 'dbo.get_distance_metres(' + CONVERT(VARCHAR, @latitude_depart) + ', ' + CONVERT(VARCHAR, @longitude_depart) + ', latitude, longitude)'
	SET @requete = 'SELECT id_ville, nom_ville, arrondissement_ville, ROUND(' + @req_GDM + ' / 1000 , 2) AS distance
					FROM Ville V '
	SET @i = 0
 
	/* construction de la requete */
	IF @canton = 1
		BEGIN
			SET @i = @i + 1
			SET @requete = @requete + 'WHERE V.id_canton IN (SELECT id_canton FROM #lstCantons)'
		END
	ELSE
		BEGIN
			IF @chef_lieu_departemental = 1
				BEGIN
					SET @i = @i + 1
					SET @requete = @requete + 'WHERE V.id_chef_lieu_departemental IN (SELECT id_chef_lieu_departemental FROM #lstChef_lieu_departemental)'
				END
			ELSE
				BEGIN
					IF @departement = 1
						BEGIN
							SET @i = @i + 1
							SET @requete = @requete + 'WHERE V.id_departement (SELECT id_departement FROM #lstDepartements)'
						END
					ELSE
						BEGIN
							IF @region = 1
							BEGIN
								SET @i = @i + 1
								SET @requete = @requete + 'INNER JOIN Departement D ON V.id_departement = D.id_departement
												WHERE D.id_region IN (SELECT id_region FROM #lstRegions)'
							END
						END
				END
		END
 
	/* intégration du périmètre de recherche */
	IF @i = 0
		BEGIN
			SET @requete = @requete + ' WHERE '
		END
	ELSE
		BEGIN
			SET @requete = @requete + ' AND '
		END
 
	SET @requete = @requete + @req_GDM + ' < ' + CONVERT(VARCHAR, @rayon)
 
	/* order by */
	SET @requete = @requete + ' ORDER BY distance ASC'
 
	/* exécution de la reqiête */
	EXEC(@requete)
 
	/* suppression des tables temporaires */
	DELETE FROM #lstRegions
	GO
 
	DELETE FROM #lstDepartements
	GO
 
	DELETE FROM #lstChefs_lieu_departemental
	GO
 
	DELETE FROM #lstCantons
	GO
GO

y a t'il encore mieux comme solution ?

appel de procédure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

EXEC SearchInZone 0, 0, 1, 1, '{25EEF587-6101-4327-A3E2-1B7EFE34E48B}', 15000

résultats :

C76C963A-FBFD-4B1B-9589-FC02D069D259 Le Petit-Quevilly NULL 9,07
82688ACB-3832-4363-8B91-26429BF22B6E Tourville-la-Rivière NULL 11,7
FE7B3BD8-6395-4509-AFFF-0361ABE7C655 Sotteville-sous-le-Val NULL 12,05
DC7F5A1A-1CA8-4014-A5BB-50FAFF597608 Freneuse NULL 13,98

**SQLpro** · 06/09/2010, 16h08

Créez dans votre base une table de nom T_LISTE_DATA_LSD avec les colonnes suivantes :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE dbo.T_LISTE_DATA_LSD
(LSD_ID          BIGINT IDENTITY PRIMARY KEY,
 LSD_TRAN_GUID   UNIQUEIDENTIFIER NOT NULL UNIQUE,
 LSD_DATA        VARCHAR(256) NOT NULL);
GO
 
CREATE INDEX X ON dbo.T_LISTE_DATA_LSD (LSD_TRAN_GUID, LSD_DATA);
GO

Dans votre code client, pour chaque liste de données à utiliser dans une requête, commencer par générer un GUID que vous utiliserez à titre de jeton transactionnel.
Insérez votre liste de données dans la colonne LSD_DATA avec toujours le même jeton transactionnel (GUID).
Dans votre requête remplacez la liste par une sous requête faisant appel à la table ainsi créée filtrée sur le jeton transactionnel.
Supprimez les lignes relative à ce jeton transactionnel après usage.

Exemple - soit faire une requête incorporant la liste 'Paris', 'Lyon', 'Marseille' :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
DECLARE @JETON UNIQUEIDENTIFIER;
 
SET @JETON = NEWID();
 
INSERT INTO dbo.T_LISTE_DATA_LSD
VALUES (@JETON , 'Paris'), (@JETON , 'Lyon'), (@JETON , 'Marseille');

Dès lors récrivez votre PS comme suit :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SELECT id_ville, nom_ville, arrondissement_ville, 
       ROUND(dbo.get_distance_metres(@latitude_depart, @longitude_depart,
             V.latitude, V.longitude) / 1000 , 2) AS distance
FROM   Ville V 
WHERE  id IN (SELECT id 
              FROM   MatableGeo AS G
                     INNER JOIN dbo.T_LISTE_DATA_LSD AS L
                           ON G.nom_geo = L.LSD_DATA)
  AND  dbo.get_distance_metres(@latitude_depart, @longitude_depart,
                               V.latitude,       V.longitude) <= @rayon
ORDER BY distance  ;

A +

**WOLO Laurent** · 07/09/2010, 09h59

Quelle version de SQL Serveur ?

**thor76160** · 09/09/2010, 10h38

j'ai sqlserver 2008 pourquoi ?

**WOLO Laurent** · 09/09/2010, 11h49

Possibilité d'utiliser le type xml à partir de la V2005.

**SQLpro** · 14/09/2010, 10h33

Oui, mais c'est BEAUCOUP, BEAUCOUP, BEAUCOUP moins performant !!!!

A +

**WOLO Laurent** · 14/09/2010, 17h21

Nulle doute que cela soit extrêmement lent. Je vous crois sur parole.
La grande question reste de prendre le compromis entre faire plusieurs accès à la base de données et faire un seul accès !
Je reviendrais vers vous pour poser un problème plus clair.

**SQLpro** · 14/09/2010, 17h45

Par nature il est rare qu'une PS fasse un seul acces à la base.

Or rajouter un accès distant pour insérer dans une table est moins couteux, si la volumétrie des données à transmettre est importante.
De plus, ce qui est pire, cela interdit la parallélisation du plan de requête car le dépilage des ID via XML nécessite une logique itérative au sein d'une requête ensembliste !

A +

**WOLO Laurent** · 15/09/2010, 11h15

Inutile alors d'insister les arguments sont très convainquants.
Merci.

**Philippe Robert** · 16/09/2010, 14h50

Envoyé par SQLpro

Par nature il est rare qu'une PS fasse un seul acces à la base.

Or rajouter un accès distant pour insérer dans une table est moins couteux, si la volumétrie des données à transmettre est importante.
De plus, ce qui est pire, cela interdit la parallélisation du plan de requête car le dépilage des ID via XML nécessite une logique itérative au sein d'une requête ensembliste !

A +

En SQL 2008, on peut passer une variable table à un procédure stockée il me semble ?

**WOLO Laurent** · 16/09/2010, 15h14

Même si c'est le cas, le problème restera entier côté ADO.Net n'est ce pas ?

**Philippe Robert** · 16/09/2010, 15h20

Envoyé par WOLO Laurent

Même si c'est le cas, le problème restera entier côté ADO.Net n'est ce pas ?

Exact, mais je suppose que la dernière version de .NET doit supporter cette fonctionalité.

**WOLO Laurent** · 16/09/2010, 15h36

Reste qu'on fasse valider ces informations.

**WOLO Laurent** · 16/09/2010, 15h51

Bonjour,

Oui, vous avez parfaitement raison ce lien le confirme.
Made in SQL Serveur 2008 et DotNet 4 !

Il faut tout de même s'assurer des performances de la méthode.

**elsuket** · 17/09/2010, 09h42

Bonjour,

Comme il n'y a aucune statistique maintenue sur les variables de type table, il me semble que SQL Server estime par défaut la cardinalité d'une telle table à 1 par défaut.
Donc si vous y mettez beaucoup de valeurs, le plan sera peu optimal.

Une autre solution (peut-être pas des meilleures) serait de stocker toutes ces valeurs dans une table de la base de données dans la procédure stockée, et d'en supprimer les valeurs juste à la fin de celle-ci ...

@++