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 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232
|
-- === Procedure statHistorisation === --
CREATE PROCEDURE statHistorisation
@nbTable_SourceLignes Int = 0 -- Limitation des lignes de Table_Source traitées par le curseur
AS
BEGIN
-- Constantes utilisées pour améliorer la lisibilité (quand elles s'appellent pas CONST_TYPE_Champ_n :) )
DECLARE
@CONST_TYPE_Champ_2 smallint,
@CONST_TYPE_Champ_3 smallint,
@CONST_TYPE_Champ_4 smallint,
@CONST_TYPE_Champ_5 smallint,
@CONST_TYPE_Champ_6 smallint,
@CONST_TYPE_Champ_7 smallint,
@CONST_TYPE_Champ_8 smallint,
@CONST_TYPE_Champ_9 smallint,
@CONST_TYPE_Champ_10 smallint,
@CONST_TYPE_Champ_11 smallint,
@CONST_TYPE_Champ_Calculé_1 smallint,
@CONST_TYPE_Champ_Calculé_2 smallint,
@CONST_TYPE_Champ_Calculé_3 smallint,
@CONST_TYPE_Champ_12 smallint,
@CONST_TYPE_Champ_13 smallint,
@CONST_TYPE_Champ_Calculé_4 smallint
-- Stocke la datetime en cours
DECLARE @currentTime DateTime
-- Stocke la dernière ID attribuée à un enregistrement dans Table_Cible
DECLARE @nextTCid Int
-- Stocke les valeurs de retour des fonction ci-dessus pour les passer en paramètre à insertIntoHR
DECLARE @valeur Money
-- Variables destinées au stockage des résultats de Table_Source_cursor
DECLARE
@Champ_Clef Int,
@Champ_2 Money,
@Champ_3 Money,
@Champ_4 Money,
@Champ_5 Money,
@Champ_6 Money,
@Champ_7 Money,
@Champ_8 Money,
@Champ_9 Money,
@Champ_10 Money,
@Champ_11 Money,
@Champ_12 Money,
@Champ_13 Money
-- Limitation de l'étendue du curseur pour tester plus facilement (sans doute une légère incidence sur les performance à cause du curseur STATIC)
IF @nbTable_SourceLignes > 0 -- Nombre de lignes de Table_Source traitées limité
SET ROWCOUNT @nbTable_SourceLignes
ELSE
SET ROWCOUNT 0 -- Désactive la fonction ROWCOUNT
-- Curseur de parcours des valeurs de Table_Source
DECLARE Table_Source_cursor CURSOR
LOCAL
STATIC -- Nécessaire pour pouvoir le limiter avec ROWCOUNT n
READ_ONLY
FOR
SELECT
Champ_1,
Champ_2,
Champ_3,
Champ_4,
Champ_5,
Champ_6,
Champ_7,
Champ_8,
Champ_9,
Champ_10,
Champ_11,
Champ_12,
Champ_13
FROM Table_Source
--________________ FIN DES DECLARATIONS ____________________--
-- Initialisation des "constantes" de type
-- ATTENTION : CODE A ENTRETENIR
SET @CONST_TYPE_Champ_2 = 1
SET @CONST_TYPE_Champ_3 = 2
SET @CONST_TYPE_Champ_4 = 3
SET @CONST_TYPE_Champ_5 = 4
SET @CONST_TYPE_Champ_6 = 5
SET @CONST_TYPE_Champ_7 = 6
SET @CONST_TYPE_Champ_8 = 7
SET @CONST_TYPE_Champ_9 = 8
SET @CONST_TYPE_Champ_10 = 9
SET @CONST_TYPE_Champ_11 = 10
SET @CONST_TYPE_Champ_Calculé_1 = 11
SET @CONST_TYPE_Champ_Calculé_2 = 12
SET @CONST_TYPE_Champ_Calculé_3 = 13
SET @CONST_TYPE_Champ_12 = 14
SET @CONST_TYPE_Champ_13 = 15
SET @CONST_TYPE_Champ_Calculé_4 = 16
-- recherche du dernier ID attribué à un enregistrement de Table_Cible
SELECT @nextTCid = MAX(TCnumid)
FROM Table_Cible
IF @nextTCid IS NULL
SET @nextTCid = 1
ELSE
SET @nextTCid = @nextTCid + 1
-- Ouverture et lecture du premier enregistrement du curseur sur Table_Source
OPEN Table_Source_cursor
SET ROWCOUNT 0 -- Désactive la fonction ROWCOUNT
FETCH NEXT FROM Table_Source_cursor
INTO
@Champ_Clef,
@Champ_2,
@Champ_3,
@Champ_4,
@Champ_5,
@Champ_6,
@Champ_7,
@Champ_8,
@Champ_9,
@Champ_10,
@Champ_11,
@Champ_12,
@Champ_13
BEGIN TRANSACTION Boucle_Historisation WITH MARK 'Fetch des enregistrements de Table_Source / Insertion des valeurs réccupérées/calculées dans Table_Cible'
WHILE @@FETCH_STATUS = 0
BEGIN
-- datetime des enregistrements liés au TSnumid courant
SET @currentTime = getDate()
-- === Insert des données lues non-nulles
IF @Champ_2 IS NOT NULL
BEGIN
INSERT INTO Table_Cible (TCnumid, TSnumid, TCtype, TCdate, TCvaleur)
VALUES (@nextTCid, @Champ_Clef, @CONST_TYPE_Champ_2, @currentTime, CONVERT(varchar(20), @Champ_2, 2))
SET @nextTCid = @nextTCid + 1
END
IF @Champ_3 IS NOT NULL
BEGIN
INSERT INTO Table_Cible (TCnumid, TSnumid, TCtype, TCdate, TCvaleur)
VALUES (@nextTCid, @Champ_Clef, @CONST_TYPE_Champ_3, @currentTime, CONVERT(varchar(20), @Champ_3, 2))
SET @nextTCid = @nextTCid + 1
END
.
.
.
IF @Champ_13 IS NOT NULL
BEGIN
INSERT INTO Table_Cible (TCnumid, TSnumid, TCtype, TCdate, TCvaleur)
VALUES (@nextTCid, @Champ_Clef, @CONST_TYPE_Champ_12, @currentTime, CONVERT(varchar(20), @Champ_13, 2))
SET @nextTCid = @nextTCid + 1
END
-- === Calcul/Insert des données calculées
-- __ Champ_Calculé_1
-- ___ Reccupération
-- Version qui parait, d'un point de vue théorique, la plus efficace
SELECT @valeur = SUM(Val1 - Val2)
FROM Table_Join_1 TJ1
JOIN Table_Source TS
ON TS.TSnumid = TJ1.TSnumid
JOIN Table_Join_2 TJ2
ON TS.TJ2numid = TJ2.TJ2numid
LEFT OUTER JOIN Table_Join_3
ON TJ1.TJ1numid = TJ3.TJ1numid
WHERE (TJ1.mtyp=7 OR TJ1.mcpt LIKE TJ2.TJ2effet + '%')
AND (TJ3.lettre in (0,4,5,6) OR TJ3.lettre IS NULL)
AND TJ1.TSnumid = @Champ_Clef
-- Version "Optimisée" par mes soin après tests de la première version
-- OPTIMISATIONS :
-- - Les JOIN sur table à gros volume dans MS SQL Server semblent moins efficace que les simples WHERE clef=clef
-- - Idem pour les LEFT OUTER JOIN, plus difficile à remplacer, voir 2 dernière lignes
-- - le '*' des EXISTS et NOT EXISTS est volontaire car plus rapide
SELECT @valeur = SUM(Val1 - Val2)
FROM Table_Join_1 TJ1, Table_Source TS, Table_Join_2 TJ2
WHERE TS.TSnumid = TJ1.TSnumid
AND TS.TJ2numid = TJ2.TJ2numid
AND TJ1.TSnumid = @Champ_Clef
AND (TJ1.mtyp=7 OR TJ1.mcpt LIKE TJ2.TJ2effet + '%')
AND (NOT EXISTS (SELECT * FROM Table_Join_3 WHERE TJ1numid = TJ1.TJ1numid) -- Remplacement de LEFT OUTER JOIN Table_Join_3
OR EXISTS (SELECT * FROM Table_Join_3 TJ3 WHERE TJ1numid = TJ1.TJ1numid AND TJ3.lettre IN (0,4,5,6)))
-- ___ Enregistrement
IF @valeur IS NOT NULL
BEGIN
INSERT INTO Table_Cible (TCnumid, TSnumid, TCtype, TCdate, TCvaleur)
VALUES (@nextTCid, @Champ_Clef, @CONST_TYPE_Champ_Calculé_2, @currentTime, CONVERT(varchar(20), @valeur, 2))
SET @nextTCid = @nextTCid + 1
END
.
. -- Requêtes du même genre pour Champ_calculé_[2, 3, 4]
.
-- === Fetch de la prochaine ligne
FETCH NEXT FROM Table_Source_cursor
INTO
@Champ_Clef,
@Champ_2,
@Champ_3,
@Champ_4,
@Champ_5,
@Champ_6,
@Champ_7,
@Champ_8,
@Champ_9,
@Champ_10,
@Champ_11,
@Champ_12,
@Champ_13
END -- WHILE
COMMIT TRANSACTION Boucle_Historisation
CLOSE Table_Source_cursor
DEALLOCATE Table_Source_cursor
END -- statHistorisation
GO |
Partager