Optimisation grosse base

Typologie :
- Procédures stockées
- SQL Server 2000

Bonjour à tous et tout d'abord bravo pour vos posts éclairés.

Pour mon premier post, j'ai décidé de placer la barre haut (en fait je n'ai pas vraiment le choix :) )

Voici mon problème :

Pour chaque entrée d'une table pouvant contenir jusqu'à 200 000
entrées je dois insérer plusieurs enregistrements dans une autre table (avec des valeurs calculées à partir des données de la première table).
Pour ce faire je parcours la première table à l'aide d'un curseur LOCAL READ_ONLY et j'appelle des fonctions stockées faites maison pour calculer
les valeurs (4 en tout, basées sur des grosses jointures) que j'insère
dans la nouvelle table.

J'ai alors obtenu des résultats plus ou moins attendus :
- Mon script se traine (estimation à 12h de batch) surtout en raison des
calculs pré-INSERT à partir des valeurs lues dans la table source.

J'ai donc testé mon code et en ai tiré les conclusions suivantes :
- les jointure dans le FROM (tab1 JOIN tab2 ON ...) sont plus lourdes que les simples jointure dans le WHERE (WHERE id1 = id2) !.
Constaté sur des requêtes lourdes avec jointure d'au moins 4 tables bien garnies (environ 250 000 enregistrements).

Mes questions :
- J'entend dire partout que les CURSOR sont contre productifs, mais dans mon cas comment le remplacer ?
- L'appel de 4 fonctions par enregistrement fait-il perdre du temps ?
Devrais-je insérer directement le code de ces fonctions dans une seule grosse procédure (ou bien c'est déjà géré inline)?

Voila, merci d'avance, je vous fait suivre toute autre information que je peu vous communiquer :)

Salut,
effectivement tu places la barre très haut.
Cela dit j'ai déja eu ce genre de problème avec une grosse proc stockée et pas mal de curseur. Je ne sais pas trop à quoi ça ressemble chez toi mais j'ai gagné du temps de manière exponentielle en plaçant des transactions dans ma procédure stockée. Tu devrais essayer de mettre chacun de tes insert dans la table dans une transaction. A mon avis tu devrais retrouver le sommeil ! :D
Enfin, j'espere

Citation:

Mes questions :
1) - J'entend dire partout que les CURSOR sont contre productifs, mais dans mon cas comment le remplacer ?
2) - L'appel de 4 fonctions par enregistrement fait-il perdre du temps ?
3) Devrais-je insérer directement le code de ces fonctions dans une seule grosse procédure (ou bien c'est déjà géré inline)?

1) Sans voir le code impossible de te répondre. Mais dans 95 % des cas en MS SQL Server j'ai supprimé les curseurs et fait en code ensembliste. Cela demande parfois des jours, voire des mois de travail.

2) énormément car dès qu'il y a une fonction et en particulier une UDF il n'y a pas de moyen d'optimiser la requête, le résultat est systématiquement un balayage de toutes les lignes de la table.

3) Oui au minimum, mais si c'est possible une seule requête. Le nombre de tables et de sous requêtes imbriquées dans ce cas n'a pas d'importance. Il m'est arrivé de remplacer il y a 4 ans 8 vues et 9 procédures stockées par une seule requête de 2 000 lignes fasiant appel à 60 tables (souvent les memes), 8 UNION, et 27 sous requêtes dont 9 corrélées. Le temps de traitement est passé de 8h à 12 minutes. Cela m'a demandé quand même une semaine de travail.

A +

En résumé, montrez-nous le DDL de vos tables et votre code

Merci pour ces astuces, je les essaye sur le champ.

Quand à ta remarque fadace, je suis tout à fait d'accord avec toi et je vous aurais déjà posté une bonne partie de mon code si celui-ci n'était pas sensible.

Je vous ferais donc part du progrés de mon travail après appliquation de vos conseils. Dans le cas (peu probable j'en suis sûr :wink: ) où aucune amélioration ne serais en vue, je me débrouillerais pour vous communiquer des parties clef de mon code.

J'ai appliqué vos conseils comme suit :
- J'ai (assez grossièrement d'accord :roll: ) englobé tous le code de ma procédure dans une transaction.
- J'ai intégré le code de mes functions annexes directement dans le code

Et j'ai eu le regret de ne constater que peu d'amélioration (1h de gain total c'est déjà ça merci :D ).

Aussi comme promis je vous fourni le code (un peu cypherisé :? ) le plus exaustif possible de ma procédure :

Définition :

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
 
-- === Aperçu des tables
 
-- == Tables sur lesquelles je ne peu pas opérer de changement :
 
CREATE TABLE dbo.Table_Source(
    TSnumid     int NOT NULL PRIMARY KEY,
    TJ2numid    int NOT NULL FOREIGN KEY REFERENCES Table_Join_2(TJ2numid),
    Champ_1     Money,
    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,
    .
    .
    .
    -- 70 champs en tout (pas de blob ni de varchar ultra long), 200 000 enregistrements en pire cas
)
 
CREATE TABLE dbo.Table_Join_1(
    TJ1numid     int NOT NULL PRIMARY KEY,
    TSnumid      int NOT NULL FOREIGN KEY REFERENCES Table_Source(TSnumid),
    mtyp         smallint NOT NULL,
    mcpt         varchar(20),
    .
    .
    .
    -- 50 champs en tout (pas de champ lourd non plus), 350 000 enregistrements en pire cas
)
 
CREATE TABLE dbo.Table_Join_2(
    TJ2numid     int NOT NULL PRIMARY KEY,
    TJ2effet     varchar(20),
    .
    .
    .
    -- 150 champs en tout (pas de champ lourd), quelques 100aines d'enregistrements en pire cas
)
 
CREATE TABLE dbo.Table_Join_3(
    TJ3numid     int NOT NULL PRIMARY KEY,
    TJ1numid     int NOT NULL FOREIGN KEY REFERENCES Table_Join_1(TJ1numid),
    lettre       smallint NOT NULL,
    .
    .
    .
    -- 6 champs, sous ensemble de Table_Join_1 d'où environ 300 000 enregistrements en pire cas
)
 
 
-- == Table créée pour l'occasion donc altérables :
 
-- Destinée à historiser les données de Table_Source
-- à terme, elle contiendra quelques dixaines de million d'enregistrements.
-- une execution de statHistorisation y créé environ 2 millions d'enregistrements en pire cas
CREATE TABLE dbo.Table_Cible(
    TCnumid              int NOT NULL PRIMARY KEY,
    TSnumid              int NOT NULL REFERENCES dbo.Table_Source(TSnumid),
    TCtype               smallint NOT NULL,
    Tcdate               datetime NOT NULL,
    TCvaleur             varchar(20)
)

La procédure en question (après ajout des modifs) :

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

J'ai résolu mon problème en supprimant toute notion de parcours séquentiel (CURSOR et autre WHILE).

Donc un indice pour ceux que ça intéresse, UTILISEZ DES TABLES TEMPORAIRES. Et surtout abusez de la clause INTO pour les créer.

Voilou, j'éspère que ça aidera :wink: