Amélioration temps exécution update

[angebe]

Bonjour,

Voici mon problème. J'ai une première table dans laquelle j'ai une ligne différente pour chaque mois et je cherche à regrouper toutes les données sur une même ligne de ma table finale, qui contient donc une colonne pour la mesure pour chacun des 12 derniers mois.

Ma solution actuelle est de mettre toutes les données de la première table dans un cursor et de faire 12 updates dans la boucle dans laquelle je fetche le cursor, un pour chaque mois, avec à chaque fois la clause where permettant de ne faire l'update que si le record est du bon mois.

Je pense que si la condition du where était mise dans un if avant la commande d'update je pourrais gagner pas mal en temps d'exécution mais je ne parviens pas à avoir une syntaxe correcte. Sinon si vous avez d'autres idées elles sont les bienvenues.

Voici ma boucle dans l'état actuel (qui fonctionne mais avec tps d'exécution désastreux):

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
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
open subscription;
loop
FETCH subscription INTO subs_rec;
EXIT WHEN subscription%NOTFOUND;
 
 
UPDATE cmc_subscription_usage
SET M = subs_rec.sessions
WHERE subs_rec.nam_code = cmc_subscription_usage.nam_channel
AND subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
AND subs_rec.n_abon = cmc_subscription_usage.ide_subs
AND extract(Month FROM sysdate) = subs_rec.month
AND extract(Year FROM sysdate) = subs_rec.year;
 
UPDATE cmc_subscription_usage
SET M1 = subs_rec.sessions
WHERE subs_rec.nam_code = cmc_subscription_usage.nam_channel
AND subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
AND subs_rec.n_abon = cmc_subscription_usage.ide_subs
AND 
((extract(Month FROM sysdate) - 1 = subs_rec.month 
AND extract(Year FROM sysdate) = subs_rec.year)
OR
(extract(Month FROM sysdate) + 11 = subs_rec.month 
AND extract(Year FROM sysdate) - 1 = subs_rec.year)) ;
 
UPDATE cmc_subscription_usage
SET M2 = subs_rec.sessions
WHERE subs_rec.nam_code = cmc_subscription_usage.nam_channel
AND subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
AND subs_rec.n_abon = cmc_subscription_usage.ide_subs
AND 
((extract(Month FROM sysdate) - 2 = subs_rec.month 
AND extract(Year FROM sysdate) = subs_rec.year)
OR
(extract(Month FROM sysdate) + 10 = subs_rec.month 
AND extract(Year FROM sysdate) - 1 = subs_rec.year)) ;
 
UPDATE cmc_subscription_usage
SET M3 = subs_rec.sessions
WHERE subs_rec.nam_code = cmc_subscription_usage.nam_channel
AND subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
AND subs_rec.n_abon = cmc_subscription_usage.ide_subs
AND 
((extract(Month FROM sysdate) - 3 = subs_rec.month 
AND extract(Year FROM sysdate) = subs_rec.year)
OR
(extract(Month FROM sysdate) + 9 = subs_rec.month 
AND extract(Year FROM sysdate) - 1 = subs_rec.year)) ;
 
UPDATE cmc_subscription_usage
SET M4 = subs_rec.sessions
WHERE subs_rec.nam_code = cmc_subscription_usage.nam_channel
AND subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
AND subs_rec.n_abon = cmc_subscription_usage.ide_subs
AND 
((extract(Month FROM sysdate) - 4 = subs_rec.month 
AND extract(Year FROM sysdate) = subs_rec.year)
OR
(extract(Month FROM sysdate) + 8 = subs_rec.month 
AND extract(Year FROM sysdate) - 1 = subs_rec.year)) ;
 
UPDATE cmc_subscription_usage
SET M5 = subs_rec.sessions
WHERE subs_rec.nam_code = cmc_subscription_usage.nam_channel
AND subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
AND subs_rec.n_abon = cmc_subscription_usage.ide_subs
AND 
((extract(Month FROM sysdate) - 5 = subs_rec.month 
AND extract(Year FROM sysdate) = subs_rec.year)
OR
(extract(Month FROM sysdate) + 7 = subs_rec.month 
AND extract(Year FROM sysdate) - 1 = subs_rec.year)) ;
 
UPDATE cmc_subscription_usage
SET M6 = subs_rec.sessions
WHERE subs_rec.nam_code = cmc_subscription_usage.nam_channel
AND subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
AND subs_rec.n_abon = cmc_subscription_usage.ide_subs
AND 
((extract(Month FROM sysdate) - 6 = subs_rec.month 
AND extract(Year FROM sysdate) = subs_rec.year)
OR
(extract(Month FROM sysdate) + 6 = subs_rec.month 
AND extract(Year FROM sysdate) - 1 = subs_rec.year)) ;
 
UPDATE cmc_subscription_usage
SET M7 = subs_rec.sessions
WHERE subs_rec.nam_code = cmc_subscription_usage.nam_channel
AND subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
AND subs_rec.n_abon = cmc_subscription_usage.ide_subs
AND 
((extract(Month FROM sysdate) - 7 = subs_rec.month 
AND extract(Year FROM sysdate) = subs_rec.year)
OR
(extract(Month FROM sysdate) + 5 = subs_rec.month 
AND extract(Year FROM sysdate) - 1 = subs_rec.year)) ;
 
UPDATE cmc_subscription_usage
SET M8 = subs_rec.sessions
WHERE subs_rec.nam_code = cmc_subscription_usage.nam_channel
AND subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
AND subs_rec.n_abon = cmc_subscription_usage.ide_subs
AND 
((extract(Month FROM sysdate) - 8 = subs_rec.month 
AND extract(Year FROM sysdate) = subs_rec.year)
OR
(extract(Month FROM sysdate) + 4 = subs_rec.month 
AND extract(Year FROM sysdate) - 1 = subs_rec.year)) ;
 
UPDATE cmc_subscription_usage
SET M9 = subs_rec.sessions
WHERE subs_rec.nam_code = cmc_subscription_usage.nam_channel
AND subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
AND subs_rec.n_abon = cmc_subscription_usage.ide_subs
AND 
((extract(Month FROM sysdate) - 9 = subs_rec.month 
AND extract(Year FROM sysdate) = subs_rec.year)
OR
(extract(Month FROM sysdate) + 3 = subs_rec.month 
AND extract(Year FROM sysdate) - 1 = subs_rec.year)) ;
 
UPDATE cmc_subscription_usage
SET M10 = subs_rec.sessions
WHERE subs_rec.nam_code = cmc_subscription_usage.nam_channel
AND subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
AND subs_rec.n_abon = cmc_subscription_usage.ide_subs
AND 
((extract(Month FROM sysdate) - 10 = subs_rec.month 
AND extract(Year FROM sysdate) = subs_rec.year)
OR
(extract(Month FROM sysdate) + 2 = subs_rec.month 
AND extract(Year FROM sysdate) - 1 = subs_rec.year)) ;
 
UPDATE cmc_subscription_usage
SET M11 = subs_rec.sessions
WHERE subs_rec.nam_code = cmc_subscription_usage.nam_channel
AND subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
AND subs_rec.n_abon = cmc_subscription_usage.ide_subs
AND 
((extract(Month FROM sysdate) - 11 = subs_rec.month 
AND extract(Year FROM sysdate) = subs_rec.year)
OR
(extract(Month FROM sysdate) + 1 = subs_rec.month 
AND extract(Year FROM sysdate) - 1 = subs_rec.year)) ;
 
UPDATE cmc_subscription_usage
SET M12 = subs_rec.sessions
WHERE subs_rec.nam_code = cmc_subscription_usage.nam_channel
AND subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
AND subs_rec.n_abon = cmc_subscription_usage.ide_subs
AND 
(extract(Month FROM sysdate) = subs_rec.month 
AND extract(Year FROM sysdate) - 1 = subs_rec.year) ;
 
 
END LOOP;
CLOSE subscription;

[orafrance]

déjà si tu remplaces :

Code :

1
2
3
4
5
UPDATE ...
SET col1 = ...
 
UPDATE ...
SET col2 = ...

etc...

Par :

Code :

1
2
UPDATE ...
SET col1=... , col2 = ...

ça ira bien mieux

Ensuite tu peux probablement supprimer le curseur pour intégrer la requête dans l'UPDATE genre :

Code :

1
2
AND extract(Month FROM sysdate) = subs_rec.month
AND extract(Year FROM sysdate) = subs_rec.year;

devient :

Code :

TO_CHAR(SYSDATE,'MMYYYY') = (SELECT month||year FROM <la requête du curseur>)

[angebe]

Excuse moi de mettre ce que tu dis en doute mais je ne vois pas très bien en quoi mon code va être plus rapide.
Le select est maintenant répété 12 fois au lieu de l'unique fois pour l'insérer dans le cursor voici l'ébauche de code, pour les 2 premiers mois seulement (à répéter donc pour les 12 autres en changeant la condition)

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
UPDATE cmc_subscription_usage
SET 
M = 
SELECT sessions
FROM
	(SELECT sum(sessions) sessions, n_pers_prs n_pers_prs, n_abon n_abon, nam_code nam_code, etl.YEAR year, etl.MONTH month
	FROM etl_ami_in_hbses etl, V_CMC_SUBSCRIPTION_USAGE cod
	WHERE etl.CHANNEL2 = cod.IDE_CODE
	GROUP BY n_pers_prs, n_abon, nam_code, etl.YEAR, etl.MONTH) subs_rec 
WHERE subs_rec.nam_code = cmc_subscription_usage.nam_channel
AND subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
AND subs_rec.n_abon = cmc_subscription_usage.ide_subs
AND extract(Month FROM sysdate) = subs_rec.month
AND extract(Year FROM sysdate) = subs_rec.year,
 
M1 = 
SELECT sessions
FROM
	(SELECT sum(sessions) sessions, n_pers_prs n_pers_prs, n_abon n_abon, nam_code nam_code, etl.YEAR year, etl.MONTH month
	FROM etl_ami_in_hbses etl, V_CMC_SUBSCRIPTION_USAGE cod
	WHERE etl.CHANNEL2 = cod.IDE_CODE
	GROUP BY n_pers_prs, n_abon, nam_code, etl.YEAR, etl.MONTH) subs_rec 
WHERE subs_rec.nam_code = cmc_subscription_usage.nam_channel
AND subs_rec.n_pers_prs = cmc_subscription_usage.cust_ide_npers
AND subs_rec.n_abon = cmc_subscription_usage.ide_subs
AND 
((extract(Month FROM sysdate) - 1 = subs_rec.month 
AND extract(Year FROM sysdate) = subs_rec.year)
OR
(extract(Month FROM sysdate) + 11 = subs_rec.month 
AND extract(Year FROM sysdate) - 1 = subs_rec.year))
;

[orafrance]

Tu peux probablement utiliser la clause WITH et de toute façon, vaut mieux faire le SELECT 2 fois que l'update 12 fois multiplié par le nombre de lignes du curseur

Et supprime tes conditions :

Code :

1
2
3
4
5
6
7
 
AND 
((extract(Month FROM sysdate) - 1 = subs_rec.month 
AND extract(Year FROM sysdate) = subs_rec.year)
OR
(extract(Month FROM sysdate) + 11 = subs_rec.month 
AND extract(Year FROM sysdate) - 1 = subs_rec.year))

Et utilise des CASE ou DECODE dans le SELECT, comme ça au lieu de 12 UPDATE t'en a plus qu'un seul

[angebe]

Oki, merci beaucoup, je vais aller faire ma réécriture et tester

[angebe]

Tout compte fait j'ai quand même encore un souci, je ne vois pas comment faire en une seule update, j'ai beau retourner les case when et decode dans tout les sens.... il ne faut évidement pas qu'une étape faisant l'update d'une colonne écrase la valeur d'une autre colonne (si je fais un select avec un case when pour chaque colonne, une seule aura une valeur, celle du mois concerné alors que les autres vaudront null...)

[orafrance]

Un truc de ce stype :

Code :

CASE WHEN condition OK THEN newvalue ELSE colonne

pour chaque colonne

[angebe]

voilà où j'en suis:

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
UPDATE cmc_subscription_usage
SET (M, M1, M2, M3, M4, M5, M6, M7, M8, M9, M10, M11, M12) = 
(SELECT
case when extract(Month FROM sysdate) = subs.month AND extract(Year FROM sysdate) = subs.year
	 then sessions else M
end,
case when ((extract(Month FROM sysdate) - 1 = subs.month AND extract(Year FROM sysdate) = subs.year)
	 OR (extract(Month FROM sysdate) + 11 = subs.month AND extract(Year FROM sysdate) - 1 = subs.year))
	 then sessions else M1
end,
case when ((extract(Month FROM sysdate) - 2 = subs.month AND extract(Year FROM sysdate) = subs.year)
	 OR (extract(Month FROM sysdate) + 10 = subs.month AND extract(Year FROM sysdate) - 1 = subs.year))
	 then sessions else M2
end,
case when ((extract(Month FROM sysdate) - 3 = subs.month AND extract(Year FROM sysdate) = subs.year)
	 OR (extract(Month FROM sysdate) + 9 = subs.month AND extract(Year FROM sysdate) - 1 = subs.year))
	 then sessions else M3
end,
case when ((extract(Month FROM sysdate) - 4 = subs.month AND extract(Year FROM sysdate) = subs.year)
	 OR (extract(Month FROM sysdate) + 8 = subs.month AND extract(Year FROM sysdate) - 1 = subs.year))
	 then sessions else M4
end,
case when ((extract(Month FROM sysdate) - 5 = subs.month AND extract(Year FROM sysdate) = subs.year)
	 OR (extract(Month FROM sysdate) + 7 = subs.month AND extract(Year FROM sysdate) - 1 = subs.year))
	 then sessions else M5
end,
case when ((extract(Month FROM sysdate) - 6 = subs.month AND extract(Year FROM sysdate) = subs.year)
	 OR (extract(Month FROM sysdate) + 6 = subs.month AND extract(Year FROM sysdate) - 1 = subs.year))
	 then sessions else M6
end,
case when ((extract(Month FROM sysdate) - 7 = subs.month AND extract(Year FROM sysdate) = subs.year)
	 OR (extract(Month FROM sysdate) + 5 = subs.month AND extract(Year FROM sysdate) - 1 = subs.year))
	 then sessions else M7
end,
case when ((extract(Month FROM sysdate) - 8 = subs.month AND extract(Year FROM sysdate) = subs.year)
	 OR (extract(Month FROM sysdate) + 4 = subs.month AND extract(Year FROM sysdate) - 1 = subs.year))
	 then sessions else M8
end,
case when ((extract(Month FROM sysdate) - 9 = subs.month AND extract(Year FROM sysdate) = subs.year)
	 OR (extract(Month FROM sysdate) + 3 = subs.month AND extract(Year FROM sysdate) - 1 = subs.year))
	 then sessions else M9
end,
case when ((extract(Month FROM sysdate) - 10 = subs.month AND extract(Year FROM sysdate) = subs.year)
	 OR (extract(Month FROM sysdate) + 2 = subs.month AND extract(Year FROM sysdate) - 1 = subs.year))
	 then sessions else M10
end,
case when ((extract(Month FROM sysdate) - 11 = subs.month AND extract(Year FROM sysdate) = subs.year)
	 OR (extract(Month FROM sysdate) + 1 = subs.month AND extract(Year FROM sysdate) - 1 = subs.year))
	 then sessions else M11
end,
case when (extract(Month FROM sysdate)  = subs.month AND extract(Year FROM sysdate) - 1 = subs.year)
	 then sessions else M12
end
FROM (SELECT sum(sessions) sessions, n_pers_prs n_pers_prs, n_abon n_abon, nam_code nam_code, etl.YEAR year, etl.MONTH month
	FROM etl_ami_in_hbses etl, V_CMC_SUBSCRIPTION_USAGE cod
	WHERE etl.CHANNEL2 = cod.IDE_CODE
	GROUP BY n_pers_prs, n_abon, nam_code, etl.YEAR, etl.MONTH) subs
WHERE subs.nam_code = cmc_subscription_usage.nam_channel
AND subs.n_pers_prs = cmc_subscription_usage.cust_ide_npers
AND subs.n_abon = cmc_subscription_usage.ide_subs);

Il me donne l'erreur suivante: ORA-01427: single-row subquery returns more than one row

Je ne comprends pas étant donné qu'un group by est fait dans la sous requête interne et qu'ensuite je fais une jointure sur tout les éléments sur lequels le group by a été fait dans la sous-requête.

Quelqu'un voit il d'où peut venir le problème?

Merci

[orafrance]

la sous requête après le SET retourne plus d'une ligne

[angebe]

Jusque là j'avais compris... mais comment celà est il possible?
Cette sous requête sélectionne ses lignes dans la table subs venant d'une autre sous-requête et dans laquelle on a un group by sur tout les éléments qui vont servir à la sélection (et aucun autre)

[Michel SALAIS]

Je n'ai pas regardé les détails mais si tu as 10g tu pourrais voir l'instruction MERGE.

[wondersonic]

Envoyé par Michel SALAIS

Je n'ai pas regardé les détails mais si tu as 10g tu pourrais voir l'instruction MERGE.

Je ne pense pas que angebe veuille insérer de nouvelles lignes dans cmc_subscription_usage, à priori (et vu ce qu'il veut faire : update),
il n'y a qu'une seule ligne dans cmc_subscription_usage.

ps : le MERGE existe à partir de la 9i

[Michel SALAIS]

Envoyé par wondersonic

Je ne pense pas que angebe veuille insérer de nouvelles lignes dans cmc_subscription_usage, à priori (et vu ce qu'il veut faire : update),
il n'y a qu'une seule ligne dans cmc_subscription_usage.

ps : le MERGE existe à partir de la 9i

C'est pourquoi je parle de 10g qui permet de faire soit update soit insert soit les deux avec l'instruction MERGE contrairement à 9i

[wondersonic]

Rectifications : il n'y a qu'une seule ligne par couple (n_pers_prs, n_abon, nam_code, etl.YEAR).

Intéressant

[wondersonic]

Envoyé par Michel SALAIS

C'est pourquoi je parle de 10g qui permet de faire soit update soit insert soit les deux avec l'instruction MERGE contrairement à 9i

Toutes mes excuses, tu as raison

[wondersonic]

Envoyé par Michel SALAIS

Je n'ai pas regardé les détails mais si tu as 10g tu pourrais voir l'instruction MERGE.

Et je me lance (fais uniquement pour 2 mois mais c'est facile de compléter),
j'espère que je n'ai pas mis trop de coquilles...

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
merge INTO cmc_subscription_usage dest USING (SELECT sum(decode(month,1,sessions,0)) m1, sum(decode(month,2,sessions,0)) m2, 
year, nam_channel, cust_ide_npers, ide_subs
FROM src 
-- where year = extract(year from sysdate) -- update older data?
GROUP BY year, nam_channel, cust_ide_npers, ide_subs) src
ON (dest.nam_channel = src.nam_channel AND 
    dest.cust_ide_npers = src.cust_ide_npers AND
    dest.ide_subs = src.ide_subs AND
    dest.month = src.month AND
    dest.year = src.year)
when matched then UPDATE SET dest.m1 = src.m1, dest.m2 = src.m2
when NOT matched then INSERT 
(dest.m1,
 dest.m2,
 dest.nam_channel,
 dest.cust_ide_npers,
 dest.ide_subs,
 dest.month,
 dest.year) 
VALUES 
(src.m1,
 src.m2,
 src.nam_channel,
 src.cust_ide_npers,
 src.ide_subs,
 src.month,
 src.year);

A tout hasard, j'ai mis la clause d'insert (remplace un autre process existant qui rajoute par couple d'aggrégation une ligne vierge ?).

Une optimisation est effectivement, de se demander si l'on met à jour des vielles données (des années passéesà ou seulement les données de l'année en cours (voir le commentaire).

[angebe]

Merci, je vais voir comment je pourrais adapter ça chez moi. Il ne s'agit effectivement pas de prendre le mois 1 de l'année puis les mois 2 de l'année mais de mettre dans m le mois en cours, dans m1 le mois précédent... et il fayut tenir compte du fait qu'en fonction du mois en cours il y a x mois ce situe soit cette année ci soit l'année précédente....

[angebe]

Envoyé par wondersonic

Je ne pense pas que angebe veuille insérer de nouvelles lignes dans cmc_subscription_usage, à priori (et vu ce qu'il veut faire : update),
il n'y a qu'une seule ligne dans cmc_subscription_usage.

ps : le MERGE existe à partir de la 9i

Correction: Ce qu'elle veut faire