Discussion :

[fredericlb]

Bonjour à tous,

j'ai un problème SQL sur un tri de réponses à un questionnaire pour lequel j'ai beaucoup de mal à trouver une solution.
Ces questionnaires sont tous les mêmes, mais dépendent d'un sujet (par exemple : Vos avis sur l'objet A, Vos avis sur l'objet B).
J'ai une table A ("Libelle") qui contient l'ensemble des réponses possibles (les réponses sont prédéfinies).
J'ai une table B ("Id", "Valeur", "Groupe") qui contient l'ensemble des réponses données possibles, où le groupe est le sujet du questionnaire correspondant à cette réponse.
Mon but est d'avoir une vue C ("Libelle", "Groupe", "Quantité") où Quantité est le nombre de réponses pour un libellé et un groupe telle que l'on ait par exemple :
("Très satisfaisant","ObjetA",5)
("Très mécontent","ObjetB",0)

La requête est très simple si l'on ne souhaite pas avoir les réponses qui n'apparaissent pas (Où quantité = 0). Or, j'ai quelques problèmes lorsque je veux le nombre de réponses pour toutes les possibilités.

J'ai tout d'abord réussi quelque chose avec des unions (Ensemble des réponses telles que (Quantité != 0) UNION Ensemble des réponses possibles avec (Quantité = 0) MINUS/EXCEPT Ensemble des réponses existantes).
Mais bien entendu c'était très lent.
La solution que j'ai actuellement est :
On fait A x (SELECT distinct Groupe from B) pour avoir un produit cartésien et on a dont l'ensemble des réponses possibles, puis on fait un LEFT JOIN avec B et on compte sur B.Groupe (puisque si B.Groupe est NULL, le couple (Réponse, Sujet) n'a jamais été donné).
Ca marche très bien, mais c'est toujours trop lent et je suis à court d'idées... Quelqu'un connaitrait il un meilleur moyen ? (SGBD : Postgresql)

Voici la requête en question :

Code :

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

1
2
3
4
5
6
7
 
select a.label, G.groupe, count(b.groupe) as qte
from   a
         cross join (select distinct groupe from b) as G
         left join b 
             on a.libelle = b.valeur and b.groupe = G.groupe
group by G.groupe, a.libelle;

Requêtes de création de table :

Code :

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

1
2
3
 
create table a
    ("Libelle" VARCHAR);

Code :

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

1
2
3
4
5
 
create table b
    ("Id" INT, 
     "Valeur" VARCHAR,
     "Groupe" INT);

[SQLpro]

Un group by ne sert à rien, si ce n'est à pourrir les performances lorsqu'aucune opération d'agrégation (SUM, AVG, MAX...) n'apparait dans une requête

enuiste pour solutionner votre problème vous devez utiliser un produit cartésien (CROSS JOIN) entre la table des question, celle de réponses et incorporer la table des réponses données en sous requête dans la clause from.

Come vous n'avez pas respecter la charte de postage, je ne puis vous en dire plus : http://www.developpez.net/forums/a69...gage-sql-lire/

A +

[fredericlb]

Un group by ne sert à rien, si ce n'est à pourrir les performances lorsqu'aucune opération d'agrégation (SUM, AVG, MAX...) n'apparait dans une requête

enuiste pour solutionner votre problème vous devez utiliser un produit cartésien (CROSS JOIN) entre la table des question, celle de réponses et incorporer la table des réponses données en sous requête dans la clause from.

Come vous n'avez pas respecter la charte de postage, je ne puis vous en dire plus : http://www.developpez.net/forums/a69...gage-sql-lire/

A +

Il y a une agrégation puisqu'il y a un COUNT dans la requête.
Et j'utilise déjà un CROSS JOIN
J'ai corrigé de façon à respecter votre charte de postage.

[estofilo]

A l'énoncé il semble qu'une simple jointure externe pourrait faire l'affaire mais des confusions font qu'il est difficile de se prononcer:
1) la table A est censée avoir une seule colonne nommée Libelle mais la requête proposée
utilise d'autres colonnes a.id et a.label
2) dans la table B, "Group" est censé être de type INT mais dans la vue C la colonne correspondante a comme valeurs d'exemples "ObjetA", "ObjetB".

Par ailleurs il est impossible de répondre sur une question de lenteur sans avoir d'idée ni de volume, ni du temps d'exécution réel versus le temps espéré, ni de la présence ou non d'index.

[fredericlb]

A l'énoncé il semble qu'une simple jointure externe pourrait faire l'affaire mais des confusions font qu'il est difficile de se prononcer:
1) la table A est censée avoir une seule colonne nommée Libelle mais la requête proposée
utilise d'autres colonnes a.id et a.label
2) dans la table B, "Group" est censé être de type INT mais dans la vue C la colonne correspondante a comme valeurs d'exemples "ObjetA", "ObjetB".

Par ailleurs il est impossible de répondre sur une question de lenteur sans avoir d'idée ni de volume, ni du temps d'exécution réel versus le temps espéré, ni de la présence ou non d'index.

C'est corrigé, désolé mais c'est juste que j'ai simplifié le problème, les tables sont plus compliquées en fait, mais c'est le problème du count() = 0 que je voulais surtout souligner.
Groupe est effectivement de type INT, mais j'ai mis ObjetA, ObjetB pour être un peu plus clair quand à la signification de ce champ. Groupe contient l'identifiant de ces objets.

Dans le cas réel, la table A possède une dizaine d'enregistrement, B environ 300 000. Le temps d'exécution de la requête frôle les 30 secondes, or j'aurai aimé qu'il soit inférieur à une dizaine de secondes si possible.
Il n'y a aucun champ indexé, sachant que je n'ai de toute façon pas la possibilité de modifier la structure des tables.

[Waldar]

Il n'y a aucun champ indexé, sachant que je n'ai de toute façon pas la possibilité de modifier la structure des tables.

Vous pouvez heureusement ajouter un index sans modifier la structure des tables, c'est un autre type d'objet.
Faites un index sur B ( groupe, libelle).

Après celà dépend également de la façon dont vous utilisez votre vue.
Si vous ne faites que ressortir tous les éléments, l'index effectivement n'aura qu'une utilité relative.

[estofilo]

J'imagine qu'il y a beaucoup moins de groupes distincts que de lignes dans B.
Il est étonnant qu'il n'y ait pas une table des groupes, qui serait utilisable en jointure à la place du

Code :

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

select distinct groupe from b

, qui est coûteux puisqu'il nécessite de parcourir intégralement la table B, en plus de l'autre parcours intégral pour faire le GROUP BY.

Quoiqu'il en soit, le temps d'exécution de 30 secondes parait considérable pour 300000 entrées dans B. Est-ce que les tables ont bien étés analysées? Peut-être que l'optimiseur choisit un plan particulièrement mauvais s'il n'a pas de stats sur A et B.

A propos du double parcours, si WITH est supporté (postgresql >=8.4), la requête est peut-être optimisable en mettant 2 sous-requêtes en with: une qui regroupe B en faisant le group by "groupe", "label" et ramène le count(*), et l'autre qui ramène les valeurs distinctes de groupe sur cette première sous-requête au lieu de la table B. Ensuite combiner avec le CROSS JOIN dans la requête principale.