Discussion :

[lucas52]

Je me demande comment optimiser une requête avec plusieurs tables père/fils utilisant 2 critères de recherche, l'un sur la table père et l'autre critère sur la dernière table fils.
Exemple:
soit les tables Ecole, Classe, Eleve, Note.
Une Ecole a plusieurs Classes, une Classe plusieurs Eleves, un Eleve plusieurs Notes et la table Note une colonne date_controle.
Comment optimiser une requête qui recherche toutes les notes avec les critères Ecole.id=? et Note.date_controle > ?

Code :

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

1
2
3
4
 
SELECT n.id, n.date_controle, n.note, n.id_eleve
FROM Note n JOIN Eleve e ON e.id=n.id_eleve JOIN Classe c ON c.id=e.id_classe JOIN Ecole ec ON ec.id=c.id_ecole
WHERE ec.id=? AND n.date_controle > ?

Avec les clés primaires, on a (1 école * nb classes * nb élèves * nb notes) puis un filtre pour date_controle.
Avec un index sur date_controle, on a (les notes qui respectent la contrainte de date de TOUTES les écoles) puis un filtre sur l'école.

Comment faire mieux ?

merci.

[tatayo]

Bonjour,
Si toutes les clés étrangères sont indexées (ce qui me semble la moindre des choses), ainsi que les id (clé primaires ?), et que tu as un index sur Note.date_controle, je ne vois pas pourquoi la requête ne serait pas optimisée...
Quel est le SGBD ? Quel est le plan d'execution ?

Tatayo.

PS. je suppose que "n.date_controle > ?" n'est pas à prendre stricto-sensus, mais que tu remplaces "?" par une vraie valeur.

[lucas52]

Effectivement, la requête en elle même ne peut pas être optimisée.
Toutefois, le nombre d'enregistrements manipulés sur la table Note est très important par rapport au nombre d'enregistrements réellement retournés.
En effet, le sgbd n'a que 2 possibilités pour résoudre cette requête.
Si on prend 1000 Ecoles avec une moyenne de 30 Classes par Ecole, 30 Elèves par classe, 50 Notes par Elève et 2 notes par semaine par élève.

Choix 1 de résolution par le sgbd, il utilise l'id de L'école.
A partir de l'id de l'école, il retrouve les classes de cette école, les élèves de ces classes et enfin les notes de ces élèves. Soit en moyenne 1 école * 30 classes * 30 élèves * 50 notes = 45000 enregistrements de la table Note à parcourir pour retrouver ceux qui respectent la contrainte de date et au final retourner 1800 enregistrements (soit les notes de la dernière semaine).

Choix 2 de résolution par le sgbd, il utilise l'index sur date_controle.
Par cet index, il retrouve toutes les notes de la dernière semaine. Soit en moyenne 1000 écoles * 30 classes * 30 élèves * 2 notes = 1800000 enregistrements de la table Note qu'il doit maintenant rattacher à la table Ecole pour retrouver les enregistrements de l'école recherchée (toujours 1800).

Dans cet exemple, le sgbd choisira bien-sûr le choix 1 de résolution.

Question: Comment faire mieux.

idée 1, utiliser une vue. Si je dois passer la requête sur plusieurs Ecoles, créer une vue intermédiaire avec uniquement les notes de la dernière semaine. Mais je ne suis pas sur que ce soit plus efficace.

idée 2, dénormaliser. Si j'ajoute une colonne id_ecole dans la table Note, je peux ajouter un index sur les colonnes (id_ecole, date_controle) et améliorer considérablement les perfs de ma requête. Mais si je dénormalise ma base pour optimiser à chaque fois les requêtes, je risque de finir avec une base difficile à maintenir non?

[tatayo]

Si tu as un index qui comprend l'id de l'élève et la date, le moteur se basera sur cet index au lieu de parcourir toutes les notes d'un élève.
Tu peux le vérifier en regardant le plan d'execution.

Tatayo.

[lucas52]

Et bien non, le plan d’exécution montre qu'il n'utilise pas l'index (id_eleve, date_controle). Il prend toujours le choix 1 comme plan d'exécution. C'est peut-être à cause de l'intervalle de temps comme 2ème critère. Cela l'oblige à parcourir trop d'éléments de l'index.

[skuatamad]

Peux tu fournir les précisions demandées par tatayo :

Si toutes les clés étrangères sont indexées (ce qui me semble la moindre des choses), ainsi que les id (clé primaires ?)

Quel est le SGBD ? Quel est le plan d'execution ?

Dèjà tu n'as pas besoin de la table ecole et filtrer directement sur c.id_ecole
Après comme tu ne sélectionnes que des lignes de la table note, tu peux envisager une sous-requête IN ou EXISTS.

[Waldar]

Parler d'optimisation sans évoquer le SGBD ni ne donner aucun temps de référence n'a pas de sens.

[Rei Ichido]

Et bien non, le plan d’exécution montre qu'il n'utilise pas l'index (id_eleve, date_controle). Il prend toujours le choix 1 comme plan d'exécution. C'est peut-être à cause de l'intervalle de temps comme 2ème critère. Cela l'oblige à parcourir trop d'éléments de l'index.

Il faudrait voir les plans d'exécution (et connaitre le SGBD) pour aller plus loin. Cependant avec un index (date_controle,id_eleve) on peut s'attendre à ce qu'il fasse un scan range puis un hash join d'index.