Discussion :

[StringBuilder]

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 :

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

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

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