Discussion :

[rayahz]

Bonjour,

Je cherche à améliorer une requête. Voici quelques explications.

j'ai une table qui contient 3 colonnes :

• id : clé primaire
• pere : identifiant du père, peut être null
• mère : identifiant de la mère, peut être null

chaque ligne de la table indique pour une personne l'identifiant de son père et l'identifiant de sa mère. à partir de l'identifiant du père ou de la mère on peut retrouver les grands parents puisque il suffit de chercher dans la colonne id, l'identifiant du père et on obtient ainsi les grands parents.

je cherche à optimiser ma requête car ma table contient pas loin de 1 million d'entrées. ma requête doit me ramener l'identifiant de la personne, celui de la mère, du père, des grand parents paternels (gpp et gmp) et des grand parents maternels (gpm et gmm). j'en suis sûre que la requête peut être amélioré mais je ne vois pas comment.

Code :

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

1
2
3
4
5
SELECT t1.id, t1.mere as mere, t1.pere as pere, t2.mere as gmm, t2.pere as gpm, t3.mere as gmp, t3.pere as gpp
FROM   table t1, table t2, table t3
WHERE  t1.mere = t2.id
AND    t1.pere = t3.id
ORDER BY t1.id

toute aide est la bienvenue
merci par avance

[Arkhena]

Bonjour,

A part le fait que votre écriture de SQL est obsolète depuis 1992 (les conditions de jointure doivent être dans la clause FROM et non dans la clause WHERE), je ne vois pas trop comment il serait possible de faire mieux...
Peut-être n'avez vous pas besoin du tri ? Cela réduira certainement votre temps de traitement si vous supprimez la clause ORDER BY.

Ensuite, avez-vous regardé votre plan d'exécution ? Avez-vous un index sur la colonne id ?

Cordialement,

Arkhena

[Rei Ichido]

Idéalement pour cette requête il faut un index sur (id, pere, mere).

[tatayo]

Bonjour,
En complément de la réponse de Arkhena, la requête en contient aucune restriction, donc quoi qu'il en soit le moteur doit parcourir toute la table pour trouver les enregistrements qui vont bien.
En effet (à priori) la seule façon de savoir si une ligne "pointe" vers un père et une mère est de lire cette ligne.
Accessoirement, la requête telle qu'écrite ici ne renverra pas les lignes "sans parents".

Tatayo.

[SergioMaster]

Bonjour,

de quel SGBD parle t-on ? pour moi ça sent la requête récursive a plein nez mais peut être est-ce parce que j'ai le nez dedans en ce moment

[rayahz]

A part le fait que votre écriture de SQL est obsolète depuis 1992 (les conditions de jointure doivent être dans la clause FROM et non dans la clause WHERE), je ne vois pas trop comment il serait possible de faire mieux...
Peut-être n'avez vous pas besoin du tri ? Cela réduira certainement votre temps de traitement si vous supprimez la clause ORDER BY.

Ensuite, avez-vous regardé votre plan d'exécution ? Avez-vous un index sur la colonne id ?

je peux supprimer l'order by il n'est pas nécessaire. par contre une jointure dans le from ça me dit rien. pour précision je ne connais que le sql de base je suis ingé c++. le sql n'est pas trop ma spécialité.

Idéalement pour cette requête il faut un index sur (id, pere, mere).

il y a un index uniquement sur id

[rayahz]

Bonjour,

de quel SGBD parle t-on ? pour moi ça sent la requête récursive a plein nez mais peut être est-ce parce que j'ai le nez dedans en ce moment

DB2

[SergioMaster]

Bon, je ne connais pas DB2 (j'ai lâché IBM avant) mais un petit tour me confirme qu'il y a possibilité de récursivité requête récursive, ce lien mérite une lecture à mon avis
quant aux jointures le cours de SQLPro est là pour ça

[rayahz]

Bon, je ne connais pas DB2 (j'ai lâché IBM avant) mais un petit tour me confirme qu'il y a possibilité de récursivité requête récursive, ce lien mérite une lecture à mon avis
quant aux jointures le cours de SQLPro est là pour ça

merci du coup j'ai amélioré la requête en faisant des left join et en virant le order by

[Rei Ichido]

Bon, je ne connais pas DB2 (j'ai lâché IBM avant) mais un petit tour me confirme qu'il y a possibilité de récursivité requête récursive, ce lien mérite une lecture à mon avis
quant aux jointures le cours de SQLPro est là pour ça

Vu qu'il n'y a que 3 niveaux, fixes, pas la peine de faire du récursif. La requête proposée, si on la ré-écrit proprement avec des INNER JOIN, devrait être plus performante qu'une requête récursive.

Sur l'amélioration des perf, créer l'index sur (id, pere, mere) devrait faire une différence plus que sensible - bien sûr, savoir s'il faut le faire dépend de l'intérêt de gagne de la performance sur la requête proposée par rapport aux temps de modification de la table ...

[François DORIN]

Vu qu'il n'y a que 3 niveaux, fixes, pas la peine de faire du récursif. La requête proposée, si on la ré-écrit proprement avec des INNER JOIN, devrait être plus performante qu'une requête récursive.

Pas INNER JOIN, mais LEFT JOIN. Les colonnes "père" et "mère" peuvent être nulles

Sur l'amélioration des perf, créer l'index sur (id, pere, mere) devrait faire une différence plus que sensible - bien sûr, savoir s'il faut le faire dépend de l'intérêt de gagne de la performance sur la requête proposée par rapport aux temps de modification de la table ...

Pour être plus précis, il faut un index sur la colonne id et qui inclus les colonnes "père" et "mère". Ainsi, l'index est suffisant en lui-même pour la requête et il ne sera pas utile d'aller interroger la table. Maintenant, je ne connais pas DB2, aussi ce que je vais dire concerne SQLServer et peut éventuellement s'appliquer ici. Par défaut, SQLServer lorsqu'il y a une clé primaire, créé un index cluster sur la clé primaire (ie. les données de la table sont physiquement trié par rapport à la clé primaire). Si ici la table ne contient que 3 colonnes (id, pere, mere), alors créé un index contenant "pere" et "mere" sera inutile puisque la table le fera déjà ! Par contre, si la table contient d'autres colonnes, alors oui cela peut être utile.

Enfin, si les accès en lecture sont véritablement à privilégier, une autre possibilité (si c'est supporté par DB2), c'est d'utiliser une vue matérialisée (terminologie ORACLE) ou indexée (terminologie SQL Server). Le principe est d'avoir une vue dont le résultat est persisté sur disque. Du coup, l'interrogation de la vue consiste juste à lire une table et non à la calculer. Et une vue indexée peut être... indexée !

Autre possibilité sinon : simuler les vues indexées via des triggers qui viendraient mettre à jour automatiquement une table contenant 7 colonnes (id, id_pere, id_mere, id_gpm, id_gmm, id_gpp, id_gmm). Ainsi, la requête consisterait juste à récupérer la bonne ligne...

[Arkhena]

merci du coup j'ai amélioré la requête en faisant des left join et en virant le order by

Les LEFT JOIN sont plus consommatrices (teurs?) que les INNER JOIN... Après, ça ne fait pas la même chose. Il faut vraiment partir de votre besoin.

par contre une jointure dans le from ça me dit rien.

Ce n'est pas une question que ça vous plaise ou non, c'est simplement que le SQL s'écrit comme ça.
Votre manière de faire est obsolète et pose des problèmes de maintenabilité du code tout en masquant des erreurs manifestes de développeurs (produit cartésiens par exemple)... Cette ancienne écriture devrait être bannie depuis 1992 (la préhistoire!) et perdure cependant pour des raisons inexpliquées...

En tant que DBA, quand on me demande d'optimiser une requête qui n'est pas écrite suivant les préceptes de base du code (mots clés en majuscules, retours chariots, indentation, respect de la norme de 1992...), j'ai l'impression que le développeur me demande de retrouver son tee-shirt préféré dans sa chambre en bordel...

Bonne journée,

Arkhena

[rayahz]

Ce n'est pas une question que ça vous plaise ou non, c'est simplement que le SQL s'écrit comme ça.
Votre manière de faire est obsolète et pose des problèmes de maintenabilité du code tout en masquant des erreurs manifestes de développeurs (produit cartésiens par exemple)... Cette ancienne écriture devrait être bannie depuis 1992 (la préhistoire!) et perdure cependant pour des raisons inexpliquées...

quand je disais que ça ne me dit rien je voulais dire que je n'en ai jamais entendue parler donc je ne sais pas comment ça fonctionne.

Les LEFT JOIN sont plus consommatrices (teurs?) que les INNER JOIN... Après, ça ne fait pas la même chose. Il faut vraiment partir de votre besoin.

ensuite j'ai quand meme besoin de remonter les lignes null alors ma premiere requete ne fonctionnait pas vu qu'elle me remontait uniquement les lignes non null d'où le left join

En tant que DBA, quand on me demande d'optimiser une requête qui n'est pas écrite suivant les préceptes de base du code (mots clés en majuscules, retours chariots, indentation, respect de la norme de 1992...), j'ai l'impression que le développeur me demande de retrouver son tee-shirt préféré dans sa chambre en bordel...

quels sont les preceptes que je n'ai pas respecté ? je pose la question car je voudrai apprendre de mes erreurs merci

[Arkhena]

Bonjour,

Voici votre requête d'origine:

SELECT t1.id, t1.mere as mere, t1.pere as pere, t2.mere as gmm, t2.pere as gpm, t3.mere as gmp, t3.pere as gpp
FROM table t1, table t2, table t3
WHERE t1.mere = t2.id
AND t1.pere = t3.id
ORDER BY t1.id

Voici comment elle aurait du être écrite pour être conforme à la norme SQL:

Code :

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

1
2
3
4
5
SELECT t1.id, t1.mere as mere, t1.pere as pere, t2.mere as gmm, t2.pere as gpm, t3.mere as gmp, t3.pere as gpp
FROM   table t1
  INNER JOIN table t2 ON t1.mere = t2.id
  INNER JOIN table t3 ON  t1.pere = t3.id
ORDER BY t1.id

De cette manière on voit en un seul coup d'oeil:
- Que vous n'avez que des jointures internes
- Que vous n'avez pas de produit cartésien
- Que vous n'avez pas de filtre sur vos données

C'est vrai que sur votre "petite" requête, ça semble purement cosmétique, mais sur les grosses requêtes, ça ne l'est pas et autant prendre les bonnes habitudes dès le début.

Bonne journée,

Arkhena