Optimisation de code T-SQL (DEBUTANT)

**sono_strass** · 30/06/2011, 12h46

Bonjour à tous,

Je suis grand débutant en T-SQL et j'ai tenté de réaliser un script un peu compliqué. J'ai l'impression qu'on peut faire mieux en terme d'optimisation, mon absence de maitrise me limitant un peu. J'en appelle donc à la communauté pour m'aider à rendre mon proc + efficace.

Objet du script:
Je pars d'une table qui contient des sociétés, des produits, des lieux de stocks, des quantité et des prix moyens pondérés (PMP).

Dans ma table source, j'ai une nouvelle ligne à chaque fois que la quantité ou le PMP change.
Mon objectif est de rétablir une granularité mensuelle dans ma table finale en respectant les règles suivantes:
- si j'ai plusieurs lignes pour un produit, une société un lieu et un mois, je prend les valeurs de la ligne la plus récente du mois.
- si j'ai plusieurs mois d'écart entre 2 lignes d'un même produit, je reconstitue les mois manquants en m'appuyant sur la dernière occurence connue

Je dois donc systématiquement comparer la ligne en cours la ligne précédente de ma source pour savoir comment me comporter.

J'en suis arrivé au code 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
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
159
160
161
162
163
 
 
-- Création de la table cible
 
/*
CREATE TABLE dmat_achstk.F_STOCK_MERG_VALO_NORM (
CODE_SOCIETE varchar(3) not null,
NUM_PRODUIT int not null,
CODE_LIEU varchar(5) not null,
ANNEE_MOIS_VALO int not null,
QTE_VALO decimal(18,7) null,
PMP Decimal(18,7) null
)
 
-- Création de la clé
ALTER TABLE [dmat_achstk].[F_STOCK_MERG_VALO_NORM] 
ADD CONSTRAINT [PK_F_STOCK_MERG_VALO_NORM] PRIMARY KEY CLUSTERED (
	CODE_SOCIETE,
	NUM_PRODUIT,
	CODE_LIEU,
	ANNEE_MOIS_VALO asc
)
 
*/
 
 
TRUNCATE TABLE dmat_achstk.F_STOCK_MERG_VALO_NORM
 
 
DECLARE		@New_Produit int, 
			@New_Societe varchar(3), 
			@New_Lieu varchar(5), 
			@New_Date int, 
			@New_Qte_Val decimal(18,7), 
			@New_PMP decimal(18,7), 
			@New_AnneeMois_Valo int, 
			@New_AnneeMois_Valo_Next int
 
DECLARE		@Last_Produit int, 
			@Last_Societe varchar(3), 
			@Last_Lieu varchar(5), 
			@Last_Date int, 
			@Last_Qte_Val decimal(18,7), 
			@Last_PMP decimal(18,7), 
			@Last_AnneeMois_Valo int,
			@Last_AnneeMois_Valo_Next int
 
Declare Valorisation CURSOR FOR
	SELECT 
		CODE_SOCIETE, 
		COLIGEOG,
		NUMPRODUIT,
		DATE_VALO,
		PMP,
		QTESTOCK,
		CEILING(DATE_VALO/100) ANNEEMOIS_VALO,
		(CASE WHEN substring(cast(DATE_VALO as varchar(8)), 5,2)='12' THEN CEILING(DATE_VALO/100)+89 ELSE CEILING(DATE_VALO/100)+ 1 END) ANNEEMOIS_VALO_NEXT
	FROM dmat_achstk.F_STOCK_MERG_VALO
	ORDER BY 
		CODE_SOCIETE, 
		COLIGEOG,
		NUMPRODUIT,
		DATE_VALO asc
 
BEGIN
 
	OPEN Valorisation
	FETCH NEXT FROM Valorisation INTO @New_Societe, @New_Lieu, @New_Produit, @New_Date, @New_PMP, @New_Qte_Val, @New_AnneeMois_Valo,  @New_AnneeMois_Valo_Next;
		SET @Last_Produit				= @New_Produit
		SET @Last_Societe				= @New_Societe
		SET @Last_Lieu					= @New_Lieu
		SET @Last_Date					= @New_Date
		SET @Last_Qte_Val				= @New_Qte_Val
		SET @Last_PMP					= @New_PMP
		SET @Last_AnneeMois_Valo		= @New_AnneeMois_Valo
		SET @Last_AnneeMois_Valo_Next	= @New_AnneeMois_Valo_Next
 
	FETCH NEXT FROM Valorisation INTO @New_Societe, @New_Lieu, @New_Produit, @New_Date, @New_PMP, @New_Qte_Val, @New_AnneeMois_Valo,  @New_AnneeMois_Valo_Next;
 
	WHILE @@FETCH_STATUS = 0
		BEGIN
 
	-- Si l'on change de Société, de produit ou de lieu, on insère la dernière ligne du triplet Société/Produit/Lieu précédent
			IF @New_Produit <> @Last_Produit OR @New_Societe <> @Last_Societe OR @New_Lieu <> @Last_Lieu
				BEGIN
				INSERT INTO dmat_achstk.F_STOCK_MERG_VALO_NORM VALUES (@Last_Societe, @Last_Produit, @Last_Lieu, @Last_AnneeMois_Valo, @Last_Qte_Val, @Last_PMP)
				SET @Last_Produit				= @New_Produit
				SET @Last_Societe				= @New_Societe
				SET @Last_Lieu					= @New_Lieu
				SET @Last_Date					= @New_Date
				SET @Last_Qte_Val				= @New_Qte_Val
				SET @Last_PMP					= @New_PMP
				SET @Last_AnneeMois_Valo		= @New_AnneeMois_Valo
				SET @Last_AnneeMois_Valo_Next	= @New_AnneeMois_Valo_Next
				END
 
 
	-- Si l'on reste sur le même produit/lieu/société pour le même mois, on ne change que la quantité valorisable et le PMP
			ELSE IF @New_Produit = @Last_Produit AND @New_Societe = @Last_Societe AND @New_Lieu = @Last_Lieu AND @Last_AnneeMois_Valo = @New_AnneeMois_Valo
				BEGIN
				SET @Last_Qte_Val				= @New_Qte_Val
				SET @Last_PMP					= @New_PMP
				END
 
 
	-- Si l'on change de mois, la ligne précédente était la dernière occurence du mois précédent: on l'insère.
			ELSE IF @New_Produit = @Last_Produit AND @New_Societe = @Last_Societe AND @New_Lieu = @Last_Lieu AND @New_AnneeMois_Valo = @Last_AnneeMois_Valo_Next
				BEGIN
				INSERT INTO dmat_achstk.F_STOCK_MERG_VALO_NORM VALUES (@Last_Societe, @Last_Produit, @Last_Lieu, @Last_AnneeMois_Valo, @Last_Qte_Val, @Last_PMP)
				SET @Last_Produit				= @New_Produit
				SET @Last_Societe				= @New_Societe
				SET @Last_Lieu					= @New_Lieu
				SET @Last_Date					= @New_Date
				SET @Last_Qte_Val				= @New_Qte_Val
				SET @Last_PMP					= @New_PMP
				SET @Last_AnneeMois_Valo		= @New_AnneeMois_Valo
				SET @Last_AnneeMois_Valo_Next	= @New_AnneeMois_Valo_Next
				END
 
 
	-- Si l'on change de mois, mais qu'il y a plus d'un mois d'écart par rapport à la dernière occurence du mois précédent
			ELSE IF @New_Produit = @Last_Produit AND @New_Societe = @Last_Societe AND @New_Lieu = @Last_Lieu AND @New_AnneeMois_Valo > @Last_AnneeMois_Valo_Next
				BEGIN
	-- On positionne la dernière occurence du mois précédent au mois suivant qu'on insère, jusqu'à atteindre le mois attendu
				WHILE @New_AnneeMois_Valo > @Last_AnneeMois_Valo_Next
					BEGIN	
					SET @Last_AnneeMois_Valo = (
						CASE 
							WHEN substring(cast(@Last_AnneeMois_Valo as varchar(6)), 5,2)='12' 
							THEN CEILING(@Last_AnneeMois_Valo/100)+89 
							ELSE CEILING(@Last_AnneeMois_Valo/100)+ 1 
						END
					)
					SET @Last_AnneeMois_Valo_Next = (
						CASE 
							WHEN substring(cast(@Last_AnneeMois_Valo_Next as varchar(6)), 5,2)='12' 
							THEN CEILING(@Last_AnneeMois_Valo_Next/100)+89 
							ELSE CEILING(@Last_AnneeMois_Valo_Next/100)+ 1 
						END	 	 
					)
					INSERT INTO dmat_achstk.F_STOCK_MERG_VALO_NORM VALUES (@Last_Societe, @Last_Produit, @Last_Lieu, @Last_AnneeMois_Valo, @Last_Qte_Val, @Last_PMP)
					END
 
				SET @Last_Produit				= @New_Produit
				SET @Last_Societe				= @New_Societe
				SET @Last_Lieu					= @New_Lieu
				SET @Last_Date					= @New_Date
				SET @Last_Qte_Val				= @New_Qte_Val
				SET @Last_PMP					= @New_PMP
				SET @Last_AnneeMois_Valo		= @New_AnneeMois_Valo
				SET @Last_AnneeMois_Valo_Next	= @New_AnneeMois_Valo_Next
				END
 
 
		FETCH NEXT FROM Valorisation INTO @New_Societe, @New_Lieu, @New_Produit, @New_Date, @New_PMP, @New_Qte_Val, @New_AnneeMois_Valo,  @New_AnneeMois_Valo_Next;
		END
 
	-- On insère la dernière ligne remontée
	INSERT INTO dmat_achstk.F_STOCK_MERG_VALO_NORM VALUES (@Last_Societe, @Last_Produit, @Last_Lieu, @Last_AnneeMois_Valo, @Last_Qte_Val, @Last_PMP)
 
	CLOSE Valorisation 
	DEALLOCATE Valorisation
END

Mais j'ai l'impression que ça ne fonctionne pas très bien.

Pourriez-vous m'aider à nettoyer ce script?

Merci

sOnO
Heavy beginner

**sono_strass** · 30/06/2011, 13h09

CORRECTION:

J'ai oublié un petit bout de code. Je remet donc la version à jour.

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
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
159
160
161
162
163
164
165
 
 
 
-- Création de la table cible
/*
CREATE TABLE dmat_achstk.F_STOCK_MERG_VALO_NORM (
CODE_SOCIETE varchar(3) not null,
NUM_PRODUIT int not null,
CODE_LIEU varchar(5) not null,
ANNEE_MOIS_VALO int not null,
QTE_VALO decimal(18,7) null,
PMP Decimal(18,7) null
)
 
-- Création de la clé
ALTER TABLE [dmat_achstk].[F_STOCK_MERG_VALO_NORM] 
ADD CONSTRAINT [PK_F_STOCK_MERG_VALO_NORM] PRIMARY KEY CLUSTERED (
	CODE_SOCIETE,
	NUM_PRODUIT,
	CODE_LIEU,
	ANNEE_MOIS_VALO asc
)
 
*/
 
 
TRUNCATE TABLE dmat_achstk.F_STOCK_MERG_VALO_NORM
 
 
DECLARE		@New_Produit int, 
			@New_Societe varchar(3), 
			@New_Lieu varchar(5), 
			@New_Date int, 
			@New_Qte_Val decimal(18,7), 
			@New_PMP decimal(18,7), 
			@New_AnneeMois_Valo int, 
			@New_AnneeMois_Valo_Next int
 
DECLARE		@Last_Produit int, 
			@Last_Societe varchar(3), 
			@Last_Lieu varchar(5), 
			@Last_Date int, 
			@Last_Qte_Val decimal(18,7), 
			@Last_PMP decimal(18,7), 
			@Last_AnneeMois_Valo int,
			@Last_AnneeMois_Valo_Next int
 
Declare Valorisation CURSOR FOR
	SELECT 
		CODE_SOCIETE, 
		COLIGEOG,
		NUMPRODUIT,
		DATE_VALO,
		PMP,
		QTESTOCK,
		CEILING(DATE_VALO/100) ANNEEMOIS_VALO,
		(CASE WHEN substring(cast(DATE_VALO as varchar(8)), 5,2)='12' THEN CEILING(DATE_VALO/100)+89 ELSE CEILING(DATE_VALO/100)+ 1 END) ANNEEMOIS_VALO_NEXT
	FROM dmat_achstk.F_STOCK_MERG_VALO
	ORDER BY 
		CODE_SOCIETE, 
		COLIGEOG,
		NUMPRODUIT,
		DATE_VALO asc
 
BEGIN
 
	OPEN Valorisation
	FETCH NEXT FROM Valorisation INTO @New_Societe, @New_Lieu, @New_Produit, @New_Date, @New_PMP, @New_Qte_Val, @New_AnneeMois_Valo,  @New_AnneeMois_Valo_Next;
		SET @Last_Produit				= @New_Produit
		SET @Last_Societe				= @New_Societe
		SET @Last_Lieu					= @New_Lieu
		SET @Last_Date					= @New_Date
		SET @Last_Qte_Val				= @New_Qte_Val
		SET @Last_PMP					= @New_PMP
		SET @Last_AnneeMois_Valo		= @New_AnneeMois_Valo
		SET @Last_AnneeMois_Valo_Next	= @New_AnneeMois_Valo_Next
 
	FETCH NEXT FROM Valorisation INTO @New_Societe, @New_Lieu, @New_Produit, @New_Date, @New_PMP, @New_Qte_Val, @New_AnneeMois_Valo,  @New_AnneeMois_Valo_Next;
 
	WHILE @@FETCH_STATUS = 0
		BEGIN
 
	-- Si l'on change de Société, de produit ou de lieu, on insère la dernière ligne du triplet Société/Produit/Lieu précédent
			IF @New_Produit <> @Last_Produit OR @New_Societe <> @Last_Societe OR @New_Lieu <> @Last_Lieu
				BEGIN
				INSERT INTO dmat_achstk.F_STOCK_MERG_VALO_NORM VALUES (@Last_Societe, @Last_Produit, @Last_Lieu, @Last_AnneeMois_Valo, @Last_Qte_Val, @Last_PMP)
				SET @Last_Produit				= @New_Produit
				SET @Last_Societe				= @New_Societe
				SET @Last_Lieu					= @New_Lieu
				SET @Last_Date					= @New_Date
				SET @Last_Qte_Val				= @New_Qte_Val
				SET @Last_PMP					= @New_PMP
				SET @Last_AnneeMois_Valo		= @New_AnneeMois_Valo
				SET @Last_AnneeMois_Valo_Next	= @New_AnneeMois_Valo_Next
				END
 
 
	-- Si l'on reste sur le même produit/lieu/société pour le même mois, on ne change que la quantité valorisable et le PMP
			ELSE IF @New_Produit = @Last_Produit AND @New_Societe = @Last_Societe AND @New_Lieu = @Last_Lieu AND @Last_AnneeMois_Valo = @New_AnneeMois_Valo
				BEGIN
				SET @Last_Qte_Val				= @New_Qte_Val
				SET @Last_PMP					= @New_PMP
				END
 
 
	-- Si l'on change de mois, la ligne précédente était la dernière occurence du mois précédent: on l'insère.
			ELSE IF @New_Produit = @Last_Produit AND @New_Societe = @Last_Societe AND @New_Lieu = @Last_Lieu AND @New_AnneeMois_Valo = @Last_AnneeMois_Valo_Next
				BEGIN
				INSERT INTO dmat_achstk.F_STOCK_MERG_VALO_NORM VALUES (@Last_Societe, @Last_Produit, @Last_Lieu, @Last_AnneeMois_Valo, @Last_Qte_Val, @Last_PMP)
				SET @Last_Produit				= @New_Produit
				SET @Last_Societe				= @New_Societe
				SET @Last_Lieu					= @New_Lieu
				SET @Last_Date					= @New_Date
				SET @Last_Qte_Val				= @New_Qte_Val
				SET @Last_PMP					= @New_PMP
				SET @Last_AnneeMois_Valo		= @New_AnneeMois_Valo
				SET @Last_AnneeMois_Valo_Next	= @New_AnneeMois_Valo_Next
				END
 
 
	-- Si l'on change de mois, mais qu'il y a plus d'un mois d'écart par rapport à la dernière occurence du mois précédent
			ELSE IF @New_Produit = @Last_Produit AND @New_Societe = @Last_Societe AND @New_Lieu = @Last_Lieu AND @New_AnneeMois_Valo > @Last_AnneeMois_Valo_Next
				BEGIN
	-- On positionne la dernière occurence du mois précédent au mois suivant qu'on insère, jusqu'à atteindre le mois attendu
 
				INSERT INTO dmat_achstk.F_STOCK_MERG_VALO_NORM VALUES (@Last_Societe, @Last_Produit, @Last_Lieu, @Last_AnneeMois_Valo, @Last_Qte_Val, @Last_PMP)
				WHILE @New_AnneeMois_Valo > @Last_AnneeMois_Valo_Next
					BEGIN	
					SET @Last_AnneeMois_Valo = (
						CASE 
							WHEN substring(cast(@Last_AnneeMois_Valo as varchar(6)), 5,2)='12' 
							THEN @Last_AnneeMois_Valo+89 
							ELSE @Last_AnneeMois_Valo+ 1 
						END
					)
					SET @Last_AnneeMois_Valo_Next = (
						CASE 
							WHEN substring(cast(@Last_AnneeMois_Valo_Next as varchar(6)), 5,2)='12' 
							THEN @Last_AnneeMois_Valo_Next+89 
							ELSE @Last_AnneeMois_Valo_Next+ 1 
						END	 	 
					)
					INSERT INTO dmat_achstk.F_STOCK_MERG_VALO_NORM VALUES (@Last_Societe, @Last_Produit, @Last_Lieu, @Last_AnneeMois_Valo, @Last_Qte_Val, @Last_PMP)
					END
 
				SET @Last_Produit				= @New_Produit
				SET @Last_Societe				= @New_Societe
				SET @Last_Lieu					= @New_Lieu
				SET @Last_Date					= @New_Date
				SET @Last_Qte_Val				= @New_Qte_Val
				SET @Last_PMP					= @New_PMP
				SET @Last_AnneeMois_Valo		= @New_AnneeMois_Valo
				SET @Last_AnneeMois_Valo_Next	= @New_AnneeMois_Valo_Next
				END
 
 
		FETCH NEXT FROM Valorisation INTO @New_Societe, @New_Lieu, @New_Produit, @New_Date, @New_PMP, @New_Qte_Val, @New_AnneeMois_Valo,  @New_AnneeMois_Valo_Next;
		END
 
	-- On insère la dernière ligne remontée
	INSERT INTO dmat_achstk.F_STOCK_MERG_VALO_NORM VALUES (@Last_Societe, @Last_Produit, @Last_Lieu, @Last_AnneeMois_Valo, @Last_Qte_Val, @Last_PMP)
 
	CLOSE Valorisation 
	DEALLOCATE Valorisation
END

**aieeeuuuuu** · 30/06/2011, 13h37

Bonjour,

En effet, je pense qu'il est possible d'optimiser tout ça, en commençant par se passer du curseur.

Envoyé par sono_strass

- si j'ai plusieurs lignes pour un produit, une société un lieu et un mois, je prend les valeurs de la ligne la plus récente du mois.

Pour cela, vous pouvez utiliser les fonctions de fenetrage.
Quelle est votre version de SQL Server ?

Envoyé par sono_strass

- si j'ai plusieurs mois d'écart entre 2 lignes d'un même produit, je reconstitue les mois manquants en m'appuyant sur la dernière occurence connue

La il vous faudrait surement un calendrier. Je vous conseille de lire cet article de SQLPro...

Je dois donc systématiquement comparer la ligne en cours la ligne précédente de ma source pour savoir comment me comporter.

ROW_NUMBER peut vous être utile pour faire une auto-jointure avec "une ligne de décalage"

Mais j'ai l'impression que ça ne fonctionne pas très bien.

C'est à dire ? les performances ne sont pas celle attendues, ou est-ce le résultat qui n'est pas celui attendu ?

Pouvez-vous nous fournir un jeu d'essai avec le résultat attendu ?

Optimisation de code T-SQL (DEBUTANT)

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager