Discussion :

[tri_yann]

Bonjour,

Je suis devant une impasse même si ca parait simple de premier abord. Je souhaite comparer le contenu de 2 tables comportant des articles composées de "composants". Je cherche à connaître les articles de @t1 qui ont exactement la même composition que les articles de @t2.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
DECLARE @t1 table (Article1 varchar(255), Composant1 varchar(255))
DECLARE @t2 table (Article2 varchar(255), Composant2 varchar(255))
 
Insert into @t1 values ('Boulon 1', 'Vis')
Insert into @t1 values ('Boulon 1', 'Ecrou')
Insert into @t1 values ('Tringle à rideaux 1', 'Barre')
Insert into @t1 values ('Tringle à rideaux 1', 'Support mural')
 
Insert into @t2 values ('Boulon 2', 'Vis')
Insert into @t2 values ('Boulon 2', 'Ecrou')
Insert into @t2 values ('Boulon 2', 'Rondelle')
Insert into @t2 values ('Tringle à rideaux 2', 'Barre')
Insert into @t2 values ('Tringle à rideaux 2', 'Support mural')

Dans cet exemple, comment faire pour savoir que "Tringle à rideaux 1" a la même composition que "Tringle à rideaux 2". Ce n'est pas le cas pour les boulons 1 & 2.

Merci de votre aide

[Kropernic]

Il y a très certainement moyen d'optimiser le tout mais le noob que je suis est arrivé à qqch avec ceci :
(j'ai ajouté quelques valeurs)

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 @t1 TABLE (Article1 varchar(255), Composant1 varchar(255))
DECLARE @t2 TABLE (Article2 varchar(255), Composant2 varchar(255))
 
INSERT INTO @t1 VALUES ('Boulon 1', 'Vis')
INSERT INTO @t1 VALUES ('Boulon 1', 'Ecrou')
INSERT INTO @t1 VALUES ('Tringle à rideaux 1', 'Barre')
INSERT INTO @t1 VALUES ('Tringle à rideaux 1', 'Support mural')
INSERT INTO @t1 VALUES ('Truc 1', 'comp 1.0')
INSERT INTO @t1 VALUES ('Truc 1', 'comp 1.1')
INSERT INTO @t1 VALUES ('Truc 1', 'comp 1.2')
INSERT INTO @t1 VALUES ('Brol 1', 'comp 2.0')
 
INSERT INTO @t2 VALUES ('Boulon 2', 'Vis')
INSERT INTO @t2 VALUES ('Boulon 2', 'Ecrou')
INSERT INTO @t2 VALUES ('Boulon 2', 'Rondelle')
INSERT INTO @t2 VALUES ('Tringle à rideaux 2', 'Barre')
INSERT INTO @t2 VALUES ('Tringle à rideaux 2', 'Support mural')
INSERT INTO @t2 VALUES ('Truc 2', 'comp 1.0')
INSERT INTO @t2 VALUES ('Truc 2', 'comp 1.1')
INSERT INTO @t2 VALUES ('Truc 2', 'comp 1.3')
INSERT INTO @t2 VALUES ('Brol 2', 'comp 2.0')
 
select 
		a.article1, b.article2
from 
		@t1 as a
			inner join @t2 as b
				on a.composant1 = b.composant2
			inner join (select article1, count(*) as 'count'
						from @t1
						group by article1) c
				on a.article1 = c.article1
			inner join (select article2, count(*) as 'count'
						from @t2
						group by article2) d
				on b.article2 = d.article2
 
group by a.article1, b.article2, c.count, d.count
having c.count = count(*) and d.count=count(*)

Après, à toi d'adapter les champs de la clause select pour avoir ce que tu veux.

En espérant avoir aider,

Kropernic.

P.S. : Je serais assez curieux de savoir commet qqn comme sqlpro ou elsuket, pour ce citer qu'eux deux (sorry pour les autres, faut bien faire un choix), aurait fait cela.

[punkoff]

Salut,

une approche un peu différente, mais je ne sais pas si c'est mieux :

Code :

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

1
2
3
4
5
6
7
8
9
10
 
with tmp as (
select distinct a.article1, b.article2 
from tb1 a
full outer join tb2 b on a.composant1 = b.composant2)
 
select * 
from tmp a 
where not exists (select null from tmp b where a.article1 = b.article1 and b.article2 is null)
and not exists (select null from tmp b where a.article2 = b.article2 and b.article1 is null);

edit : c'est nul en fait ca gère pas le cas initial

[Kropernic]

Ah cette fonction exists !! Faudrait vraiment que je me rappelle qu'elle existe.

Pour ma part, je pense que cette approche est plus performante (mais il faudrait analyser les plans de requête respectif pour en être certain). De prime abord, il y a tellement moins de comptage qui sont fait que cela ne peut être que bénéfique je dirais.

Non ?

Kropernic.

[Sergejack]

Juste pour le plaisir d'utiliser INTERSECT

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
 
SELECT
	TB1.Article1
	, TB2.Article2
FROM (
	SELECT 
		TB1.Article1
		, COUNT(*) AS Cnt
	FROM @t1 AS TB1
	GROUP BY TB1.Article1
) AS TB1
INNER JOIN (
	SELECT 
		TB2.Article2
		, COUNT(*) AS Cnt
	FROM @t2 AS TB2
	GROUP BY TB2.Article2
) AS TB2 ON (
	TB2.Cnt = TB1.Cnt
)
CROSS APPLY (
	SELECT
		COUNT(*) AS Cnt
	FROM (
		SELECT
			T1.Composant1
		FROM @t1 AS T1
		WHERE T1.Article1 = TB1.Article1
		INTERSECT
		SELECT
			T2.Composant2
		FROM @t2 AS T2
		WHERE T2.Article2 = TB2.Article2
	) AS X
) AS X
WHERE X.Cnt = TB1.Cnt

Note: c'est peut-être aussi un poil plus performant

[tri_yann]

Merci à vous tous. Malheureusement, je n'ai pas la méthode miracle que j'espérais. Mes 2 tables comptent 100000 enr chacune pour 30000 articles. Dans toutes vos solutions, je passe par une combinaison de plus 3 500 000 enr. La comparaison des comptages prend donc 5 min. Ca marche mais c'est long. La plus performante est celle de Kropernic : 5min.
La solution de Punkoff, pourtant charmante, je ne l'ai pas laissée finir. arrivée à 2 000 000 d'enr, j'ai annulé puis 35 min de Rollback !!!

Malgré tout, j'ai appris de nouvelles méthodes et pour ca, je vous en remercie.

Pour info, je dois trouver une méthode pour attaquer 2 tables d'1 000 000 d'enr chacune (c'est la même table que je compare à elle même) en passant par une procédure stockée de préparation de 10sec. maxi pour pouvoir faire des select d'1 sec. maxi.

Cordialement

[punkoff]

question conne, cette table c'est quoi sa structure réelle ?

(car 1m d'enregistrement c'est pas beaucoup surtout qu'ici on devrait avoir juste une table d'association entre un article et un composant...donc comparaison d'ID de type integer)

[Sergejack]

La comparaison des comptages prend donc 5 min. Ca marche mais c'est long. La plus performante est celle de Kropernic : 5min.

Et la mienne prend combien de temps ?
Essayez-la aussi en rajoutant à la fin

Code :

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

1
2
 
OPTION (FORCE ORDER)

Puis laissez moi savoir ce que cela aura donné, si vous le voulez bien (sauf si ça prend tellement longtemps que vous préférez l'interrompre).

[tri_yann]

Punkoff,
La structure de la table est la suivante :
Id_veh int
Ref varchar(18)
Les 2 colonnes contiennent chacune 1 index non unique
Id_Veh = Article
Ref = Composant

[tri_yann]

SergeJack,
Ta requête :
- Sans "Option" : 12min
- Avec "Option" : 10min

Pour les temps, je me suis affranchi de l'affichage de tous les enregistrements en n'affichant qu'un count(*).

A quoi sert le OPTION (FORCE ORDER) ?

[punkoff]

une variante de la requête count, plus de consolidation en amont (mais bon l'étape qui coute est celle de recoupement des composant de toute manière) :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
with tmp  as (
select id_art, count(id_art) as cnt
from tb1
group by id_art)
 
select * 
from (
	select a.id_art as id_art1, b.id_art as id_art2, count(*) as cnt
	from tb1 a
	inner join tb1 b on a.id_comp = b.id_comp and a.id_art > b.id_art
	group by a.id_art, b.id_art) as a
inner join tmp b on a.id_art1 = b.id_art and a.cnt = b.cnt
inner join tmp c on a.id_art2 = c.id_art and a.cnt = c.cnt;

Par contre, que vient faire ici une ref en varchar dans cette table ?

typiquement cette table devrait avoir comme clef primaire le couple id_art, id_comp.

id_compclef étrangère sur une table de composant.


ou (si présence d'une clef autoincrementer) d'un index unique sur le couple.

Avec une modélisation correct, gagnez-vous du temps de traitement ?

[Sergejack]

SergeJack,
Ta requête :
- Sans "Option" : 12min
- Avec "Option" : 10min

Pour les temps, je me suis affranchi de l'affichage de tous les enregistrements en n'affichant qu'un count(*).

A quoi sert le OPTION (FORCE ORDER) ?

Merci pour l'info.

Pour la peine, voici un query qui mérite d'être tenté (je pense qu'il peut aller très vite) :

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
 
SELECT DISTINCT
	Article1
	, Article2
FROM (
	SELECT
		T1.Article1
		, T2.Article2
		, COUNT(*) OVER (PARTITION BY T1.Article1) AS NA1
		, COUNT(*) OVER (PARTITION BY T2.Article2) AS NA2
		, COUNT(*) OVER (PARTITION BY T1.Article1, T2.Article2) AS NAM
	FROM @t1 AS T1
	FULL JOIN @t2 AS T2 ON (
		T2.Composant2 = T1.Composant1
	)
) AS X
WHERE NA1 = NAM
AND NAM = NA2

Quant'à FORCE ORDER, ça permet d'obliger SQL Server à faire un plan où la séquence des opérations est dans le même ordre que celui des jointures.

[punkoff]

Faire un count(*) over(PARTITION BY T1.Article1) avec une jointure va compter toutes les occurences d'un même article dans le résultat.

Du coup c'est pas comparable avec le reste.

(mais l'idée de la partition est à creusée)

[Sergejack]

Du coup c'est pas comparable avec le reste.

Le nombre de composants d'un article1 est égal au nombre de composants d'un article2 tous deux égaux au nombre de composants en commun.

Cela répond bien à la question.

[Kropernic]

Ca ne revient pas à faire ce que je propose au début mais avec des fonctions sophistiquées ?? (c'est une vraie question, j'suis un noob en sql)

Kropernic.

P.S. : Je n'en reviens pas que ce soit mon truc tout pourri qui est le plus rapide.

[punkoff]

Le nombre de composants d'un article1 est égal au nombre de composants d'un article2 tous deux égaux au nombre de composants en commun.

Cela répond bien à la question.

Sauf que vous n'arriverez jamais à avoir ce méchanisme avec une telle requête.

Prenons un exemple simple :

Code :

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

1
2
3
4
5
6
7
8
9
 
id_art    id_comp
------------------------
1           1
1           2
2           1
3           1
4           1
4           2

ici concernant l'article : 1
NA1 va être égale à 6
NAM va être égale à 2


Concernant l'article : 2
NA1 va être égale à 4
NAM va être égale à 1

[Sergejack]

Code :

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

1
2
3
4
5
6
7
8
9
 
id_art    id_comp
------------------------
1           1
1           2
2           1
3           1
4           1
4           2

ici concernant l'article : 1
NA1 va être égale à 6
NAM va être égale à 2


Concernant l'article : 2
NA1 va être égale à 4
NAM va être égale à 1

Heu... non.
On comprend dans votre exemple que 1, 2 appartiennent à T1 et 3, 4 appartiennent à T2.

ici concernant l'article : 1
NA1 va être égale à 2
NAM va être égale à 2 (voir article 4)
NA1 va être égale à 2 (voir article 4)


Concernant l'article : 2
NA1 va être égale à 1
NAM va être égale à 1 (voir article 3)
NA1 va être égale à 1 (voir article 3)

D'ailleurs Triyan nous dira ce que cette requête aura donné comme résultat

[tri_yann]

Bonjour Punkoff,
Bien joué, en faisant un count(*) pour m'affranchir du temps d'affichage, ca prend 4 sec. Pa contre, j'ai un doute sur le résultat. J'ai 2 fois moins de cross (1 500 000 enr), il faut que je contrôle si j'ai pas de manque.
Pour la structure de la table, je sais que ce n'est pas l'idéal le varchar, mais je ne choisis pas la structure et même si je la change pour mettre un Int, ca ne fera qu'alléger sensiblement. Sql Server est tout de même très efficace sur les comparaisons de chaînes.
Merci

[punkoff]

transformez le > en <>, c'était pour éviter les comparaisons dans les deux sens (2 fois plus de boulot).


@Serge : essayez votre requête avec un jeu de test un peu plus diversifiez vous comprendrez.

Concernant les tables T1 et T2 c'est en fait le même table cf le poste de tri_yann

Pour info, je dois trouver une méthode pour attaquer 2 tables d'1 000 000 d'enr chacune (c'est la même table que je compare à elle même) en passant par une procédure stockée de préparation de 10sec. maxi pour pouvoir faire des select d'1 sec. maxi.

[Sergejack]

@Serge : essayez votre requête avec un jeu de test un peu plus diversifiez vous comprendrez.

Concernant les tables T1 et T2 c'est en fait le même table cf le poste de tri_yann

L'erreur ne vient pas de mon jeu de test, l'erreur vient du fait que T1 et T2 sont la même table, ce que j'ignorais (j'ai dû oublié la remarque entre parenthèse de tri_yann à la lecture de son jeu de test).

Je pense que votre solution est la bonne.