Améliorer un UPDATE

**Darcynette** · 19/10/2007, 10h56

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 :

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

**Loyd1974** · 19/10/2007, 11h08

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

**Darcynette** · 19/10/2007, 11h14

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

)

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

@+
Darcynette

**McM** · 19/10/2007, 11h29

Peux-tu poster le code complet stp, parce que

Code :

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

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)

**Darcynette** · 19/10/2007, 11h39

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 :

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

**McM** · 19/10/2007, 11h46

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 :

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

**Darcynette** · 19/10/2007, 12h42

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

**McM** · 19/10/2007, 13h18

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 :

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

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.

**McM** · 19/10/2007, 13h36

Voila le code en diminuant le nb de select du calendrier

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

**Darcynette** · 19/10/2007, 13h48

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

(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

**McM** · 19/10/2007, 13h48

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 :

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

**McM** · 19/10/2007, 13h52

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

Edit : Corrigé.

**Darcynette** · 19/10/2007, 14h07

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

**McM** · 19/10/2007, 18h03

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 :

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

**Darcynette** · 22/10/2007, 09h47

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

Encore merci et à très bientot sur ce site vraiment bien fait !
Darcynette

**McM** · 22/10/2007, 10h34

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

A bientôt.

Améliorer un UPDATE

SQL Oracle

Discussions similaires

Partager

Partager