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

Version imprimable

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:

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 :)

06/09/2010, 14h58
SQLpro

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

A +

Citation:

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:

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:

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:

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

résultats :

Citation:

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

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

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:

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:

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 +

07/09/2010, 09h59
WOLO Laurent

Quelle version de SQL Serveur ?
09/09/2010, 10h38
thor76160

j'ai sqlserver 2008 pourquoi ?
09/09/2010, 11h49
WOLO Laurent

Possibilité d'utiliser le type xml à partir de la V2005.
14/09/2010, 10h33
SQLpro

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

A +
14/09/2010, 17h21
WOLO Laurent

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.
14/09/2010, 17h45
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 +
15/09/2010, 11h15
WOLO Laurent

Inutile alors d'insister les arguments sont très convainquants.
Merci.
16/09/2010, 14h50
Philippe Robert

Citation:

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 ?
16/09/2010, 15h14
WOLO Laurent

Même si c'est le cas, le problème restera entier côté ADO.Net n'est ce pas ?
16/09/2010, 15h20
Philippe Robert

Citation:

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é.
16/09/2010, 15h36
WOLO Laurent

Reste qu'on fasse valider ces informations.
16/09/2010, 15h51
WOLO Laurent

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.
17/09/2010, 09h42
elsuket

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 ... ;)

@++ ;)
17/09/2010, 10h29
WOLO Laurent

Je pense que l'écriture des données en disque dans ce cas d'espèce sera plus lentes que leur traitement.
17/09/2010, 11h03
Philippe Robert

Est-ce que l'usage de variable table sollicite Tempdb ?
17/09/2010, 11h22
mikedavem

Citation:

Est-ce que l'usage de variable table sollicite Tempdb ?

Oui les variables de tables sont stockées en tant qu'objet dans tempdb.

++