Optimisation d'une requête complexe (Oracle 10g)

Version imprimable

Bonjour,

Je suis confronté à une requête SQL un peu complexe et qui peut prendre un temps d'exécution excessif ( >>5h :roll:)
dans un premier temps je pense réorganiser les bouts de code de la requête.
la requête est sous la forme suivante :
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 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 Select entite.libelle, candidat.nom, candidat.prenom, candidat.date, ... from candidat, entite, employe, service, (select nom, prenom from employe, entite where employe.entite = entite.id and entite.id = 34894) retenu where date = to_date('14/05/2008','DD/MM/YYYY'), and division.id = entite.division and retenu.nom = candidat.nom and retenu.prenom = candidat.prenom and cadidat.type = 'interne' union Select entite.libelle, candidat.nom, candidat.prenom, candidat.date, ... from prestataire, employeur, employe, service, (select nom, prenom from employeur, entite where employe.entite = entite.id and entite.id = 34894) retenu where date = to_date('14/05/2008','DD/MM/YYYY'), and division.id = entite.division and retenu.nom = prestataire.nom and retenu.prenom = prestataire.prenom and prestataire.profil = 'SAP'
Vous avez surement remarqué qu'un select imbriqué se répète deux fois.
Code:

1 2 3 4 5 6 (select nom, prenom from employe, entite where employe.entite = entite.id and entite.id = 34894) retenu
je cherche à récupérer les résultats de ce select(retenu), et le réutiliser autant de fois. sans avoir à lancer la sous requête une deuxième fois.
à titre indicatif, le temps de traitement est divisé par 3 si je stock le résultat de cette requête dans une table temporaire. sauf que ce n'est pas propre, surtout si cette requête est lancé par plusieurs utilisateurs en même temps...

D'avance merci pour vos réponses.

25/08/2009, 14h53
Waldar

Il y a effectivement quelques améliorations à apporter.

La première, utiliser les jointures ANSI et des alias : vous gagnerez en lisibilité et vous remarquerez que vous avez de coûteux produits cartésiens (ou alors vous avez trop coupé votre requête, ce qui n'est pas une bonne chose si vous voulez qu'on vous aide à l'optimiser).

La deuxième, pour factoriser une sous-requête vous pouvez utilisez un WITH.

La troisième, à priori vous ne cernez pas les mêmes candidats / prestataires donc vous ne pouvez pas avoir de doublons : utilisez UNION ALL entre vos deux requêtes.

Enfin, peut-être (là ce seront votre modèle et/ou vos données qui le décideront) pouvez-vous vous passer de l'union en effectuant deux jointures externes.
25/08/2009, 17h34
ddl2004
- Pour la première suggestion: D'après ce que j'ai pu lire sur les JOINTURES ANSI SQL99, elle n'apporte aucune amélioration en terme de performance (vrais? ou j'ai compris de travers?)
- Pour la deuxième suggestion: C'est ce que je cherchais exactement! Milles merci :D
- Pour la troisième suggestion: UNION est plus couteux que UNION ALL?
Citation:

Envoyé par Waldar

Pouvez-vous vous passer de l'union en effectuant deux jointures externes.

auriez vous un exemple pour ca? ou une suggestion de doc je suis aussi preneur.

Merci bcp.

Oui, pas d'amélioration de performance puisqu'il s'agit d'une modification syntaxique de vos requêtes.
Mais quel gain en lisibilité, et on voit tout de suite si on a oublié une jointure (ce que je vous le rappelle est peut-être votre cas).

UNION implique un distinct global sur vos données, UNION ALL non :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
select 1 as nb from dual union     select 1 from dual;
 
nb
--
1
 
select 1 as nb from dual union all select 1 from dual;
 
nb
--
1
1

Pour le dernier point, aujourd'hui j'ai l'impression que vous faites ceci :

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
25
26
27
28
29
30
31
with refer as
(
select 1 as id from dual union all
select 2       from dual union all
select 3       from dual union all
select 4       from dual union all
select 5       from dual
),
    cand as
(
select 1 as id, 'Name1' as name from dual union all
select 3      , 'Name3'         from dual union all
select 5      , 'Name5'         from dual
),
    prest as
(
select 2 as id, 'Name2' as name from dual union all
select 4      , 'Name4'         from dual    
)
select r.id, c.name
  from refer r inner join cand c  on c.id = r.id
UNION all
select r.id, p.name
  from refer r inner join prest p on p.id = r.id;
 
ID	NAME
1	Name1
3	Name3
5	Name5
2	Name2
4	Name4

Je vous propose de tester ceci.
Je ne sais pas si ce sera plus rapide ou même possible, peut-être que oui, peut-être que non :

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
25
26
27
28
29
30
with refer as
(
select 1 as id from dual union all
select 2       from dual union all
select 3       from dual union all
select 4       from dual union all
select 5       from dual
),
    cand as
(
select 1 as id, 'Name1' as name from dual union all
select 3      , 'Name3'         from dual union all
select 5      , 'Name5'         from dual
),
    prest as
(
select 2 as id, 'Name2' as name from dual union all
select 4      , 'Name4'         from dual    
)
select r.id, coalesce(c.name, p.name) as name
  from refer r
       left outer join cand c  on c.id = r.id
       left outer join prest p on p.id = r.id;
 
ID	NAME
1	Name1
3	Name3
5	Name5
2	Name2
4	Name4

27/08/2009, 10h38
ddl2004

Bonjour,

Merci pour votre réponse et pour votre aide. ça m'a beaucoup avancé.

en ce qui concerne les jointures, j'ai abandonné la réécriture de la requête; La requête d'origine fait environs 550 Lignes, et vu le temps qui presse je n'ai pas trop le choix.

ps: vos exemples m'ont fait découvrir d'autres fonctions utiles "COALESCE " par exemple ;)

Merci pour tout