Discussion :

[Saluzatous]

Bonjour à tous !

J'aurais besoin d'aide en ce qui concerne des requêtes SQL, ça fait un bout de temps que je n'en ai pas fait et je suis un peu rouillé.

Mon problème est le suivant :
À l'aide d'une requête SQL, il faut que je sélectionne toutes les recettes sauf celles dont 2 utilisateurs ou plus sont allergiques. J'ai réussi à trouver une requête qui pourrait correspondre, mais j'ai besoin de savoir si elle est valide.
De plus, elle contient 2 requêtes imbriquées, ce qui risque d'augmenter le temps de réponse (et il faudrait que je puisse l'utiliser plusieurs fois). Est-ce que quelqu'un aurait une réponse moins coûteuse ?

Recettes (id_recette, nom)
Presente (id_recette, id_allergene)
Allergenes (id_allergene, nom)
Est_allergique_a (id_allergene, id_utilisateur)
Utilisateur (id_utilisateur, nom, prenom)

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
-- Sélectionner les recettes ne contenant pas d'allergènes susceptibles de gêner 2 convives ou plus 
SELECT id_recette
FROM Recettes
WHERE id_recettes <> (SELECT R.id_recette -- Sélectionner les recettes possédant ces allergènes
		       FROM Recettes AS R
		         JOIN Presente AS P 
		         ON P.id_recette = R.id_recette
		       WHERE P.id_allergene IN (SELECT id_allergene -- Sélectionner les allergènes gênant plus de 2 convives
				                FROM Est_allergique_a
				                WHERE id_utilisateur IN (id_user1, id_user2, ...)
				                GROUP BY id_allergene
				                HAVING COUNT(id_allergene) >= 2))

Pour tous ceux qui accepterons de me donner un coup de main : merci d'avance!

[escartefigue]

Hello

La première requête utilise un prédicat différent non indexable par définition, donc si volume il y a, gros problème de performances.
Je pense que tu peux remplacer par un where not exists qui lui pourra être indexable.

La première requête imbriquée doit préciser le type de jointure (inner ou outer).
Non pas que ça ne fonctionne pas, mais le natural join est très fragile en cas d'évolution du DDL (risque de grosse perte de perfs).

La 2eme requête imbriquée peut être remplacée par un where exists qui sera plus optimisé.

Je n'ai pas vérifié (je te laisse le soin de le faire car je ne peux pas rester) que toutes les colonnes de jointures sont bien de même format.

[aieeeuuuuu]

Bonjour

Que donne ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT rec.id
FROM	recettes rec
LEFT JOIN	presente p
		INNER JOIN allergenes al
			ON		al.id = p.id_allergene
		INNER JOIN Est_allergique_a eaa
			ON		eaa.id_allergene = al.id
	ON	p.id_recette = rec.id
GROUP BY rec.id 
HAVING COUNT(DISTINCT eaa.id_utilisateur) < 2

[Saluzatous]

Merci pour vos réponses!

Escartefigue : le problème avec EXISTS, c'est que ça me renvoie toute la table dès lors que la requête imbriquée retourne un résultat. Du coup, j'ai préféré garder les requêtes sans utiliser cette fonction. Est-ce que c'est parce que je l'utilise mal ?

Voici à quoi ressemble ma requête avec les EXIST :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT id
FROM Recettes
WHERE NOT EXISTS (SELECT id_recette
		FROM Presente
		WHERE EXISTS (SELECT id_allergene
				FROM Est_allergique_a
				WHERE id_utilisateur IN (1, 3)
				GROUP BY id_allergene
				HAVING COUNT(id_allergene) >= 2)
		GROUP BY id_recette)

J'ai cependant supprimé les jointures (je me suis rendu compte qu'elles ne servaient pas à grand-chose) :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT id
FROM Recettes
WHERE id NOT IN (SELECT id_recette
		FROM Presente
		WHERE id_allergene IN (SELECT id-allergene
					FROM Est_allergique_a
					WHERE id_utilisateur IN (1, 4)
					GROUP BY id_allergene
					HAVING COUNT(id_allergene) >= 2)
		GROUP BY id_recette)

Aieeeuuuuu : Ça marche nickel. J'ai ajouté la condition AND eaa.id_utilisateur IN (id_user1, id_user2, ...) pour n'avoir que les allergies des utilisateurs concernés. Cependant, le temps de réponse est supérieur à la requête précédente, est-ce à cause des jointures ? Pour tester, j'ai utilisé des tables peu remplies, c'est peut-être à cause de ça aussi.

Requête complète :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT rec.id_recette
FROM	Recettes rec
	LEFT JOIN	Presente p
		INNER JOIN Allergenes al
			ON al.id_allergene = p.id_allergene
		INNER JOIN Est_allergique_a eaa
			ON (eaa.id_allergene = al.id_allergene AND eaa.id_utilisateur IN (1, 4))
	ON	p.id_recette = rec.id_recette
GROUP BY rec.id_recette 
HAVING COUNT(DISTINCT eaa.id_utilisateur) < 2

En tout cas, merci à vous

[escartefigue]

Merci pour vos réponses!

Escartefigue : le problème avec EXISTS, c'est que ça me renvoie toute la table dès lors que la requête imbriquée retourne un résultat. Du coup, j'ai préféré garder les requêtes sans utiliser cette fonction. Est-ce que c'est parce que je l'utilise mal ?

En tout cas, merci à vous

C'est anormal, les requêtes where exists ou where (...) in doivent produire le même résultat, mais exists est généralement plus rapide puisqu'il évite de construire le sous-ensemble produit par la requête in.
La différence est parfois non mesurable si le sous-ensemble est très petit