Discussion :

[JmL40]

Bonjour,

Nous avons développé pour ma société un module de calcul de moyenne dans le domaine de la génétique. Il s'agit tout simplement d'effectuer des comparaisons de valeurs pour des individus A et B et d'extraire des résultats du type moyenne, nombre de valeurs identiques ...

Cependant nous nous sommes rendu compte que générer ces résultats sur le poste client de manière instantanée est trop long au niveau des temps d'exécution, car un individus A peut-être comparé a 2000 ou 3000 individus et chaque individus possède 500 à 1000 valeurs.

Nous avons donc décidé de mettre en place une procédure qui permet d'effectuer tous les traitements nécessaires chaque jour et d'alimenter une table pour ensuite faciliter l'extraction des données par le client avec une simple requête sur la table.

J'en viens à l'essentiel et à mes questions :

Dois-je obligatoirement en T-SQL utiliser les curseurs pour parcourir les enregistrements ?

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
 
DECLARE @MARQUEUR_1 varchar(20), @VALEUR_1 varchar(20), @MARQUEUR_2 varchar(20), @VALEUR_2 varchar(20) 
 
DECLARE CURSOR_FOR_1 CURSOR FOR
SELECT TOP(20) LAB_ANALYSE.MARQUEUR,LAB_ANALYSE.VALEUR
	FROM LAB_ANALYSE 
		WHERE CODE_FICHE IN 
			(SELECT DISTINCT(LAB_FICHE.CODE_FICHE)
				FROM LAB_FICHE
					WHERE LAB_FICHE.CODE_EXP = 'LSH369'
						AND LAB_FICHE.LOT = '')
OPEN CURSOR_FOR_1
FETCH NEXT FROM CURSOR_FOR_1 INTO @MARQUEUR_1, @VALEUR_1
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM CURSOR_FOR_1
 
	DECLARE CURSOR_FOR_2 CURSOR FOR
	SELECT TOP(20) LAB_ANALYSE.MARQUEUR,LAB_ANALYSE.VALEUR
	FROM LAB_ANALYSE 
		WHERE CODE_FICHE IN 
			(SELECT DISTINCT(LAB_FICHE.CODE_FICHE)
				FROM LAB_FICHE
					WHERE LAB_FICHE.CODE_EXP = 'LSH369'
						AND LAB_FICHE.LOT = '') AND LAB_ANALYSE.MARQUEUR = @MARQUEUR_1
 
	OPEN CURSOR_FOR_2
	FETCH NEXT FROM CURSOR_FOR_2 INTO @MARQUEUR_2, @VALEUR_2
	WHILE @@FETCH_STATUS = 0
	BEGIN
	FETCH NEXT FROM CURSOR_FOR_2
		PRINT 'TEST : ' + @VALEUR_2
	END
	CLOSE CURSOR_FOR_2
	DEALLOCATE CURSOR_FOR_2
 
END
CLOSE CURSOR_FOR_1
DEALLOCATE CURSOR_FOR_1

[elsuket]

Bonjour,

Dois-je obligatoirement en T-SQL utiliser les curseurs pour parcourir les enregistrements ?

Non, c'est tout le contraire de ce que vous devez faire avec un moteur de bases de données relationnelles SQL

En effet les SGBDR modernes sont conçus pour traiter les données dans leur ensemble, et non pas pour les traiter dans leur ensemble.

Il est donc normal, surtout avec le traitement que vous avez donné comme exemple, que vous ayez des performances médiocres.
Je montre ici la différence de vitesse d'exécution, comme la différence de simplicité d'écriture.
SQLPro vous montre ici comment éviter les curseurs sous SQL Server.

Donc pour faire simple : quand vous écrivez un traitement en bases de données relationnelle SQL, oubliez toute logique itérative, et pensez par ensembles.

Même remarque pour l'opérateur TOP : il est anti-relationnel, puisqu'il ne retourne qu'un sous-ensemble "au hasard" de l'ensemble résultat de votre requête.
C'est bien pour avoir un aperçu pendant la phase de développement, mais vous devriez vous en passer une fois celle-ci terminée.

Enfin à la place d'utiliser IN, vous pouvez utiliser des jointures.
Normalement IN ne devrait être utilisé que pour spécifier une liste de valeurs "en dur".

Le filtre AND LAB_FICHE.LOT = '' pourrait être simplifié, puisqu'en SQL l'absence de valeur est "représentée" par un marqueur : NULL.
Vous devriez donc mettre à jour votre table LAB_FICHE sur sa colonne LOT pour qu'elle soit NULL si sa valeur est une chaîne vide :

Code :

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

1
2
3
UPDATE dbo.LAB_FICHE
SET LOT = NULL
WHERE LOT = ''

Ceci sous réserve bien sûr que cette colonne soit NULLable.

Votre requête pourrait se transformer en une 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
CREATE PROCEDURE maProcedure
	@_CODE_EXP varchar(8)
	, @_LOT varchar(8) = NULL
AS
BEGIN
	IF @_LOT IS NULL
	BEGIN
		SELECT  A.MARQUEUR
			, A.VALEUR
		FROM		dbo.LAB_ANALYSE AS A
		INNER JOIN	dbo.LAB_FICHE AS F
					ON A.CODE_FICHE = F.CODE_FICHE
		WHERE		F.CODE_EXP = @_CODE_EXP
		AND		F.LOT IS NULL
	END
	ELSE
	BEGIN
		SELECT  A.MARQUEUR
			, A.VALEUR
		FROM		dbo.LAB_ANALYSE AS A
		INNER JOIN	dbo.LAB_FICHE AS F
					ON A.CODE_FICHE = F.CODE_FICHE
		WHERE		F.CODE_EXP = @_CODE_EXP
		AND		F.LOT = @_LOT
	END
END

Et pour rechercher les résultats d'une expérience, vous n'auriez plus qu'à écrire ou appeler :

Code :

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

EXEC dbo.maProcedure 'LSH369'

Si vous avez un lot spécifique pour lequel vous voulez rechercher des résultats, vous exécuteriez :

Code :

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

EXEC dbo.maProcedure 'LSH369', 'monLot'

Si en plus vous disposez de l'index dont la définition serait la suivante sur la table LAB_FICHE :

Code :

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

1
2
CREATE INDEX IX_LABFICHE__CODE_FICHE_CODE_EXP_LOT
ON dbo.LAB_FICHE (CODE_FICHE, CODE_EXP, LOT)

Vous aurez des temps de réponse qui seront à mon avis beaucoup beaucoup plus rapides, et vous en oublierez même l'alimentation d'une table à part

Néanmoins si vous avez ensuite besoin d'effectuer des statistiques ou des rapports, sachez qu'il existe d'autres fonctionnalités de SQL Server qui vous permettent de faire cela.

Je vous conseille de lire les cours de SQLPro qui vous en apprendront beaucoup sur SQL et sur les façons de tirer les meilleures performances de votre base de données

Mais au fait, quelle version et édition de SQL Server utilisez-vous ?
Si vous ne le savez pas, voici comment le trouver

@++

[JmL40]

Bonjour,

Tout d'abord, merci pour votre réponse, avec trois semaines de retard mais merci ... (congés obliges).

J'avoue que je suis un peu perdu, car je me suis concentré ces deux derniers jours à developper avec des curseurs le traitement que je souhaite mais après une heure d'exécution le résultat est médiocre car à peine 10% du traitement a été effectué.

J'explique avec précision mon problème et je met une visualisation de mon code.
En fait, j'ai une table qui se compose de la manière suivante :

(CODE_FICHE) (MARQUEUR) (VALEUR)
-------------------------------------------
2 ----------- 1022 ----------- 144#156
2 ----------- 1025 ----------- 179#192
2 ----------- 1028 ----------- 169#177
4 ----------- 1031 ----------- 88#93.94#98
4 ----------- 1022 ----------- 181#185
4 ----------- 1046 ----------- 219#227
4 ----------- 105 ----------- .
10 ----------- 1056 ----------- 122#131
10 ----------- 1046 ----------- 307#314
20 ----------- 1064 ----------- 194#208

Je souhaiterai comparer tous les CODE_FICHE un à un c'est-à-dire comparer CODE_FICHE = 2 à CODE_FICHE 4,10 et 20 sachant que je ne dois prendre en compte que les marqueurs communs au CODE_FICHE 2 et au CODE_FICHE comparé et ainsi de suite.
Je dois ensuite réaliser des traitements lors la comparaison :

- Compter le nombre de marqueurs communs entre chaques CODE_FICHE en fonction de la colonne MARQUEUR (si colonne MARQUEUR 1022 pour CODE_FICHE 2 est présente pour CODE_FICHE 4 alors nombre de marqueurs +1) mais aussi en fonction la valeur.

- Si le marqueur est commun alors comparer les valeurs des deux marqueurs pour chaques codes et vérifier si les valeurs sont communes, différentes, si celles-ci comportent un ' . ' alors découper la valeur et comparer la partie gauche et droite.

C'est assez complexe puisque je dois alimenter ensuite une table pour faire des selects.

Je vous passe le code que j'ai mis en place certe pas du tout optimisé car codé rapidement :

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
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
 
------------------------------------------------------------------------------
-- Déclaration des variables de traitement
	-- Declaration
	DECLARE @HTZ1 int, @HTZ2 int, @COMCM int, @COMPM int, @DIFFCM int, @DIFFPM int
	DECLARE @C1 int, @C2 int, @D1 int, @D2 int, @DCM int, @DPM int, @DIST int
	--Initialisation
	SET @HTZ1 = 0 SET @HTZ2 = 0 SET @COMCM = 0 SET @COMPM = 0 SET @DIFFCM = 0 SET @DIFFPM = 0
	SET @C1 = 0 SET @C2 = 0 SET @D1 = 0 SET @D2 = 0 SET @DCM = 0 SET @DPM = 0 SET @DIST = 0
------------------------------------------------------------------------------
 
------------------------------------------------------------------------------
-- Déclaration des variables par curseur
	-- Variables CURSEUR
	DECLARE @MARQUEUR varchar(20), @VALEUR varchar(20), @CODE_EXP varchar(20)
	-- Variables CURSEUR_X
	DECLARE @MARQUEUR_X varchar(20), @VALEUR_X varchar(20), @CODE_EXP_X varchar(20)
------------------------------------------------------------------------------
 
------------------------------------------------------------------------------
-- Declaration du curseur CURSEUR
	-- Declaration et integration de la requete dans le curseur
	DECLARE CURSEUR CURSOR FOR SELECT LAB_FICHE.CODE_EXP,LAB_ANALYSE.MARQUEUR,LAB_ANALYSE.VALEUR FROM LAB_FICHE INNER JOIN LAB_ANALYSE ON LAB_FICHE.CODE_FICHE = LAB_ANALYSE.CODE_FICHE
	-- Ouverture du curseur
	OPEN CURSEUR
	-- Récupération du premier enregistrement de la requete dans variable CURSEUR
	FETCH NEXT FROM CURSEUR INTO @CODE_EXP, @MARQUEUR, @VALEUR
------------------------------------------------------------------------------
 
------------------------------------------------------------------------------
-- Boucle sur les résultats de la requete dans CURSEUR
	-- Tant qu'il y a des enregistremnts
	WHILE @@FETCH_STATUS = 0
	-- Debut
	BEGIN
 
		------------------------------------------------------------------------------
		-- Declaration du curseur CURSEUR_X
			-- Declaration et integration de la requete dans le curseur
			DECLARE CURSEUR_X CURSOR FOR SELECT LAB_FICHE.CODE_EXP,LAB_ANALYSE.MARQUEUR,LAB_ANALYSE.VALEUR FROM LAB_FICHE INNER JOIN LAB_ANALYSE ON LAB_FICHE.CODE_FICHE = LAB_ANALYSE.CODE_FICHE WHERE LAB_ANALYSE.MARQUEUR = @MARQUEUR
			-- Ouverture du curseur
			OPEN CURSEUR_X
			-- Récupération du premier enregistrement de la requete dans variable CURSEUR_X
			FETCH NEXT FROM CURSEUR_X INTO @CODE_EXP_X, @MARQUEUR_X, @VALEUR_X
		------------------------------------------------------------------------------
 
		------------------------------------------------------------------------------
		-- Boucle sur les résultats de la requete dans CURSEUR
			-- Tant qu'il y a des enregistremnts
			WHILE @@FETCH_STATUS = 0
			-- Debut
			BEGIN					
 
				-- Insertion de la paire à comparer dans la table SYS_GENLABO
				IF (SELECT COUNT(*) FROM SYS_GENLABO WHERE INDIVIDU_1 = @CODE_EXP AND INDIVIDU_2 = @CODE_EXP_X) = 0
				BEGIN
					INSERT INTO SYS_GENLABO (INDIVIDU_1,INDIVIDU_2) VALUES (@CODE_EXP,@CODE_EXP_X)
				END
 
				-- Reinitialisation des compteurs
				SET @HTZ1 = 0 SET @HTZ2 = 0 SET @COMCM = 0 SET @COMPM = 0 SET @DIFFCM = 0 SET @DIFFPM = 0
				SET @C1 = 0 SET @C2 = 0 SET @D1 = 0 SET @D2 = 0 SET @DCM = 0 SET @DPM = 0 SET @DIST = 0
 
				-- Effectue le traitement uniquement si le marqueur existe pour CODE_EXP_X
				IF (SELECT COUNT(*) FROM LAB_FICHE INNER JOIN LAB_ANALYSE ON LAB_FICHE.CODE_FICHE = LAB_ANALYSE.CODE_FICHE WHERE LAB_FICHE.CODE_EXP = @CODE_EXP_X AND LAB_ANALYSE.MARQUEUR = @MARQUEUR) > 0
				BEGIN
 
					------------------------------------------------------------------------------
					--------------------------- TRAITEMENT DES DONNEES ---------------------------
					------------------------------------------------------------------------------
 
						------------------------------------
						-- Gestion des données hétérozygotes 
							-- HTZ1 :  si la chaine contient un '.'
							IF CHARINDEX ( '.' , @VALEUR ) > 0 AND LEN(@VALEUR) > 1
							BEGIN								
								-- Nombre d'hétérozygote pour VALEUR dans la paire
								SET @HTZ1 = (SELECT HETEROZYGOTE_1 FROM SYS_GENLABO WHERE INDIVIDU_1 = @CODE_EXP AND INDIVIDU_2 = @CODE_EXP_X)
								IF @HTZ1 IS NULL
									SET @HTZ1 = 1
								ELSE
									SET @HTZ1 = @HTZ1 + 1						
								-- Update nombre d'hétérozygote pour VALEUR dans la paire
								UPDATE SYS_GENLABO SET HETEROZYGOTE_1 = @HTZ1 WHERE INDIVIDU_1 = @CODE_EXP AND INDIVIDU_2 = @CODE_EXP_X
							END
 
							-- HTZ2 : si la chaine contient un '.'
							IF CHARINDEX ( '.' , @VALEUR_X ) > 0 AND LEN(@VALEUR_X) > 1
							BEGIN							
								-- Nombre d'hétérozygote pour VALEUR_X dans la paire
								SET @HTZ2 = (SELECT HETEROZYGOTE_2 FROM SYS_GENLABO WHERE INDIVIDU_1 = @CODE_EXP AND INDIVIDU_2 = @CODE_EXP_X)
								IF @HTZ2 IS NULL
									SET @HTZ2 = 1
								ELSE
									SET @HTZ2 = @HTZ2 + 1							
								-- Update nombre d'hétérozygote pour VALEUR dans la paire
								UPDATE SYS_GENLABO SET HETEROZYGOTE_2 = @HTZ2 WHERE INDIVIDU_1 = @CODE_EXP AND INDIVIDU_2 = @CODE_EXP_X
 
							END
						------------------------------------
 
						------------------------------------
						-- Traitement des données marqueurs
 
							-- Cas des données manquantes
							IF @VALEUR = '.' OR @VALEUR_X = '.' 
							BEGIN
								PRINT ''
							END						
							-- Cas des allèles non renseignés
							ELSE IF @VALEUR = '' OR @VALEUR_X = '' 
							BEGIN
								PRINT ''
							END	
							-- Cas général
							ELSE
							BEGIN	
								-- VALEUR et VALEUR_X non hétérozygotes
								IF CHARINDEX ('.',@VALEUR) = 0 AND CHARINDEX ('.' ,@VALEUR_X) = 0
								BEGIN
									IF(@VALEUR = @VALEUR_X)
									BEGIN
										SET @COMCM = @COMCM + 1
										SET @COMPM = @COMPM + 1
									END
									ELSE IF(@VALEUR != @VALEUR_X)
									BEGIN
										SET @DIFFCM = @DIFFCM + 1
										SET @DIFFPM = @DIFFPM + 1
									END				
								END
 
								-- VALEUR hétérozygotes et VALEUR_X non hétérozygotes
								ELSE IF CHARINDEX ('.',@VALEUR) > 0 AND CHARINDEX ('.' ,@VALEUR_X) = 0
								BEGIN		
									SET @DIFFCM = @DIFFCM + 1
 
									IF SUBSTRING(@VALEUR,1,CHARINDEX('.', @VALEUR)-1) = @VALEUR_X
										SET @COMPM = @COMPM + 1
									ELSE IF SUBSTRING(@VALEUR,CHARINDEX('.', @VALEUR)+1,15) = @VALEUR_X
										SET @COMPM = @COMPM + 1
									ELSE
										SET @DIFFPM = @DIFFPM + 1					
								END					
 
								-- VALEUR non hétérozygotes et VALEUR_X hétérozygotes
								ELSE IF CHARINDEX ('.',@VALEUR) = 0 AND CHARINDEX ('.' ,@VALEUR_X) > 0
								BEGIN
									SET @DIFFCM = @DIFFCM + 1
 
									IF SUBSTRING(@VALEUR_X,1,CHARINDEX('.', @VALEUR_X)-1) = @VALEUR
										SET @COMPM = @COMPM + 1
									ELSE IF SUBSTRING(@VALEUR_X,CHARINDEX('.', @VALEUR_X)+1,15) = @VALEUR
										SET @COMPM = @COMPM + 1
									ELSE
										SET @DIFFPM = @DIFFPM + 1					
								END
 
								-- VALEUR hétérozygotes et VALEUR_X hétérozygotes
								ELSE IF CHARINDEX ('.',@VALEUR) > 0 AND CHARINDEX ('.' ,@VALEUR_X) > 0
								BEGIN
									IF @VALEUR = @VALEUR_X	
										SET @COMCM = @COMCM + 1
									ELSE
										SET @DIFFCM = @DIFFCM + 1
 
									IF SUBSTRING(@VALEUR_X,1,CHARINDEX('.', @VALEUR)-1) = SUBSTRING(@VALEUR,1,CHARINDEX('.', @VALEUR)-1)
										SET @COMPM = @COMPM + 1
									ELSE IF SUBSTRING(@VALEUR_X,CHARINDEX('.', @VALEUR)+1,15) = SUBSTRING(@VALEUR,CHARINDEX('.', @VALEUR)+1,15)
										SET @COMPM = @COMPM + 1
									IF SUBSTRING(@VALEUR_X,1,CHARINDEX('.', @VALEUR)-1) = SUBSTRING(@VALEUR,CHARINDEX('.', @VALEUR)+1,15)
										SET @COMPM = @COMPM + 1
									ELSE IF SUBSTRING(@VALEUR_X,CHARINDEX('.', @VALEUR)+1,15) = SUBSTRING(@VALEUR,1,CHARINDEX('.', @VALEUR)-1)
										SET @COMPM = @COMPM + 1
									ELSE
										SET @DIFFPM = @DIFFPM + 1				
								END	
 
								-- Calculs des résultats et update table						
								SET @C1 = (SELECT COMMUN_CM FROM SYS_GENLABO WHERE INDIVIDU_1 = @CODE_EXP AND INDIVIDU_2 = @CODE_EXP_X)
								IF @C1 IS NULL
									SET @C1 = @COMCM
								ELSE
								SET @C1 = @C1 + @COMCM	
 
								SET @C2 = (SELECT COMMUN_PM FROM SYS_GENLABO WHERE INDIVIDU_1 = @CODE_EXP AND INDIVIDU_2 = @CODE_EXP_X)
								IF @C2 IS NULL
									SET @C2 = @COMPM
								ELSE
								SET @C2 = @C2 + @COMPM
 
								SET @D1 = (SELECT DIFFERENT_CM FROM SYS_GENLABO WHERE INDIVIDU_1 = @CODE_EXP AND INDIVIDU_2 = @CODE_EXP_X)
								IF @D1 IS NULL
									SET @D1 = @DIFFCM
								ELSE
								SET @D1 = @D1 + @DIFFCM						
 
								SET @D2 = (SELECT DIFFERENT_PM FROM SYS_GENLABO WHERE INDIVIDU_1 = @CODE_EXP AND INDIVIDU_2 = @CODE_EXP_X)
								IF @D1 IS NULL
									SET @D2 = @DIFFPM
								ELSE
								SET @D2 = @D2 + @DIFFPM		
 
								SET @DCM = ((@C1/(@C1+@D1))*100)
								SET @DPM = ((@C2/(@C2+@D2))*100)
								SET @DIST = (@DCM + @DPM) / 2			
 
								-- Update nombre d'hétérozygote pour VALEUR dans la paire
								UPDATE SYS_GENLABO SET MOYENNE = @DIST, DISTANCE_CM = @DCM, DISTANCE_PM = @DPM, COMMUN_CM = @C1, COMMUN_PM = @C1, DIFFERENT_PM = @D1, DIFFERENT_CM = @D1 WHERE INDIVIDU_1 = @CODE_EXP AND INDIVIDU_2 = @CODE_EXP_X
							END
						------------------------------------						
 
					------------------------------------------------------------------------------
					------------------------------------------------------------------------------
					------------------------------------------------------------------------------
 
				END
 
				-- Récupération de l'enregistrement suivant de la requete dans variabe CURSEUR_X
				FETCH NEXT FROM CURSEUR_X INTO @CODE_EXP_X, @MARQUEUR_X, @VALEUR_X
			-- Fin
			END
		------------------------------------------------------------------------------
 
		------------------------------------------------------------------------------
		-- Fermeture et liberation mémoire pour CURSEUR_X
			-- Fermeture de CURSEUR_X
			CLOSE CURSEUR_X
			-- Liberation mémoire
			DEALLOCATE CURSEUR_X 
		------------------------------------------------------------------------------
 
		-- Récupération de l'enregistrement suivant de la requete dans variabe CURSEUR
		FETCH NEXT FROM CURSEUR INTO @CODE_EXP, @MARQUEUR, @VALEUR
	-- Fin
	END
 
	------------------------------------------------------------------------------
	-- Fermeture et liberation mémoire pour CURSEUR
		-- Fermeture de CURSEUR
		CLOSE CURSEUR
		-- Liberation mémoire
		DEALLOCATE CURSEUR
	------------------------------------------------------------------------------
 
------------------------------------------------------------------------------

Je cherche des idées, une route à suivre, des conseils ...

Cordialement

[SQLpro]

Voici la solution sans procédure en une seule requête :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
CREATE DATABASE GENOME;
GO
 
USE GENOME;
GO
 
CREATE TABLE GENETIC
(CODE_FICHE INT, 
 MARQUEUR INT, 
 VALEUR VARCHAR(32)
 PRIMARY KEY (CODE_FICHE, MARQUEUR))
GO

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
INSERT INTO GENETIC
VALUES (2, 1022, '144#156'),
(2, 1025, '179#192'),
(2, 1028, '169#177'),
(4, 1031, '88#93.94#98'),
(4, 1022, '181#185'),
(4, 1046, '219#227'),
(4, 1028, '169#177'),
(4, 105, '.'),
(10, 1056, '122#131'),
(10, 1046, '307#314'),
(20, 1064, '194#208'),
(20, 1031, '88#93.94#98');

Code :

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

1
2
3
4
5
6
7
8
SELECT G1.CODE_FICHE, G2.CODE_FICHE AS CODE_FICHE2, 
       COUNT(*) AS NOMBRE
FROM   GENETIC AS G1
       INNER JOIN GENETIC AS G2
             ON G1.CODE_FICHE < G2.CODE_FICHE
                AND G1.MARQUEUR = G2.MARQUEUR
                AND G1.VALEUR = G2.VALEUR
GROUP  BY G1.CODE_FICHE, G2.CODE_FICHE;

--> sans index lent !

--> Avec index bien placé :

Code :

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

1
2
CREATE INDEX X ON GENETIC (MARQUEUR, VALEUR, CODE_FICHE);
GO

-- requête rapide :

Code :

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

1
2
3
4
5
6
7
8
SELECT G1.CODE_FICHE, G2.CODE_FICHE AS CODE_FICHE2, 
       COUNT(*) AS NOMBRE
FROM   GENETIC AS G1
       INNER JOIN GENETIC AS G2
             ON G1.CODE_FICHE < G2.CODE_FICHE
                AND G1.MARQUEUR = G2.MARQUEUR
                AND G1.VALEUR = G2.VALEUR
GROUP  BY G1.CODE_FICHE, G2.CODE_FICHE;

A +

[JmL40]

Bonjour,

Merci maitenant cette requete me permet d'obtenir la totalité des marqueurs communs.

Maintenant, je souhaiterai rajouter une condition comparer les valeurs à, et générer de nouvelle colonnes à savoir :

Si G1.VALEUR ou G2.VALEUR contiennent un ' . ' alors comparer gauche et droite G1.VALEUR avec gauche et droite de G2.VALEUR et placer le résultat de la comparaison dans une nouvelle colonnes.

Je ne sais pas si je me fait comprendre !

Cordialement

[SQLpro]

Dans ce cas vous avez mal modélisé votre base. Vous auriez du faire deux colonnes. Vous n'avez pas respecté la première forme normal qui dit que toute colonne doit contenir une données atomique, c'est à dire INSÉCABLE (sens latin du texte).
Le viol de la première forme normale implique fatalement des problèmes de performances.
On peut cependant contourner ce problème en rajoutant des colonnes calculées indexées à votre table, de la façon suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
ALTER TABLE GENETIC 
  ADD VALEUR1 AS CASE 
          WHEN CHARINDEX('.', VALEUR) = 0 THEN VALEUR
          ELSE SUBSTRING(VALEUR, 1, CHARINDEX('.', VALEUR) -1)
       END  
  PERSISTED;
 
ALTER TABLE GENETIC 
  ADD VALEUR2 AS CASE 
          WHEN CHARINDEX('.', VALEUR) = 0 THEN ''
          ELSE SUBSTRING(VALEUR, CHARINDEX('.', VALEUR) +1, LEN(VALEUR) - CHARINDEX('.', VALEUR))
       END 
  PERSISTED;

Puis indexer :

Code :

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

1
2
3
CREATE INDEX X1 ON GENETIC (MARQUEUR, VALEUR1, CODE_FICHE);
 
CREATE INDEX X2 ON GENETIC (MARQUEUR, VALEUR2, CODE_FICHE);

La requête étant :

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
SELECT CODE_FICHE, CODE_FICHE2, SUM(NOMBRE) AS NOMBRE2
FROM
(
SELECT G1.CODE_FICHE, G2.CODE_FICHE AS CODE_FICHE2, 
       COUNT(*) AS NOMBRE
FROM   GENETIC AS G1
       INNER JOIN GENETIC AS G2
             ON G1.CODE_FICHE < G2.CODE_FICHE
                AND G1.MARQUEUR = G2.MARQUEUR
                AND G1.VALEUR = G2.VALEUR1
GROUP  BY G1.CODE_FICHE, G2.CODE_FICHE
UNION ALL
SELECT G1.CODE_FICHE, G2.CODE_FICHE AS CODE_FICHE2, 
       COUNT(*) AS NOMBRE
FROM   GENETIC AS G1
       INNER JOIN GENETIC AS G2
             ON G1.CODE_FICHE < G2.CODE_FICHE
                AND G1.MARQUEUR = G2.MARQUEUR
                AND G1.VALEUR = G2.VALEUR2
GROUP  BY G1.CODE_FICHE, G2.CODE_FICHE)
AS T
GROUP BY CODE_FICHE, CODE_FICHE2

A +