Problèmes avec une procédure SQL server 2000

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:

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

Ci- dessous la fonction exécutée dans la procédure:

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

Merci par avance pour vos retours.

Bonjour,

Il y a plusieurs choses qui sont contre-performantes dans l'expression de vos requêtes :

- les fonctions scalaires sont connues pour être contre-performantes parce qu'elles ne sont pas traitée de façon ensembliste
- un curseur est le pire ennemi d'une requête performante
- vous êtes visiblement la victime d'une mauvaise modélisation de la base de données sur laquelle vous travaillez, et c'est définitivement l'ennemi d'une base de données relationnelle SQL performante.

Rappelons que SQL est un langage :

- Déclaratif, c'est à dire que le développeur ne doit pas se soucier de la façon dont est obtenu le résultat de la requête; il ne guide pas le moteur de base de données : c'est ce dernier qui fait le choix de la meilleure façon de résoudre la requête. La plupart du temps il fait ce choix proprement, mais il a parfois besoin de l'aide d'un DBA.

- Ensembliste, c'est à dire qu'il est conçu et optimisé pour traiter de façon non-itérative un grand nombre de données très rapidement

Donc en supposant que vous ne pouvez pas remettre en question le modèle de données, il faut donc arriver à se passer du curseur, et déplacer le code de la fonction dans la procédure stockée.

La définition de votre curseur étant :
Code:

1 2 3 4 5 6 SELECT sc_col_id , year FROM dbo.SC_COLUMN_MAP WHERE sort_id <= @sort_id AND sort_id % 5 <> 0 ORDER BY sort_id
et comme vous avez besoin d'effectuer IF @previous_year <> @year_tmp, c'est qu'il y a un ordre dans le traitement.
Or encore une fois, l'ordre n'existe pas en SQL, puisque c'est un langage ensembliste.

Est-il possible de réaliser une auto-jointure sur le sort_id, ou year, du style :
Code:

1 2 3 4 5 6 7 8 9 SELECT A.sc_col_id , A.year AS previous_year , B.year AS next_year FROM dbo.SC_COLUMN_MAP AS A INNER JOIN dbo.SC_COLUMN_MAP AS B ON A.sort_id = B.sort_id + 4 WHERE A.sort_id <= @sort_id AND A.sort_id % 5 <> 0 ORDER BY A.sort_id
?

@++ ;)

Bonjour,

Merci pour vos retours.

Effectivement, j'ai un souci avec la modélisation de la base de données. De toute évidence, elle a été mal modélisée. Le fait est que je ne peux la remettre en question. Aussi vos retours me sont d'une grande aide.

Je vais suivre vos orientations, vos conseils et voir ce que cela donnera.

Merci encore.

@++