Améliorer un UPDATE

Version imprimable

Améliorer un UPDATE

Bonjour à tous,

Je dois faire un UPDATE sur une table contenant 170000 lignes, le tout en un minimum de temps car l'UPDATE est dans une procédure stockée à utiliser par des utilisateurs.
Mon code est le suivant :
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 declare debextract date := :date1; finextract date := :date2; date_boucle date; tbf number; type_jour varchar2(3); cursor c_tbf is /*déclaration du curseur*/ begin for c in c_tbf loop /*On calcule les durées en minutes*/ tbf :=0; date_boucle := debextract; select cal_particularite into type_jour from si_calendrier_20042020 where to_date(date_boucle) = cal_datejour; while date_boucle <= finextract /*on fait tout un tas de calcul pour avoir tbf*/ update si_proc_disponibilite set prd_tbf = tbf where rowid = c.rowid ; end loop; commit; end;
Ce code fonctionne met compte tenu du nombre de lignes, cela met plus de 30 minutes à s'exécuter, ce qui n'est pas viable.

Est-ce que qq1 a une idée pour améliorer le script ou une autre méthode pour fait un UPDATE en masse ?

Je suis sous ORACLE 9i et je suis obligée d'utiliser une procédure stockée car elle est lancée via COGNOS qui n'accepte que ce genre d'objets.

Merci pour vos réponses
Darcynette

19/10/2007, 11h08
Loyd1974
Pas évident comme ça de t'aider mais les pistes que j'explorerais serait :
- Utiliser le bulk collect et le FORALL au lieu de faire du fetch et de l'update unitaire
- Essayer d'intégrer la lecture du SI_CALENDRIER_20042020 au sein de ton curseur, encore une fois pour éviter de devoir chercher des valeurs de manière unitaire
- Ne pas faire de commit pour chaque ligne que tu updates mais seulement au bout d'un certain nombre. Mais si tu travailles en tableau avec le bulk collect, alors tu peux faire un commit à la fin de ta boucle de traitement comme ici
19/10/2007, 11h14
Darcynette

Merci pour ces pistes de recherche !!

Je vais essayer de comprendre les fonctionnements de FORALL et BULK COLLECT pour modifier tout ca.
Pour info, la recherche sur le calendrier doit se faire pour chaque jour balayer car il permet de déterminer si le jour est dans un week end ou un jour férie, le calcul de tbf étant différent dans ce cas (merci les règles de gestion à rallonge :aie:)

Si j'ai des questions, je n'hésiterai pas à les poser ;)

@+
Darcynette

Peux-tu poster le code complet stp, parce que
Code:

1 2 3 4 5 6 7 LOOP date_boucle := debextract; SELECT cal_particularite INTO type_jour FROM si_calendrier_20042020 WHERE TO_DATE(date_boucle) = cal_datejour; END LOOP;
tu pourrais le sortir de la boucle vu que date_boucle = debextract (=:date1)

Oups, effectivement, vu ce que j'ai posté, ce n'est pas très parlant!
Le code complet est le suivant, avec qq explications :

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
 
declare
debextract date := :date1;
finextract date := :date2;
date_boucle date;
tbf number;
type_jour varchar2(3);
cursor c_tbf is 
	   select
	   prd_famille, prd_domaine, 
	   prd_code_bm,  
	   prd_reseau , rowid
	   from si_proc_disponibilite 
	   where (prd_domaine <> 'S-TE' and prd_domaine <> 'S-EQ' ) 
	   or (prd_code_bm like 'SONO%' and prd_code_bm <> 'SONOR-');
begin
for c in c_tbf
loop
/*On calcule les durées en minutes*/
	tbf :=0;
	date_boucle := debextract;
/*Je veux récupérer pour chaque jour si c'est un jour férié ou un dimanche car le calcul est différent*/
/*Mais peut-être que ce n'est pas judicieux ou le plus simple....*/
	select cal_particularite into type_jour from si_calendrier_20042020 where to_date(date_boucle) = cal_datejour; 
/*Je fais une boucle pour calculer par jour la durée*/
	while date_boucle <= finextract
	 loop
	 case when (c.prd_domaine in ('EQVT', 'EQPT', 'S-TE') or (c.prd_domaine in ('EQ11') and c.prd_famille in ('A001', 'A002')))
	 then tbf := tbf + 24*60;
	   date_boucle := date_boucle + 1;
	else case when ((type_jour = 'JF' or (to_char(date_boucle, 'day') in ('dimanche'))) and c.prd_reseau = 'METRO') 
	  then tbf := tbf + 21*60; 
	  date_boucle := date_boucle + 1; 
   	  else tbf := tbf + 20*60; 
	  date_boucle := date_boucle+1; 
	  end case; 
	end case ;
	end loop; 
update si_proc_disponibilite 
set prd_tbf = tbf 
where prd_code_bm = c.prd_code_bm;
end loop;
commit; 	
end;

J'ai aussi une question sur l'utilisation de FORALL par rapport à mon traitement de donnée.
Apparemment FORALL n'accepte pas plusieurs instructions à l'intérieur, comment je dois faire par rapport à ma boucle WHILE ?

Merci à tous pour votre aide.
Darcynette

Pour Forall, va voir dans les tuto, le cours de Sheik sur sql. Le FOrall est bien expliqué.

Sinon, j'avais bien raison, le select sur le calendrier est fait 1 fois par boucle pour rien : 1 seule fois avant la boucle suffit.

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
47
48
49
50
 
DECLARE
debextract DATE := :date1;
finextract DATE := :date2;
date_boucle DATE;
tbf NUMBER;
type_jour VARCHAR2(3);
CURSOR c_tbf IS 
	   SELECT prd_famille, prd_domaine, 
	   			prd_code_bm, prd_reseau
	   FROM SI_PROC_DISPONIBILITE 
	   WHERE (prd_domaine <> 'S-TE' AND prd_domaine <> 'S-EQ' ) 
	   OR (prd_code_bm LIKE 'SONO%' AND prd_code_bm <> 'SONOR-')
		 FOR UPDATE NOWAIT;
BEGIN
 
	/*Je veux récupérer pour chaque jour si c'est un jour férié ou un dimanche car le calcul est différent*/
	/*Mais peut-être que ce n'est pas judicieux ou le plus simple....*/
	SELECT cal_particularite 
	INTO type_jour 
	FROM si_calendrier_20042020 
	WHERE  cal_datejour = TO_DATE(debextract); 
 
	FOR c IN c_tbf
	LOOP
		/*On calcule les durées en minutes*/
		tbf :=0;
		date_boucle := debextract;
 
		/*Je fais une boucle pour calculer par jour la durée*/
		WHILE date_boucle <= finextract
		LOOP
			 CASE WHEN (c.prd_domaine IN ('EQVT', 'EQPT', 'S-TE') OR (c.prd_domaine IN ('EQ11') AND c.prd_famille IN ('A001', 'A002')))
			 THEN tbf := tbf + 24*60;
			 ELSE 
				CASE WHEN ((type_jour = 'JF' OR (TO_CHAR(date_boucle, 'day') IN ('dimanche'))) AND c.prd_reseau = 'METRO') 
			  		THEN tbf := tbf + 21*60; 
			  	ELSE tbf := tbf + 20*60; 
			  	END CASE; 
			 END CASE;
			date_boucle := date_boucle + 1; 
		END LOOP; 
 
		UPDATE SI_PROC_DISPONIBILITE 
		SET prd_tbf = tbf 
		WHERE CURRENT OF c_tbf;
 
	END LOOP;
COMMIT; 	
END;

Je vais voir si on peut pas tout faire en pl (la boucle de calcul de jour)

19/10/2007, 12h42
Darcynette

Mais je dois faire le test pour chaque valeur de date_boucle, qui est incrémentée de 1 à chaque tour et non pas par rapport à la date de début.
Exemple :
debextract = 01/01/2007
finextract = 03/01/2007
=> je dois récupérer
date_boucle = 01/01/2007 et type_jour = JF
date_boucle = 02/01/2007 et type_jour null
date_boucle = 03/01/2007 et type_jour null

Mais peut-être faut-il codé autrement??...

C'est ce que j'ai vu avant d'aller manger : Mais ton code est faux alors, le select n'est pas dans la boucle while.

Code:

1
2
3
4
5
6
7
date_boucle := debextract;
/*Je veux récupérer pour chaque jour si c'est un jour férié ou un dimanche car le calcul est différent*/
/*Mais peut-être que ce n'est pas judicieux ou le plus simple....*/
	SELECT cal_particularite INTO type_jour FROM si_calendrier_20042020 WHERE to_date(date_boucle) = cal_datejour; 
/*Je fais une boucle pour calculer par jour la durée*/
	while date_boucle <= finextract
	 loop

Je vais chercher.

Voila le code en diminuant le nb de select du calendrier

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
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
 
DECLARE
	debextract 	DATE := :date1;
	finextract 	DATE := :date2;
	date_boucle DATE;
	tbf 		NUMBER;
	v_nb_JF 	NUMBER;
CURSOR c_tbf (datedeb IN DATE, datefin IN DATE) IS 
	SELECT prd_famille, prd_domaine, 
			prd_code_bm, prd_reseau
	FROM SI_PROC_DISPONIBILITE 
	WHERE (prd_domaine <> 'S-TE' AND prd_domaine <> 'S-EQ' ) 
		OR (prd_code_bm LIKE 'SONO%' AND prd_code_bm <> 'SONOR-')
	FOR UPDATE NOWAIT;
BEGIN
 
	/*Je veux récupérer pour chaque jour si c'est un jour férié ou un dimanche car le calcul est différent*/
	/*Mais peut-être que ce n'est pas judicieux ou le plus simple....*/
 
	FOR c IN c_tbf (debextract, finextract)
	LOOP
		/*On calcule les durées en minutes*/
		tbf :=0;
 
		IF c.prd_domaine IN ('EQVT', 'EQPT', 'S-TE') 
		OR (c.prd_domaine IN ('EQ11') AND c.prd_famille IN ('A001', 'A002'))
		THEN 
			tbf := 24 * 60 * ( (finextract- debextract) + 1);
		ELSE
 
			IF c.prd_reseau = 'METRO'
			THEN
				-- Calcul du nb de jfériés non dimanches (Simplifiable si Calendrier contient tous les jours et pas que les fériés. Dans ce cas là, pas de boucle WHILE)
				SELECT COUNT(*)
				INTO v_nb_JF
				FROM SI_CALENDRIER_20042020 
				WHERE  cal_datejour BETWEEN debextract AND finextrac
				AND cal_particularite = 'JF'
				AND TO_CHAR(cal_datejour, 'day') <> 'dimanche';
 
			END IF;
 
			/*Je fais une boucle pour calculer par jour la durée*/
			date_boucle := debextract;
			WHILE date_boucle <= finextract
			LOOP
				IF c.prd_reseau = 'METRO' AND TO_CHAR(date_boucle, 'day') = 'dimanche'
				THEN
					tbf := tbf + 21 * 60;
				ELSE 
					tbf := tbf + 20 * 60; 
			  	END IF; 
				date_boucle := date_boucle + 1; 
			END LOOP; 
		-- S'il y avait des Jfériés autre que dimanche, on les a comptés en 20*60.
		v_tbf := v_tbf - n_vb_JF * 20 * 60 + v_nbJF * 21 * 60; 
 
		END IF;
 
		UPDATE SI_PROC_DISPONIBILITE 
		SET prd_tbf = tbf 
		WHERE CURRENT OF c_tbf;
 
	END LOOP;
COMMIT; 	
END;

19/10/2007, 13h48
Darcynette

Merci McM, j'ai compris mon erreur dans la récupération de la particularité du jour.
Par contre, les performances sont encore pires qu'avant :cry: (mais je pense que cela vient des updates unitaires).

J'ai regardé le tuto sur FORALL et BULK COLLECT mais j'avoue que j'ai du mal à appliquer à mon cas (notamment les histoires de collection). Les exemples sont donnés avec une seule colonne retournée dans le curseur alors que j'en ai plusieurs (peut-être que je n'ai pas tout vu....) et avec FORALL qui n'accepte qu'une seule instruction, j'ai du mal à voir comment collé ma partie de calcul dans l'ensemble...

Je début encore en ORACLE et là j'avoue que je me sens un peu dépassée :(:(

Merci pour tous les conseils que vous pourrez me donner
Darcynette

Pour simplifier l'UPDATE, le mieux est de faire une fonction qui te ramène le bon tbf, et de faire 1 seul update : Pas de boucles.

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
47
48
49
50
51
52
53
54
CREATE FUNCTION F_CALC_TBF(p_fam IN VARCHAR2, p_dom IN VARCHAR2, p_reseau IN VARCHAR2,
					p_datedeb IN DATE, p_datefin IN DATE)
RETURN NUMBER IS
	v_tbf NUMBER := 0;
	v_nb_JF NUMBER;
	date_boucle DATE;
BEGIN
	IF p_dom IN ('EQVT', 'EQPT', 'S-TE') 
	OR (p_dom = 'EQ11' AND p_fam IN ('A001', 'A002'))
	THEN 
		v_tbf := 24 * 60 * ( (p_datefin - p_datedeb) + 1);
	ELSE
 
		IF p_reseau = 'METRO'
		THEN
				-- Calcul du nb de jfériés non dimanches.
			SELECT COUNT(*)
			INTO v_nb_JF
			FROM SI_CALENDRIER_20042020 
			WHERE  cal_datejour BETWEEN p_datedeb AND p_datefin
			AND cal_particularite = 'JF'
			AND TO_CHAR(cal_datejour, 'day') <> 'dimanche';
 
		END IF;
 
		/*Je fais une boucle pour calculer par jour la durée*/
		date_boucle := debextract;
		WHILE date_boucle <= finextract
		LOOP
			IF p_reseau = 'METRO' AND TO_CHAR(date_boucle, 'day') = 'dimanche'
			THEN
				v_tbf := v_tbf + 21 * 60;
			ELSE 
				v_tbf := v_tbf + 20 * 60; 
		  	END IF; 
			date_boucle := date_boucle + 1; 
		END LOOP; 
		-- S'il y avait des Jfériés autre que dimanche, on les a comptés en 20*60.
		v_tbf := v_tbf - n_vb_JF * 20 * 60 + v_nbJF * 21 * 60; 
	END IF;
	RETURN v_tbf;
END;
/
 
DECLARE
	debextract 	DATE := :date1;
	finextract 	DATE := :date2;
BEGIN
	UPDATE SI_PROC_DISPONIBILITE 
	SET tbf = F_CALC_TBF(prd_famille, prd_domaine, prd_reseau, debextract, finextract)
	WHERE (prd_domaine <> 'S-TE' AND prd_domaine <> 'S-EQ' ) 
		OR (prd_code_bm LIKE 'SONO%' AND prd_code_bm <> 'SONOR-');
	COMMIT; 	
END;

19/10/2007, 13h52
McM

Attention, mon code est faux, on prend en compte 2 fois les jours fériés. :?

Edit : Corrigé.
19/10/2007, 14h07
Darcynette

Et du coup avec ce système, je peux utiliser l'UPDATE en masse avec FORALL ??
parce que malheureusement, je dois faire tourner en moins de 2 minutes la procédure :(:( ce qui n'est pas gagné...

En tout cas, merci beaucoup pour ton aide, ca me permet de progresser dans l'écriture du code ;)

Citation:

Et du coup avec ce système, je peux utiliser l'UPDATE en masse avec FORALL ??

Même plus besoin de FORALL, ça devient un update normal sur toutes tes lignes.

Allez encore une optimisation super importante : On ne fait plus de select sur calendrier dans la fonction, vu que c'est toujours la même chose.

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
47
48
49
50
51
52
CREATE FUNCTION F_CALC_TBF(p_fam IN VARCHAR2, p_dom IN VARCHAR2, p_reseau IN VARCHAR2,
					p_datedeb IN DATE, p_datefin IN DATE, p_nbJF IN NUMBER)
RETURN NUMBER IS
	v_tbf NUMBER := 0;
	date_boucle DATE;
BEGIN
	IF p_dom IN ('EQVT', 'EQPT', 'S-TE') 
	OR (p_dom = 'EQ11' AND p_fam IN ('A001', 'A002'))
	THEN 
		v_tbf := 24 * 60 * ( (p_datefin - p_datedeb) + 1);
	ELSE
 
		/*Je fais une boucle pour calculer par jour la durée*/
		date_boucle := debextract;
		WHILE date_boucle <= finextract
		LOOP
			IF p_reseau = 'METRO' AND TO_CHAR(date_boucle, 'day') = 'dimanche'
			THEN
				v_tbf := v_tbf + 21 * 60;
			ELSE 
				v_tbf := v_tbf + 20 * 60; 
		  	END IF; 
			date_boucle := date_boucle + 1; 
		END LOOP; 
		-- S'il y avait des Jfériés autre que dimanche et METRO, on les a comptés en 20*60.
		IF p_reseau = 'METRO' THEN
			v_tbf := v_tbf - p_nbJF * 20 * 60 + p_nbJF * 21 * 60; 
		END IF;
	END IF;
	RETURN v_tbf;
END;
/
 
DECLARE
	debextract 	DATE := :date1;
	finextract 	DATE := :date2;
	v_nbJF NUMBER := 0;
BEGIN
	-- Calcul du nb de jfériés non dimanches.1 seule fois sur la période.
	SELECT COUNT(*)
	INTO v_nb_JF
	FROM SI_CALENDRIER_20042020 
	WHERE  cal_datejour BETWEEN p_datedeb AND p_datefin
	AND cal_particularite = 'JF'
	AND TO_CHAR(cal_datejour, 'day') <> 'dimanche';
 
	UPDATE SI_PROC_DISPONIBILITE 
	SET tbf = F_CALC_TBF(prd_famille, prd_domaine, prd_reseau, debextract, finextract, v_nbJF)
	WHERE (prd_domaine <> 'S-TE' AND prd_domaine <> 'S-EQ' ) 
		OR (prd_code_bm LIKE 'SONO%' AND prd_code_bm <> 'SONOR-');
	COMMIT; 	
END;

22/10/2007, 09h47
Darcynette

Merci, merci...que dire de plus !! et bien merci

:yaisse2:
Encore merci pour ton aide McM !!
Non seulement j'ai compris mes erreurs et j'ai compris tes corrections, mais en plus ca me retire un buisson d'épines du pied et ca va me permettre de partir en week end prolongé la semaine prochaine.

Ton script est vraiment super et fonctionne à la vitesse de l'éclair et en plus ca fait plaisir à mon exploitation :mouarf::mouarf:

Encore merci et à très bientot sur ce site vraiment bien fait !
Darcynette
22/10/2007, 10h34
McM

:merci:

C'est encore mieux du fait que tu ais compris l'opti proposée.

A bientôt. :D