Optimiser les jointures dans des requêtes [Résolu]

[klereth]

Bonjour!

je dois réaliser un générateur de SQL qui exécutera des requêtes sur Postgres. On m'a demandé d'optimiser au plus les requêtes, et bien sûr comme ce n'est pas moi qui crée la base je ne peux optimiser qu'au niveau des SELECT.
Grâce à mes petits papiers préférés j'ai pu avoir plein d'astuces (merci beaucoup SQLPro ). J'ai cherché aussi si l'ordre des conditions comptait, pour poser les conditions les plus restrictives en premier, et je me suis rendue compte que ce n'était pas la peine puisque l'optimiseur de Postgres s'occupe tout seul d'exécuter dans le bon ordre. (Arrêtez-moi si je me trompe )
Je me demandais alors s'il n'y avait pas d'autre choses à faire pour optimiser encore plus (je pense par exemple à l'utilisation au mieux des index, sur lesquels je ne suis pas très calée ) ?

Merci d'avance!

Klereth

[Quentin]

Par acquis de conscience, je te donne le lien vers le cours de SQLPro sur les index : http://sql.developpez.com/sqlaz/ddl/?page=partie2#L10

En effet, l'ordre des conditions importe peu, le SGBD fait sa propre analyse derrière pour déterminer dans quel ordre il va les traiter.

D'une façon générale, tu as la commande EXPLAIN qui te permet de voir l'efficacité du plan de tes requêtes. Ou en utilisant EXPLAIN EXECUTE, la requête est non seulement analysée, mais elle est également lancée et son temps d'exécution effectif est donné à la fin.
Pour plus d'informations sur EXPLAIN, voir la documentation

Pour que PostgreSQL puisse choisir le bon plan, il convient aussi de nettoyer et de mettre à jour régulièrement les tables et leurs statistiques, avec les commandes VACUUM et ANALYZE (il est possible de combiner les deux).
Commande VACUUM
Commande ANALYZE

Voilà pour les conseils d'ordre général. Après, cela devient plus spécifique, donc pour pouvoir t'aider plus, il nous faut plus de détails sur ce que tu dois faire.

[Grubshka]

Si j'ai bien compris tu ne t'occupes que des requêtes, ce n'est pas toi qui crée la base. Tu ne pourras alors pas lancer des vacuum analyze (enfin peut-être que oui, mais l'admin de la bd risque de gueuler )

[klereth]

Merci pour ta réponse, et d'avoir confirmé mes doutes
En fait mon rôle c'est juste d'optimiser au niveau requête, et l'optimisation des tables, les index etc. ne me concernent pas vraiment.

Quand même, au cas où, je précise mon boulot :
je dois faire un outil qui permet à un utilisateur de sélectionner des colonnes de tables et de poser des conditions sur des colonnes, puis à partir de cet ensemble de colonnes et conditions, génère une requête SQL la plus optimisée possible.

Voilà, donc si vous avez des trucs en plus à me donner, je suis preneuse!

Au fait,

Si j'ai bien compris tu ne t'occupes que des requêtes, ce n'est pas toi qui crée la base. Tu ne pourras alors pas lancer des vacuum analyze (enfin peut-être que oui, mais l'admin de la bd risque de gueuler Razz

T'as tout compris mon lapin

[Grubshka]

Un outil un peu comme Business Object

[klereth]

Oui c'est ça, j'essaye de pomper une partie de Business Objetcs

Un petit exemple des questions que je me pose :
est-ce la peine d'écrire toutes les jointures dans la clause JOIN plutôt que de les écrire dans le WHERE avec toutes les autres conditions ? Niveau plan, ça a l'air d'être pareil... Pour la propreté du code, c'est toujours mieux,mais je me demandais si ça optimisait ou pas du tout...
Donc en gros : est-ce que ca change quelque chose de mettre les jointures dans JOIN plutôt que dans WHERE ?
Merci!

[Quentin]

Personnellement, je n'ai jamais noté de différence de performances. Mais, question de lisibilité, j'ai pris l'habitude de séparer les jointures des "filtres d'extraction"

[loacast]

En fait les jointures sont très lourdes en SQL, si tu peux te débrouiller pour faire pluisieurs requêtes plutot qu'une tu gagneras en performances.

[Quentin]

Envoyé par loacast

En fait les jointures sont très lourdes en SQL, si tu peux te débrouiller pour faire pluisieurs requêtes plutot qu'une tu gagneras en performances.

Pardon ? J'ai toujours entendu le contraire, pour ma part...
Dans les petits papiers de SQLPro, il est conseillé d'éviter les requêtes imbriquées quand on peut faire la même chose avec une jointure.

Et si l'on parle de séparer une requête en plusieurs requêtes séparées, cela n'est pas forcément une bonne idée, puisque chaque nouvelle requête demande une nouvelle analyse du planificateur, et peut-être aussi des répétitions dans les parcours de table.

[loacast]

je n'ai pas été précis mais je vais y remédier

Primo je n'ai jamais parlé de requêtes imbriqués mais de plusieurs requêtes ...

Ensuite cela ne s'applique que dans un seul cas en fait. Imaginons une base imaginaire (certes). Nous avons un livre qui possède plusieurs pages et une page qui n'appartient qu'à un seul livre.

Je précise avant tout que le sujet choisi est un peu beaucoup bitique mais bon on fera avec ...

table LIVRE :

id_livre
lib_livre
...

table PAGE :

id_page
lib_page
#id_livre

Et bien si tu recherches le libelle d'un livre via un libelle de page, la requête suivante :

select lib_livre
from livre, page
where livre.id_livre = page.id_livre
and lib_page = 'cette fameuse page !!'

est moins performante que les deux requetes suivantes (on part tout de même du principe que le php est là pour transférer les données :

select id_livre
from page
where lib_page = 'cette fameuse page !!'

select lib_livre
from livre
where id_livre = $id_livre

Tu pourras dire ce que tu veux mais crois moi si je te dis que c'est largement plus performant. Bon il est évident que ce n'est pas le genre de choses à faire habituellement mais il voulait de la performance alors je lui en donne !

[Quentin]

Bah, je te crois

Juste une précision, tout de même : puisque tu parles de PHP, j'imagine que tu as dû relever cette différence de performance sur une base MySQL, non ?

Si c'est le cas, je pense que la lenteur de jointures est dû au fait que MySQL ne gère pas l'intégrité référentielle, ce qui peut effectivement ralentir sensiblement les jointures entre tables. Dans le cas de PostgreSQL (qui gère l'intégrité référentielle), les champs appartenant à des clefs étrangères sont indexés, d'où une plus grande rapidite dans les jointures.

[loacast]

Ben justement, mysql est bien plus rapide que postgresSQL mais c'est surtout du au fait qu'il ne gère que peu de contraintes d'intégrité (mysql étant excellent pour débuter mais une fois que l'on découvre posgis de postgresql, on oublis vite mysql (très utile pour le svg !!) . Qu'importe la base de données, faire une recherche séparé sur chaque table nécessite moins de calcul car moins de tri. Par contre refaire le traitement pour une même table à chaque fois, ça c'est pas performant.

[Quentin]

Ah, les mythes et légendes
Le concept de rapidité d'une base de données est vraiment relatif aux tests qu'on veut bien lui faire faire.
MySQL a été conçu comme un SGBDR simple et performant pour de petites bases de données (en termes de tables et d'accès concurrents). Dans une utilisation de ce type, MySQL est effectivement plus rapide que PostgreSQL ou Oracle. A l'inverse, une grosse de données dont le volume de données se chiffre en Go, où plusieurs accès simultanés sont faits... MySQL s'écroule complètement.
MySQL et PostgreSQL n'ont pas la même utilisation, ne visent pas le même public, donc ça n'a pas grand intérêt de les comparer dans l'absolu.

Dans mon cas, avec la base sur laquelle je travaille, si je découpe mes requêtes pour éviter les jointures, les temps de réponses vont considérablement augmenter (je parle en connaissance de cause). Maintenant, il faut dire qu'avec une base de 200 tables et dont les requêtes tournent à minimum 5/6 jointures, la configuration n'est pas la même

Faut tester