Optimisation requete sql

Version imprimable

Bonjour j'essaye d'optimiser la requete suivante

Code:

SELECT id_pres AS id, (SELECT nomrue FROM rue WHERE id_rue = idrue ) AS nomrue,LOWER(nom) AS nom, ((CASE WHEN numrue != '' THEN numrue ELSE '' END) || ' ' || (SELECT nomrue FROM rue WHERE id_rue = idrue )) AS rue , (SELECT nomoperation FROM operation WHERE id_operation = idop) AS nomoperation, ( CASE WHEN etat=2 THEN 'En cours' WHEN etat=3 THEN 'Réalisé' ELSE 'En attente' END ) AS etat, dateprestation, (SELECT nomville FROM ville WHERE id_ville = idville ) AS ville FROM prestation WHERE id_pres != 0

Mon explain me donne ceci (les tables ne contiennent pas encore beaucoup de donnees)
Code:

1 2 3 4 5 6 7 8 9 10 11 "Seq Scan on prestation (cost=0.00..6968.50 rows=269 width=154)" " Filter: (id_pres <> 0)" " SubPlan" " -> Seq Scan on ville (cost=0.00..1.04 rows=1 width=32)" " Filter: (id_ville = $5)" " -> Index Scan using pk_operation on operation (cost=0.00..8.27 rows=1 width=32)" " Index Cond: (id_nontrait = $3)" " -> Index Scan using pk_rue on rue (cost=0.00..8.27 rows=1 width=21)" " Index Cond: (id_rue = $0)" " -> Index Scan using pk_rue on rue (cost=0.00..8.27 rows=1 width=21)" " Index Cond: (id_rue = $0)"
Je me rends bien compte que je lance deux fois la meme requete

Code:

SELECT nomrue FROM rue WHERE id_rue = idrue

(vu que je recupere tout dans un datatable ca m'evitait de faire un traitement dans le code)

C'est un peu couillon de faire ca. Donc je voulais savoir si en sql c'etait possible de l'ameliorer?
Je suis bien sur preneur d'autre piste pour ameliorer ce genre de requete.

Merci

Salut !

Tout d'abord, on va mettre en forme ta requête, juste pour la lisibilité...
Code:

1 2 3 4 5 6 7 8 9 SELECT id_pres AS id, (SELECT nomrue FROM rue WHERE id_rue = idrue ) AS nomrue,LOWER(nom) AS nom, ((CASE WHEN numrue != '' THEN numrue ELSE '' END) || ' ' || (SELECT nomrue FROM rue WHERE id_rue = idrue )) AS rue , (SELECT nomoperation FROM operation WHERE id_operation = idop) AS nomoperation, ( CASE WHEN etat=2 THEN 'En cours' WHEN etat=3 THEN 'Réalisé' ELSE 'En attente' END ) AS etat, dateprestation, (SELECT nomville FROM ville WHERE id_ville = idville ) AS ville FROM prestation WHERE id_pres != 0
En suite, un principe de base : quand tu veux retrouver sur une ligne plusieurs informations provenant de tables différentes, tu fais plutôt des jointures que des sous-requêtes corrélées qui renvoient le résultat.
Je ne te réécris pas toutes la requête, mais juste pour la rue :
Code:

1 2 3 4 SELECT nomrue, CASE numrue when '' THEN '' ELSE numrue END || ' ' || nomrue FROM prestation a JOIN rue b ON id_rue = idrue
Comme tu le vois, quand tu fais une jointure, tu récupères tous les champs de A et tous les champs de B sur la même ligne. Après, tu peux réutiliser autant que tu le souhaite ses champs.
Ainsi, ça te permet de ne pas faire plusieurs fois la même requête pour avoir nomrue.

Bien entendu, tu peux faire la jointure entre plusieurs tables :
Code:

1 2 3 4 5 6 7 SELECT ... FROM tableA a JOIN tableB b ON a.ref = b.id JOIN tableC c ON a.ref2 = c.id WHERE ...
Cela dit, je suppose que tu es un peu débutant sur SQL... donc il faut que tu saches que la jointure est la notion de base qui te permet de faire le lien entre les différentes données de ton système.
Je te conseille quand même de te trouver des tutos (notament sur ce site) pour avoir de bonnes bases en SQL ;)

12/02/2009, 14h30
Waldar

Je dirai aussi que le code suivant :

Code:

CASE numrue when '' THEN '' ELSE numrue END

est identique à numrue.

Salut merci pour la reponse,

Bon j'ai juste oublie de spécifier un petit truc c'est que j'ai fait des sous requetes corrolees parce que je savais que ces requetes ne me renvoyait qu'un seul resultat.

Code:

SELECT nomrue FROM rue WHERE id_rue = idrue

Me renverra qu'un seul nom de rue (je fais une recherche sur l'id de la rue).

Bon j'ai quand meme teste avec des jointures et d'apres l'explain faire cette requete avec des jointures est beaucoup plus lourd en terme de traitement:

(j'ai remis en forme la requete ;) )
Sans jointure ca donne ca
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 EXPLAIN SELECT id_pres AS id, ( SELECT nomrue FROM rue WHERE id_rue = idrue ) AS nomrue ,LOWER(nom) AS nom, ((CASE WHEN numrue != '' THEN numrue ELSE '' END) || ' ' || (SELECT nomrue FROM rue WHERE id_rue = idrue )) AS rue , ( SELECT nomoperation FROM operation WHERE id_operation = idop ) AS nomoperation, ( CASE WHEN etat=2 THEN 'En cours' WHEN etat=3 THEN 'Réalisé' ELSE 'En attente' END ) AS etat, dateprestation, ( SELECT nomville FROM ville WHERE id_ville = P.idville ) AS ville FROM prestation P WHERE id_pres != 0
et le explain
Code:

1 2 3 4 5 6 7 8 9 10 11 "Seq Scan on prestation p (cost=0.00..6968.50 rows=269 width=154)" " Filter: (id_pres <> 0)" " SubPlan" " -> Seq Scan on ville (cost=0.00..1.04 rows=1 width=32)" " Filter: (id_ville = $5)" " -> Index Scan using pk_operation on operation (cost=0.00..8.27 rows=1 width=32)" " Index Cond: (id_oper = $3)" " -> Index Scan using pk_rue on rue (cost=0.00..8.27 rows=1 width=21)" " Index Cond: (id_rue = $0)" " -> Index Scan using pk_rue on rue (cost=0.00..8.27 rows=1 width=21)" " Index Cond: (id_rue = $0)"
et avec jointure pour les rues ca donne ca
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 EXPLAIN SELECT id_pres AS id, nomrue ,LOWER(nom) AS nom, ((CASE WHEN numrue != '' THEN numrue ELSE '' END) || ' ' || nomrue) , ( SELECT nomoperation FROM operation WHERE id_operation = idop ) AS nomoperation, ( CASE WHEN etat=2 THEN 'En cours' WHEN etat=3 THEN 'Réalisé' ELSE 'En attente' END ) AS etat, dateprestation, ( SELECT nomville FROM ville WHERE id_ville = P.idville ) AS ville FROM prestation P JOIN rue ON id_rue = idrue WHERE id_pres != 0
Code:

1 2 3 4 5 6 7 8 9 10 11 "Hash Join (cost=25.37..2549.55 rows=269 width=171)" " Hash Cond: (p.idrue = rue.id_rue)" " -> Seq Scan on prestation p (cost=0.00..13.38 rows=269 width=154)" " Filter: (id_pres <> 0)" " -> Hash (cost=15.72..15.72 rows=772 width=25)" " -> Seq Scan on rue (cost=0.00..15.72 rows=772 width=25)" " SubPlan" " -> Seq Scan on ville (cost=0.00..1.04 rows=1 width=32)" " Filter: (id_ville = $2)" " -> Index Scan using pk_operation on operation (cost=0.00..8.27 rows=1 width=32)" " Index Cond: (id_oper = $0)"
Donc sans jointure j'ai un cout de 0,...
et avec j'ai un cout de 25.

(bon apres je suis pas sur de comprendre ce que me raconte le explain.)

Donc si je me trompe pas d'apres l'explain ma requete est beaucoup plus legere en terme de traitement malgre le fait que je fasse deux fois la meme requete.

Avec un resultat me renvoyant 1287 prestat (j'ai 1288 prestat dans la bdd et 800 rues pour l'instant mais a terme les chiffres seront bien plus gros)
Avec jointure je mets 280 ms
Sans jointure je mets 265 ms
et sans jointure en faisant sauter un des deux select non de rue je tombe a 250 ms

12/02/2009, 14h50
pacmann

Lol, bien joué Waldar !
(ça m'avait même pas choqué d'écrire ça :aie:)
12/02/2009, 14h51
pacmann

Et qu'est ce qui te dérange dans l'exec plan ?

Par ailleurs, tu peux appliquer le principe de jointure sur les autres tables (operation, ...) afin de ne plus avoir aucun select imbriqué.
12/02/2009, 15h02
xian21

Justement dans le cas present les select imbriques sont nettement mieux que les jointures.
La jointure allourdit tout la requete d'apres l'explain.
Ca me parait pas logique mais c'est le resultat que j'obtiens. (difference de cout de 25)

(Je sais d'avance que mes select sur operation, rue et ville ne me renverront qu'un seul et unique champs)

Sinon

Code:

((CASE WHEN numrue != '' THEN numrue ELSE '' END) || ' ' || (SELECT nomrue FROM rue WHERE id_rue = idrue )) AS rue

Ne me renvoye pas le meme resultat que

Code:

( numrue || ' ' || (SELECT nomrue FROM rue WHERE id_rue = idrue )) AS rue

dans le deuxieme cas si le numrue est vide du coup le resultat rue est vide meme si j'ai bien un nom de rue. ET la j'ai pas compris pourquoi
Peut etre un bug de postgres???
12/02/2009, 15h04
xian21

Citation:

Envoyé par pacmann

Et qu'est ce qui te dérange dans l'exec plan ?

exec plan = explain?

Ben si c'est bien ca je sais pas encore analyser exactement le resultat qu'il me renvoye

Essayez la requête suivante :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT
    P.id_pres AS id,  
    P.nomrue,
    lower(P.nom) AS nom,
    coalesce(P.numrue, ' ') || ' ' || P.nomrue as adresse,
    O.nomoperation,
    CASE P.etat
      WHEN 2 THEN 'En cours'
      WHEN 3 THEN 'Réalisé' 
      ELSE 'En attente'
    END AS etat,
    P.dateprestation,
    V.nomville AS ville
FROM
    prestation P
    INNER JOIN rue R ON R.id_rue = P.idrue
    INNER JOIN operation O ON O.id_operation = P.idop
    INNER JOIN ville V ON V.id_ville = P.idville 
WHERE
    P.id_pres <> 0

12/02/2009, 15h27
pacmann

Citation:

"(cost=0.00..6968.50 rows=269 width=154)"

Je ne sais pas non plus lire les plans d'exécution Postgres...
Mais je suppose que c'est une fourchette qui t'es indiquée.
Donc la deuxième fourchette est meilleure que la première.

La raison ?
Tu vois apparaître dans le plan d'exécution des HASH join.
En fait, lorsque tu fais des jointures, ton SGBD aurait pu choisir exactement le même plan que sans les jointures. Par contre, tu lui as permis d'envisager et de préférer une solution plus efficaces que l'itération simple : le HASH join.

Il faut en fait surtout que tu arrêtes de penser en itérations et en résultats imbriqués pour chaque ligne, mais en relations.

Ah okip, donc la ma requete est meilleurs parce que postgresql sait mieux optimiser ce genre de requete (hash join) que celle avec la jointure.
Par contre dans ce cas la c'est bizarre quand je lui propose une jointure qu'il n'aille pas de lui meme faire un hash join ...

Waldar merci.

J'ai teste mais c'est pas mieux :s c'est meme pire au niveau du cout desole
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 "Sort (cost=56.43..56.46 rows=13 width=163)" " Sort Key: p.id_pres" " -> Nested Loop (cost=1.07..56.19 rows=13 width=163)" " -> Nested Loop (cost=1.07..51.60 rows=13 width=146)" " -> Hash Join (cost=1.07..47.23 rows=13 width=118)" " Hash Cond: (p.idville = v.id_ville)" " -> Seq Scan on prestation p (cost=0.00..42.80 rows=863 width=90)" " Filter: (id_pres <> 0)" " -> Hash (cost=1.03..1.03 rows=3 width=36)" " -> Seq Scan on ville v (cost=0.00..1.03 rows=3 width=36)" " -> Index Scan using pk_operation on operation (cost=0.00..8.27 rows=1 width=32)" " Index Cond: (id_oper = $3)" " -> Index Scan using pk_rue on rue r (cost=0.00..0.33 rows=1 width=25)" " Index Cond: (r.id_rue = p.idrue)"
coalesce est mieux que case ?

12/02/2009, 18h11
SQLpro

Faire des jointure sur des littéraux est extrêmement couteux (en général cela correspond à 4 fois le cout d'une jointure d'entier par multiple de 4 octets. Par exemple pour un littéral d'au plus 16 octets, le coût est de 16 fois plus). C'est pourquoi la jointure avec clef de hachage est souvent plus rapide. Cela consiste à calculer un entier par rapport à la chaine puis la stocker dans une entrée de tableau correspondant à l'entier puis d'assurer la correspondance.
Mais le cout est estimé d'après les statistiques qu'il a en mémoire; Si vous disposez de peu de stats (par exemple si votre base est sous indexée) alors il peut se tromper !

A +
12/02/2009, 18h34
Waldar

Vous devriez apprendre qu'un coût plus bas ne signifie pas une requête plus rapide, ça reste du domaine de l'estimation !

Dans les deux cas les requêtes sont quasi instantanées (< 300 ms), donc les deux sont viables et vous pouvez utiliser celle que vous préférez.

Maintenant je vous invite à tester avec un peu plus de volumétrie, je pense que celle avec les jointures "classiques" sera plus performante, mais encore une fois il faudrait le vérifier, il y a tant de paramètres en jeu.

Coalesce est une fonction spécialement conçue pour gérer les valeurs nulles.
Je ne pense pas qu'en exécution on puisse constater de notables différences avec un case, mais en syntaxe c'est plus léger, surtout quand vous avez plus de deux arguments.
12/02/2009, 18h46
SQLpro

Sur la comparaison CASE / COALESEC, cela peut jouer car autant le CASE peut être optimisé autant pour le COALESCE, s'agissant d'une fonction, c'est moins jouable.

Par exemple sur SQL Server, le CASE est optimisé systématiquement.

A +
12/02/2009, 19h16
pacmann

Citation:

Envoyé par SQLpro

Faire des jointure sur des littéraux est extrêmement couteux (en général cela correspond à 4 fois le cout d'une jointure d'entier par multiple de 4 octets. Par exemple pour un littéral d'au plus 16 octets, le coût est de 16 fois plus). C'est pourquoi la jointure avec clef de hachage est souvent plus rapide. Cela consiste à calculer un entier par rapport à la chaine puis la stocker dans une entrée de tableau correspondant à l'entier puis d'assurer la correspondance.
Mais le cout est estimé d'après les statistiques qu'il a en mémoire; Si vous disposez de peu de stats (par exemple si votre base est sous indexée) alors il peut se tromper !

A +

SQLPro, si je ne me trompe pas, il n'y a pas que cet aspect de litteraux.
Pour moi, le plus important, est la présence d'un index, puis même en cas de présence de l'index, il y a sa performance (du moins sous Oracle) :
- Pas d'index : en nested loops, c'est un carnage. Deux méthodes alternatives : le merge sort ou le hash.
- Avec un index : on peut envisager le nested loops, mais lorsque la sélectivité de la driving table est faible, le nombre de lectures par index peut être couteux. C'est la qu'entre en jeu la notion de clustering factor : si les valeurs adjacentes d'un index ont une forte probabilité d'être regroupées dans des mêmes blocs, l'index est bon. Sinon, un nombre trop important de blocs sont lus... on peut préférer un index moins adapté, voire le FULL TABLE SCAN + une jointure par hash.

@xian : encore une fois, je ne sais pas lire les explain plan postgres... mais le coût global n'est il pas bien moins élevé ?

Citation:

cost=56.43..56.46
13/02/2009, 00h26
SQLpro

Citation:

SQLPro, si je ne me trompe pas, il n'y a pas que cet aspect de litteraux.

Pas, mais cela compte énormément. La différence est la complexité des tables de caractères et la gestion des collations (sensible ou non à la casse, aux accents) ce que le numérique n'a pas à faire...

A +
13/02/2009, 10h09
xian21

Pour l'explain postgresql je suis parti du principe que plus le chiffre est eleve plus le cout est important et qu'il faut donc avoir le chiffre le plus bas possible.
cost=56.43..56.46 > cost=0.00..6968.50

Sinon pour ma requete dans le cas present a chaque fois que j'ai un champs commencant par id c'est soit une clef primaire soit un clef etrangere de type int.

Waldar j'ai fait mes tests avec des tables de 2000 et 800 lignes mais effectivement faudrait que j'essaye de les faire grossir encore pour mes tests.

En tout cas merci pour vos explications.
13/02/2009, 10h29
pacmann

Citation:

cost=56.43..56.46 > cost=0.00..6968.50

Que veut dire 56.43..56.46 ?
Supposition : cout entre 56.43 et 56.46

Si c'est bien ça, on a :
56.43 > 0, certes
Mais on a surtout 56.46 < 6968.50 !

Non ?

[EDIT]
Tiens, parmi les grands conseils qu'on peut donner quand on ne connaît pas quelque chose : chercher !
Et on trouve :

Citation:

The most critical part of the display is the estimated statement execution cost, which is the planner's guess at how long it will take to run the statement (measured in units of disk page fetches). Actually two numbers are shown: the start-up time before the first row can be returned, and the total time to return all the rows

Et là, c'est plus convaincant ? ;)

http://www.postgresql.org/docs/8.1/s...l-explain.html
13/02/2009, 11h16
xian21

Yes beaucoup plus . (c'est ca de pas lire la doc :p )
En fait je regardais que le premier chiffre jusqu'a present.

Donc la solution de Waldar serait la plus performante :)