"optimiser" une reqûete UNION ALL

**dams78** · 04/05/2010, 11h19

Bonjour,
Je viens d'hériter d'une requête que je dois "optimiser" enfin plutôt voire ce qu'on peut faire avec.

La requête se décompose en deux parties, un peu comme ceci :

Code :

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

1
2
3
4
5
6
7
select champ1, champ2, champ3, champ4
from table1, table2, table3
where blabla
union all
select champ1, 0 as champ2, champ3, 1 as champ4
from table1, table2
where blabla

Donc vous l'aurez compris l'intérêt (pour moi) du union all est que dans le deuxième select on travaille avec une table en moins et le champ de cette table est remplacé par une constante, parce qu'on en a quand même besoin dans le retour.

Donc a votre avis est ce que je pourrai faire sauter le union all et un peu raccourcir cette requête? Parce que là ça dépasse un peu mes connaissances de sql.

Merci de votre aide.

**Snipah** · 04/05/2010, 13h16

Bonjour,

Sans la vraie requête et une description de ce qu'elle est censée fournir comme infos, il sera difficile de vous aider.
On parle d'optimisation, donc pourquoi pas la structure et la volumétrie de vos tables, les index, etc...
Précisez au passage votre SGBD, ça peut aussi servir.

**SQLpro** · 04/05/2010, 13h29

1 repondez à spinah en respectant les règles de ce forum
http://www.developpez.net/forums/a69...gage-sql-lire/
2 transformez le UNION ALL en UNION ci cela est sémantiquement valable

A +

**dams78** · 04/05/2010, 14h15

Envoyé par Snipah

Bonjour,

Sans la vraie requête et une description de ce qu'elle est censée fournir comme infos, il sera difficile de vous aider.
On parle d'optimisation, donc pourquoi pas la structure et la volumétrie de vos tables, les index, etc...
Précisez au passage votre SGBD, ça peut aussi servir.

Pour la description de ce que doit faire la requête, je suis un peu dans le flou, on m'a refilé le bébé et débrouilles toi...
Sinon on est entrain de migrer notre système d'une base DB2 à une base Oracle.
Je n'ai accès qu'à la dev et une sorte de pré-prod... donc je ne pourrai pas me prononcé sur les index en revanche au niveau de la volumétrie on doit se situer dans les 100.000 lignes.

Je vais essayer de vous copier la requête en question.
Merci pour votre aide.

**dams78** · 04/05/2010, 14h39

Voila la fameuse requête

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
 
SELECT T3.LIBELLE, T1.NFACTURE AS NUMERO, T2.DATE_PAIEMENT, T1.DATE_DEBUT, T1.DATE_FIN, T2.PUBLIE, T3.TYPE_ABO
	FROM TABLE1 T1, TABLE2 T2, TABLE3 T3, TABLE4 T4
		WHERE T1.REFI=T3.REFI 
		AND T1.NFACTURE=T2.NFACTURE 
		AND T1.APPLICATION='APPLI' 
		AND T3.TYPE_ABO=1 
		AND T2.STATUS_PAIEMENT=0 
		AND (T4.ID=502 AND T1.USERID=T4.USERID)
UNION ALL
SELECT T3.LIBELLE, 0 AS NUMERO, T1.DATE_DEBUT AS DATE_PAIEMENT, T1.DATE_DEBUT, T1.DATE_FIN, 1 AS PUBLIE, T3.TYPE_ABO
	FROM TABLE1 T1, TABLE3 T3, TABLE4 T4
		WHERE T1.REFI=T3.REFI 
		AND T1.APPLICATION='APPLI' 
		AND (T3.TYPE_ABO=2 OR T3.TYPE_ABO=5) 
		AND (T4.ID=502 AND T1.USERID=T4.USERID)

**SQLpro** · 04/05/2010, 15h03

Optimiser sans savoir quels sont les index et donc ne pas pouvoir en rajouter ne sert strictement à rien. En effet on optimise pas (ou rarement) une requête. On optimise la structure physique des données pour obtenir de meilleures performance sur des requêtes.

Seule amélioration possible, en supposant qu'il n'y a pas de facture 0 (T1.NFACTURE) : supprimer le ALL de l'UNION.

Voir cours d'optimisation :
http://sqlpro.developpez.com/cours/optimiser/
http://sqlpro.developpez.com/cours/quoi-indexer/
http://sqlpro.developpez.com/optimisation/

A +

**dams78** · 04/05/2010, 15h25

En fait c'est pas vraiment de l'optimisation (d'où mes guillemets) mais plutôt une amélioration, en fait je crois que cette requête à fait peur et qu'on nous à demandé de la retravailler.

En tout cas merci pour tes liens, je vais quand même regarder ce que ça donne.
Et j'ai enlevé aussi le ALL et pour l'instant ça me donne la même chose.

Merci

**Oishiiii** · 04/05/2010, 15h38

Bonjour,

J'ai toujours pensé que UNION ALL était plus performant que seulement UNION car (a priori) le SGBD n'a pas le travail supplémentaire de "trier" les lignes pour en supprimer les doublons.

Envoyé par SQLpro

transformez le UNION ALL en UNION ci cela est sémantiquement valable

Comment est-ce que cela s'explique ?

**Snipah** · 04/05/2010, 16h06

J'ai toujours pensé que UNION ALL était plus performant que seulement UNION car (a priori) le SGBD n'a pas le travail supplémentaire de "trier" les lignes pour en supprimer les doublons.

Idem. Qu'est ce qui justifierait d'enlever le ALL si on n'a pas de doublons?

Envoyé par dams78

En fait c'est pas vraiment de l'optimisation (d'où mes guillemets) mais plutôt une amélioration, en fait je crois que cette requête à fait peur et qu'on nous à demandé de la retravailler.

Vous pouvez toujours la réécrire en utilisant la syntaxe normalisée pour les jointures, peut être qu'elle leur fera moins peur

Sans connaitre les règles de fonctionnement, ce que représentent vos tables, un éventuel jeu d'essai, etc... on ne pourra pas vraiment vous trouver une requête équivalente.

**dams78** · 04/05/2010, 16h13

Le problème c'est que je vois pas comment dire de remplacer le numéro de facture dans le deuxième select par la constante 0?

**Snipah** · 04/05/2010, 16h33

Je parlais des jointures, pas du UNION ALL. Votre requête est strictement équivalente à celle ci, plus lisible :

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
 
SELECT T3.LIBELLE, T1.NFACTURE AS NUMERO, T2.DATE_PAIEMENT, T1.DATE_DEBUT, T1.DATE_FIN, T2.PUBLIE, T3.TYPE_ABO
FROM TABLE1 T1
JOIN TABLE2 T2 ON T1.NFACTURE=T2.NFACTURE
JOIN TABLE3 T3 ON T1.REFI=T3.REFI 
JOIN TABLE4 T4 ON T1.USERID=T4.USERID
WHERE T1.APPLICATION='APPLI' 
  AND T3.TYPE_ABO=1 
  AND T2.STATUS_PAIEMENT=0 
  AND T4.ID=502
UNION ALL
SELECT T3.LIBELLE, 0 AS NUMERO, T1.DATE_DEBUT AS DATE_PAIEMENT, T1.DATE_DEBUT, T1.DATE_FIN, 1 AS PUBLIE, T3.TYPE_ABO
FROM TABLE1 T1
JOIN TABLE3 T3 ON T1.REFI=T3.REFI
JOIN TABLE4 T4 ON T1.USERID=T4.USERID
WHERE T1.APPLICATION='APPLI' 
  AND (T3.TYPE_ABO=2 OR T3.TYPE_ABO=5) 
  AND T4.ID=502

Ensuite, qu'est ce qui justifie de ne pas joindre à la TABLE2 dans la seconde requête?
Si c'est juste que lorsque T3.TYPE_ABO est égal à 2 ou 5, on n'a pas de correspondance dans T2, alors cette requête avec un LEFT JOIN sur TABLE2 est peut être équivalente :

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
 
SELECT T3.LIBELLE, 
       coalesce(T2.NFACTURE,0) AS NUMERO, 
       coalesce(T2.DATE_PAIEMENT,T1.DATE_DEBUT) AS DATE_PAIEMENT, 
       T1.DATE_DEBUT, 
       T1.DATE_FIN, 
       coalesce(T2.PUBLIE,1) AS PUBLIE, 
       T3.TYPE_ABO
FROM TABLE1 T1
JOIN TABLE3 T3 ON T1.REFI=T3.REFI
JOIN TABLE4 T4 ON T1.USERID=T4.USERID
LEFT JOIN TABLE2 T2 ON T1.NFACTURE=T2.NFACTURE
WHERE T1.APPLICATION='APPLI' 
  AND T3.TYPE_ABO IN (1,2,5) 
  AND T2.STATUS_PAIEMENT=0 
  AND T4.ID=502

Si on a des correspondances dans T2 mais qu'on ne veut pas les utiliser lorsque T3.TYPE_ABO est égal à 2 ou 5, alors l'union restera sans doute la solution la plus propre.
Mais ça on ne peut pas le deviner.

**dams78** · 04/05/2010, 16h56

J'ai essayé ta requête mais elle me retourne rien, mais je vais continuer à creuser dans ce sens là.
Par contre pour répondre à ta question il n'y a effectivement pas de tuples dans la table 2 qui correspondent aux retours du deuxième select, en fait la table 2 contient les factures et si le type_abo est à 2 ou 5 il n'y a pas de factures.

Merci de ton aide

**SQLpro** · 05/05/2010, 09h53

Autant pour moi, j'ai effectivement dit une connerie !!!! Mieux vaut UNION ALL que UNION tout court ! Mea culpa !!!!!

A +

**Snipah** · 05/05/2010, 10h40

Envoyé par dams78

J'ai essayé ta requête mais elle me retourne rien, mais je vais continuer à creuser dans ce sens là.
Par contre pour répondre à ta question il n'y a effectivement pas de tuples dans la table 2 qui correspondent aux retours du deuxième select, en fait la table 2 contient les factures et si le type_abo est à 2 ou 5 il n'y a pas de factures.

Merci de ton aide

Oui, j'ai laissé dans la clause WHERE une condition sur une colonne de T2, du coup ça casse la jointure externe.
Essayez ceci :

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
SELECT T3.LIBELLE, 
       coalesce(T2.NFACTURE,0) AS NUMERO, 
       coalesce(T2.DATE_PAIEMENT,T1.DATE_DEBUT) AS DATE_PAIEMENT, 
       T1.DATE_DEBUT, 
       T1.DATE_FIN, 
       coalesce(T2.PUBLIE,1) AS PUBLIE, 
       T3.TYPE_ABO
FROM TABLE1 T1
JOIN TABLE3 T3 ON T1.REFI=T3.REFI
JOIN TABLE4 T4 ON T1.USERID=T4.USERID
LEFT JOIN TABLE2 T2 
     ON T1.NFACTURE=T2.NFACTURE
     AND T2.STATUS_PAIEMENT=0 
WHERE T1.APPLICATION='APPLI' 
  AND T3.TYPE_ABO IN (1,2,5) 
  AND T4.ID=502

**CinePhil** · 05/05/2010, 10h41

Envoyé par dams78

J'ai essayé ta requête mais elle me retourne rien, mais je vais continuer à creuser dans ce sens là.
Par contre pour répondre à ta question il n'y a effectivement pas de tuples dans la table 2 qui correspondent aux retours du deuxième select, en fait la table 2 contient les factures et si le type_abo est à 2 ou 5 il n'y a pas de factures.

Essaie celle-ci :

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
SELECT T3.LIBELLE, 
       coalesce(T2.NFACTURE,0) AS NUMERO, 
       coalesce(T2.DATE_PAIEMENT,T1.DATE_DEBUT) AS DATE_PAIEMENT, 
       T1.DATE_DEBUT, 
       T1.DATE_FIN, 
       coalesce(T2.PUBLIE,1) AS PUBLIE, 
       T3.TYPE_ABO
FROM TABLE1 T1
JOIN TABLE3 T3 ON T1.REFI = T3.REFI
JOIN TABLE4 T4 ON T1.USERID = T4.USERID
LEFT JOIN TABLE2 T2 ON T1.NFACTURE = T2.NFACTURE
WHERE T1.APPLICATION = 'APPLI' 
  AND T4.ID=502
  AND 
  (
    (
      T3.TYPE_ABO = 1
      AND T2.STATUS_PAIEMENT=0
    )
    OR T3.TYPE_ABO IN (2, 5)
  )

**dams78** · 05/05/2010, 14h04

Envoyé par CinePhil

Essaie celle-ci :

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
SELECT T3.LIBELLE, 
       coalesce(T2.NFACTURE,0) AS NUMERO, 
       coalesce(T2.DATE_PAIEMENT,T1.DATE_DEBUT) AS DATE_PAIEMENT, 
       T1.DATE_DEBUT, 
       T1.DATE_FIN, 
       coalesce(T2.PUBLIE,1) AS PUBLIE, 
       T3.TYPE_ABO
FROM TABLE1 T1
JOIN TABLE3 T3 ON T1.REFI = T3.REFI
JOIN TABLE4 T4 ON T1.USERID = T4.USERID
LEFT JOIN TABLE2 T2 ON T1.NFACTURE = T2.NFACTURE
WHERE T1.APPLICATION = 'APPLI' 
  AND T4.ID=502
  AND 
  (
    (
      T3.TYPE_ABO = 1
      AND T2.STATUS_PAIEMENT=0
    )
    OR T3.TYPE_ABO IN (2, 5)
  )

Celle-ci fonctionne parfaitement, je vais voire si elle leur convient plus.
En tout cas merci de votre aide!

"optimiser" une reqûete UNION ALL

Langage SQL

Discussions similaires

Partager

Partager