2 pièce(s) jointe(s)
Optimisation d'une procédure
Bonjour,
Nous avons une procédure de nuit qui dure plus de 5 heures.
Elle commence à empiéter sur les horraires de travail, ce qui n'est pas là pour arranger sa durée...
J'ai tenté de l'optimiser, mais contre toute attente, chaque tentative d'optimisation ne fait qu'alourdir les requêtes !
Je ne sais plus quoi faire.
Voici un exemple, avec la première requête (elle sert pour faire un curseur qui englobe l'ensemble des traitements) :
La version originale :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
SELECT cnt.codsoc, cnt.sigtie, cnt.datapp, cnt.datech ,cnt.numcnt, cnt.libcnt, cnp.codpro, cnp.coduni, cnt.codzn5, pru.coefuv
from cnt, cnp, pru
where cnt.CODSOC = 100
and cnt.achvte = 'V'
and cnt.typeve = 'CNT'
and cnt.codzn4 = 'PDL'
and cnt.datrev <= to_char(sysdate, 'YYYYMMDD')
and TO_CHAR(TO_DATE(cnt.datech,'YYYYMMDD') +15,'YYYYMMDD') >= to_char(sysdate, 'YYYYMMDD')
and exists(select 1 from cnt cnt1 where cnt1.codsoc = 100 and cnt1.achvte ='V' and cnt1.typeve = 'CNT' and cnt1.numcnt = cnt.codzn5)
and cnp.codsoc = cnt.codsoc
and cnp.achvte = cnt.achvte
and cnp.numcnt = cnt.numcnt
and pru.codsoc = cnp.codsoc
and pru.codpro = cnp.codpro
and pru.coduni = cnp.coduni
order by cnt.codzn5, cnp.codpro |
La version "optimisée" :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
select c1.codsoc, c1.sigtie, c1.datapp, c1.datech, c1.numcnt, cnp.codpro, c1.codzn5, pru.coefuv
from cnt c1
inner join cnt c2 on c2.codsoc = c1.codsoc and c2.achvte = c1.achvte and c2.numcnt = c1.codzn5 and c2.codzn4 = 'ENS'
inner join cnp on cnp.codsoc = c1.codsoc and cnp.achvte = c1.achvte and cnp.numcnt = c1.numcnt and cnp.tqoi = '501'
inner join pru on pru.codsoc = cnp.codsoc and pru.codpro = cnp.codpro and pru.coduni = cnp.coduni
where c1.codsoc = 100
and c1.achvte = 'V'
and c1.typeve = 'CNT'
and c1.codzn4 = 'PDL'
and c1.datrev <= to_char(sysdate, 'YYYYMMDD')
and c1.datech >= to_char(to_date(to_char(sysdate, 'YYYYMMDD'), 'YYYYMMDD') - 15, 'YYYYMMDD')
order by c1.codzn5, cnp.codpro; |
Voici les optimisation que j'y ai déjà apporté :
-- Optimisation 1 : Réécriture de la requête au format SQL-92
-- Optimisation 2 : Utilisation un "in" à la place d'un "exists", car "in" est plus performant
-- Optimisation 3 : On ne fait pas de cast sur cnt.datech (n fois) mais sur V_date (1 fois)
-- Optimisation 4 : Ajout de cnp.tqoi dans la requête : index primaire sur CNP
-- Optimisation 5 : Remplacement du EXISTS par une jointure INNER JOIN
-- Optimisation 6 : Suppression de champs inutilement retournés par la requête
La requête originale met 3,86 secondes à s'exécuter, et environ 21 secondes pour fetcher l'ensemble des lignes (comptage de tête, je sais, c'est super précis...)
La requête "optimisée" met 3,02 secondes à s'exécuter, et environ 23 secondes pour fetcher l'ensemble des lignes.
Selon la charge du serveur, les temps ont tendance à s'inverser.
Bref, "optimisation" ou pas, ça met grossomodo le même temps à s'exécuter...
Les plans d'exécution sont en pièces attachées.
Ce qui est ensuite bizarre, c'est que j'ai modifié l'ensemble du script qui contenait :
Curseur sur la requête ci-dessus
Curseur sur une autre requête
Update
Loop
Curseur sur une autre requête
Update
Loop
Update
Appel a une procédure
Loop
Et maintenant :
Curseur sur la requête ci-dessus
Appel a une procédure
Select sum()
execute immediate 'Update'
Select sum()
execute immediate 'Update'
execute immediate 'Update'
Loop
Appel a une procédure
Donc je n'ai plus de boucles imbriquées, mais des requêtes qui retournent un sum() dans une variable.
Je n'ai plus d'update inline, mais des execute immediate à la place.
J'ai moins d'update (au lieu de les faire ligne par ligne, je les fais pour un lot de lignes).
Pourtant, le temps d'exécution est plus long qu'avant !!!
J'ai pris une à une toutes mes requêtes, j'ai vérifier que j'utilisais au maximum les champs des index uniques. Elles sont toutes mieux optimisées que les originales.
Je ne sais plus quoi faire pour optimiser...
Si vous voulez, je peux vous poster l'ensemble du script, mais c'est un peu long...
