Bonjour à tous,
Novice en SQL server, je travail actuellement sur une application SQL Server 2000. J'ai un problème avec ma procédure stockée SQL server. De toute évidence, elle paraît contre - performante car elle consomme beaucoup de temps CPU.
J’ai développé une procédure qui permet de calculer les indicateurs financiers d’un tableau de bord et de mettre à jour ces données dans ma table principale.
Cette procédure utilise une fonction qui permet de calculer différentes quantités nécessaires à la mise à jour de mon tableau de bord.
Je l’ai testé et elle marche. Le problème, c’est qu’elle met un certains temps pour s’exécuter.
Aussi, j’ai utilisé l’analiseur de requêtes (SQL Query Analyzer) afin d’obtenir une trace de ma procédure. J’ai constaté qu’elle utilisait un temps CPU assez important, ce qui pourrait justifier la lenteur constatée lors de son exécution.
- Pour la procédure : CPU : 517, Lectures (Reads) : 977
- Pour la fonction : CPU : 2499, Lectures (Reads) : 5504
L’application gérant des niveaux différents, le niveau 0 (zéro) correspond à la racine, le niveau 2 au projet et le niveau 3 au sous projet qui est le tableau de bord.
Au niveau de la racine, on affiche l’ensemble des programmes (niveau 1). Dans un programme, on a au moins un projet (niveau 2). Dans un projet, on a au moins un sous projet (niveau 3).
Aussi, je souhaiterai avoir vos conseils, vos orientations et vos propositions pour que la procédure consomme moins de CPU afin qu’elle s’exécute plus rapidement. Je passe probablement à côté de quelque chose.
Ci dessous, la procédure:
Ci- dessous la fonction exécutée dans la procédure:
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
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 CREATE PROCEDURE update_tableau_de_bord -- Cette procédure calcule les indicateurs financiers globaux de tableausx de bord -- This procedure computes the aggregated financial indicators from the scorecard @entry_id int, @level_id int AS SET NOCOUNT ON DECLARE @total_fin_ind1 decimal(38,14), --row_id=1 @total_fin_ind2 decimal(38,14), --row_id=14 @total_fin_ind3 decimal(38,14), --row_id=99 @total_fin_ind1_percent float, @total_fin_ind2_percent float, @total_fin_ind3_percent float, @total_fin_ind1_current decimal(38,14), @total_fin_ind2_current decimal(38,14), @total_fin_ind3_current decimal(38,14), @today_ctime int, @current_date datetime SET @current_date = DATEADD(quarter,-1,GETDATE()) IF @level_id = 0 -- le niveau 0 correspond à la racine RETURN -- calcul des indicateurs financiers IF @level_id=3 -- le niveau 3 correspond au sous projet et le niveau 2 correspond au projet BEGIN -- Obtenir l'indicateur financier 1 SELECT @total_fin_ind1 = ISNULL( SUM(ISNULL(col_0001, 0)), 0) FROM Tableau_de_bord_euro WHERE entry_id3 = @entry_id AND row_id = 1 -- Obtenir l'indicateur financier 2 SELECT @total_fin_ind2 = ISNULL( SUM(ISNULL(col_0001, 0)), 0) FROM Tableau_de_bord_euro WHERE entry_id3 = @entry_id AND row_id = 14 -- Obtenir l'indicateur financier 3 SELECT @total_fin_ind3 = ISNULL( SUM(ISNULL(col_0001, 0)), 0) FROM Tableau_de_bord_euro WHERE entry_id3 = @entry_id AND row_id = 99 -- calculer la valeur actuelle pour les indicateurs financiers EXEC @total_fin_ind1_current = calculate_qtd @entry_id, 1,@current_date EXEC @total_fin_ind2_current = calculate_qtd @entry_id,14,@current_date EXEC @total_fin_ind3_current = calculate_qtd @entry_id,99,@current_date END ELSE BEGIN SELECT @total_fin_ind1 = ISNULL( SUM(ISNULL(col_fin_ind1, 0)), 0), @total_fin_ind2 = ISNULL( SUM(ISNULL(col_fin_ind2, 0)), 0), @total_fin_ind3 = ISNULL( SUM(ISNULL(col_fin_ind3, 0)), 0), @total_fin_ind1_current = ISNULL( SUM(ISNULL(col_fin_ind1_current, 0)), 0), @total_fin_ind2_current = ISNULL( SUM(ISNULL(col_fin_ind2_current, 0)), 0), @total_fin_ind3_current = ISNULL( SUM(ISNULL(col_fin_ind3_current, 0)), 0) FROM TableDonnees WHERE parent_entry_id = @entry_id AND deleted=0 END IF @total_fin_ind1 = 0 SET @total_fin_ind1_percent = 0 ELSE SET @total_fin_ind1_percent = ROUND ((@total_fin_ind1_current/@total_fin_ind1)*100 ,0) IF @total_fin_ind2 = 0 SET @total_fin_ind2_percent = 0 ELSE SET @total_fin_ind2_percent = ROUND ((@total_fin_ind2_current/@total_fin_ind2)*100 ,0) IF @total_fin_ind3 = 0 SET @total_fin_ind3_percent = 0 ELSE SET @total_fin_ind3_percent = ROUND ((@total_fin_ind3_current/@total_fin_ind3)*100 ,0) IF @total_fin_ind1_percent>201 SET @total_fin_ind1_percent = 201 IF @total_fin_ind2_percent>201 SET @total_fin_ind2_percent = 201 IF @total_fin_ind3_percent>201 SET @total_fin_ind3_percent = 201 UPDATE TableDonnees SET col_fin_ind1 = @total_fin_ind1, col_fin_ind2 = @total_fin_ind2, col_fin_ind3 = @total_fin_ind3, col_fin_ind1_current = @total_fin_ind1_current, col_fin_ind2_current = @total_fin_ind2_current, col_fin_ind3_current = @total_fin_ind3_current, col_fin_ind1_percent = @total_fin_ind1_percent, col_fin_ind2_percent = @total_fin_ind2_percent, col_fin_ind3_percent = @total_fin_ind3_percent WHERE entry_id = @entry_id AND deleted = 0 GO
Merci par avance pour vos retours.
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
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 CREATE FUNCTION calculate_qtd (@entry_id int,@row_id int,@today_ctime datetime) -- Cette fonction permet de calculer les quantités.... RETURNS float AS BEGIN DECLARE @currentyear int, @currentquarter int, @sort_id int, @qtd float, @qtd_tmp float, @sc_col_id varchar(100), @year_tmp int, @previous_year int, @qtd_year float SET @qtd = 0 SET @currentyear = YEAR (@today_ctime) SET @currentquarter = DATEPART(quarter, @today_ctime) SELECT @sort_id=sort_id FROM SC_COLUMN_MAP WHERE year=@currentyear and quarter=@currentquarter DECLARE sc_column_cursor CURSOR READ_ONLY FOR SELECT sc_col_id,year FROM SC_COLUMN_MAP where sort_id<=@sort_id and sort_id%5<>0 order by sort_id OPEN sc_column_cursor FETCH NEXT FROM sc_column_cursor INTO @sc_col_id,@year_tmp SET @previous_year = @year_tmp WHILE @@FETCH_STATUS = 0 BEGIN SELECT @qtd_tmp= CASE @sc_col_id WHEN 'COL_0006' THEN COL_0006 WHEN 'COL_0016' THEN COL_0016 WHEN 'COL_0026' THEN COL_0026 WHEN 'COL_0036' THEN COL_0036 WHEN 'COL_0040' THEN COL_0040 WHEN 'COL_0056' THEN COL_0056 WHEN 'COL_0066' THEN COL_0066 WHEN 'COL_0076' THEN COL_0076 WHEN 'COL_0086' THEN COL_0086 WHEN 'COL_0090' THEN COL_0090 WHEN 'COL_0106' THEN COL_0106 WHEN 'COL_0116' THEN COL_0116 WHEN 'COL_0126' THEN COL_0126 WHEN 'COL_0136' THEN COL_0136 WHEN 'COL_0140' THEN COL_0140 WHEN 'COL_0156' THEN COL_0156 WHEN 'COL_0166' THEN COL_0166 WHEN 'COL_0176' THEN COL_0176 WHEN 'COL_0186' THEN COL_0186 WHEN 'COL_0190' THEN COL_0190 WHEN 'COL_0206' THEN COL_0206 WHEN 'COL_0216' THEN COL_0216 WHEN 'COL_0226' THEN COL_0226 WHEN 'COL_0236' THEN COL_0236 WHEN 'COL_0240' THEN COL_0240 WHEN 'COL_0256' THEN COL_0256 WHEN 'COL_0266' THEN COL_0266 WHEN 'COL_0276' THEN COL_0276 WHEN 'COL_0286' THEN COL_0286 WHEN 'COL_0290' THEN COL_0290 WHEN 'COL_0306' THEN COL_0306 WHEN 'COL_0316' THEN COL_0316 WHEN 'COL_0326' THEN COL_0326 WHEN 'COL_0336' THEN COL_0336 WHEN 'COL_0340' THEN COL_0340 WHEN 'COL_0356' THEN COL_0356 WHEN 'COL_0366' THEN COL_0366 WHEN 'COL_0376' THEN COL_0376 WHEN 'COL_0386' THEN COL_0386 WHEN 'COL_0390' THEN COL_0390 WHEN 'COL_0406' THEN COL_0406 WHEN 'COL_0416' THEN COL_0416 WHEN 'COL_0426' THEN COL_0426 WHEN 'COL_0436' THEN COL_0436 WHEN 'COL_0440' THEN COL_0440 WHEN 'COL_0456' THEN COL_0456 WHEN 'COL_0466' THEN COL_0466 WHEN 'COL_0476' THEN COL_0476 WHEN 'COL_0486' THEN COL_0486 WHEN 'COL_0490' THEN COL_0490 WHEN 'COL_0506' THEN COL_0506 WHEN 'COL_0516' THEN COL_0516 WHEN 'COL_0526' THEN COL_0526 WHEN 'COL_0536' THEN COL_0536 WHEN 'COL_0540' THEN COL_0540 WHEN 'COL_0556' THEN COL_0556 WHEN 'COL_0566' THEN COL_0566 WHEN 'COL_0576' THEN COL_0576 WHEN 'COL_0586' THEN COL_0586 WHEN 'COL_0590' THEN COL_0590 WHEN 'COL_0606' THEN COL_0606 WHEN 'COL_0616' THEN COL_0616 WHEN 'COL_0626' THEN COL_0626 WHEN 'COL_0636' THEN COL_0636 WHEN 'COL_0640' THEN COL_0640 WHEN 'COL_0656' THEN COL_0656 WHEN 'COL_0666' THEN COL_0666 WHEN 'COL_0676' THEN COL_0676 WHEN 'COL_0686' THEN COL_0686 WHEN 'COL_0690' THEN COL_0690 WHEN 'COL_0706' THEN COL_0706 WHEN 'COL_0716' THEN COL_0716 WHEN 'COL_0726' THEN COL_0726 WHEN 'COL_0736' THEN COL_0736 WHEN 'COL_0740' THEN COL_0740 ELSE 0 END FROM Tableau_de_bord_euro WHERE row_id = @row_id and entry_id3 = @entry_id IF @previous_year <> @year_tmp BEGIN SET @qtd = @qtd + ISNULL(@qtd_year,0) SET @qtd_year = @qtd_tmp SET @previous_year = @year_tmp END ELSE --Si la variable @qtd_tmp n'est pas NULL --IF @qtd_tmp IS NOT NULL SET @qtd_year = @qtd_tmp FETCH NEXT FROM sc_column_cursor INTO @sc_col_id,@year_tmp END CLOSE sc_column_cursor DEALLOCATE sc_column_cursor SET @qtd = @qtd + ISNULL(@qtd_year,0) return @qtd END
Partager