Discussion :

[xian21]

Bonjour j'essaye d'optimiser la requete suivante

Code :

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

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 :

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

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 :

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

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

[pacmann]

Salut !

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

Code :

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

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 :

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

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 :

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

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

[Waldar]

Je dirai aussi que le code suivant :

Code :

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

CASE numrue when '' THEN '' ELSE numrue END

est identique à numrue.

[pacmann]

Lol, bien joué Waldar !
(ça m'avait même pas choqué d'écrire ça )

[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é.

[xian21]

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

[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 :

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

((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 :

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

( 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???

[xian21]

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 :

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

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 :

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

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 :

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

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 :

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

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 :

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

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