Procédure stockée, Multithread

**Pfeffer** · 26/04/2020, 14h35

Bonjour à tous,

J'ai une procédure stockée qui génère des tables temporaires et qui y intègre des données de manières légères (quelques lignes).
Cette procédure stockée est lancée en multi-thread depuis un programme .NET. Je lance jusqu'à 20 threads simultanément et au total je lance approximativement 10000x fois cette procédure. Tout fonctionne bien, de bonne performance jusqu'à 70% du traitement global ou là tout se dégrade, les performances deviennent exécrables.
En y regardant de plus près dans le moniteur d'activité, j'ai ceci Nom : Sans titre2.png
Affichages : 226
Taille : 38,4 Ko

Nom : Sans titre2.png
Affichages : 226
Taille : 38,4 Ko

D’où ça peut provenir ? La tempDB ? Je précise qu'elle se situe sur un autre disque que la DATA.
Dans ma procédure stockée, je ne DROP pas les tables temporaires je laisse faire SQLServer, est-ce que c'est ça le problème ?
Comment je peux essayer de tracker le goulot d'étranglement ?

Merci à tous.

**SQLpro** · 27/04/2020, 13h18

CXPACKET sont des attentes liées au parallélisme interne de SQL Server.... Parce que une même requête SQL Server peut utiliser plusieurs threads.
Les blocages concernant visiblement des INSERTs ce qui est normal s'ils vident la même table....

Tout dépend donc ce que fait votre code..... Si tout vos threaeds font de l'INSERT au final, il seront mis en série.....

Décrivez votre processus de manière détaillé. Je pense que vous avez conçu un "monstre" dans le sens ou ce qu'il faut faire est de laisser gérer la charge de traitement pas le serveur et d'éviter de le faire côté applicatif !

A +

**Pfeffer** · 27/04/2020, 14h12

Voici la procédure stockée que j'exécute dans des threads différents.
Ce que je ne comprends pas c'est pourquoi tout ralenti à partir de 70%, c'est comme si la charge devenait trop importante à partir de ce moment là.
J'ai essayé explicitement de faire le DROP de mes tables temporaires, cela ne change rien, même effet.

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
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
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
 
--- <Description>
--- Version du: 26/11/2019
--- Procédure permettant de vérifier la consolidation entre [gtm_crb], [gtm_crb_consolide_pt10], [gtm_crb_daily], [gtm_crb_monthly]
--- Si une différence est constatée, une écriture se fait dans la table [gtm_crb_consolide_pt10_checkresult]
--- </Description>
 
CREATE PROCEDURE [dbo].[sp_check_gtm_crb_consolide] @dtStartParam        DATETIME,
                                                       @dtEndParam          DATETIME,
                                                       @cpt_id              INT,
                                                       @sLoginUser          VARCHAR (50),
                                                       @checkResultId       INT = NULL OUTPUT
   WITH
   EXEC AS CALLER
AS
DECLARE @sTableName   VARCHAR (25);
DECLARE @sSQLRequest   NVARCHAR (MAX);
DECLARE @ParmDefinition   NVARCHAR (500);
 
BEGIN TRY
   -- Nom de la table GTM_crb
   -- ------------------------
   SET @sTableName =
          'gtm_crb' + right ('00000' + CONVERT (VARCHAR (20), @cpt_id), 5)
 
 
   CREATE TABLE #crb_table
   (
      type_pt_id    INTEGER NOT NULL,
      volume_crb    FLOAT NOT NULL,
      count         INT NOT NULL
   )
 
   CREATE TABLE #consolide_pt10
   (
      type_pt_id                   INTEGER NOT NULL,
      volume_crb_consolide_pt10    FLOAT NOT NULL
   )
 
   CREATE TABLE #crb_daily
   (
      type_pt_id          INTEGER NOT NULL,
      volume_crb_daily    FLOAT NOT NULL
   )
 
   CREATE TABLE #crb_monthly
   (
      type_pt_id            INTEGER NOT NULL,
      volume_crb_monthly    FLOAT NOT NULL
   )
 
   CREATE TABLE #check_consolidation
   (
      compteur_id                                  INT NOT NULL,
      volume_crb                                   FLOAT NULL,
      volume_crb_daily                             FLOAT NULL,
      volume_crb_consolide_pt10                    FLOAT NULL,
      vol_crb_monthly                              FLOAT NULL,
      vol_diff_gtm_crb_daily                       FLOAT NULL,
      vol_diff_gtm_crb_consolide_pt10              FLOAT NULL,
      vol_diff_gtm_crb_daily_crb_consolide_pt10    FLOAT NULL,
      vol_diff_gtm_crb_monthly                     FLOAT NULL,
      vol_diff_gtm_crb_consolide_pt10_monthly      FLOAT NULL,
      vol_diff_gtm_crb_daily_crb_monthly           FLOAT NULL,
      type_pt_id                                   INT NULL,
      dtstartparam                                 DATETIME NOT NULL,
      dtendparam                                   DATETIME NOT NULL,
      nbpointtoconsolide                           INT NULL,      
      loginUser                                    VARCHAR (100)
   )
 
   DECLARE @volGTMCrb   DECIMAL (15, 5)
   SET @volGTMCrb = 0
 
   DECLARE @iNbRecordToConsolide   INTEGER
   SET @iNbRecordToConsolide = 0
 
   -- Récupération des données sur la table GTM_CRB
   -- ---------------------------------------------
   SET @sSQLRequest =
            'INSERT INTO #crb_table (type_pt_id, volume_crb, [count])'
          + ' SELECT type_pt_id, ISNULL (sum (puissance) / 6, 0) AS volume,  count (*) AS count'
          + ' FROM '
          + @sTableName
          + ' as c0'
          + ' WHERE c0.datepoint BETWEEN @dtStartParam AND dateadd(day,1, @dtEndParam)'
          + ' AND c0.version = (SELECT MAX(c1.version) FROM '
          + @sTableName
          + ' c1 WHERE c0.type_pt_id = c1.type_pt_id'
          + ' AND c0.datepoint = c1.datepoint AND c0.compteur_id=c1.compteur_id)'
          + ' AND c0.type_pt_id in (select type_pt_id from gtm_type_preference where lot_id = 2)'
          + ' GROUP BY c0.type_pt_id'
   SET @ParmDefinition = N'@dtStartParam DATETIME, @dtEndParam DATETIME';
 
   -- Exécution de la requête
   -- -----------------------
   EXEC
      sp_executesql @sSQLRequest,
      @ParmDefinition,
      @dtStartParam   = @dtStartParam,
      @dtEndParam     = @dtEndParam
 
   -- Récupération des données sur la table GTM_CRB_DAILY
   -- ---------------------------------------------------
   DECLARE @volGTMCrbDaily   DECIMAL (15, 5)
   SET @volGTMCrbDaily = 0
   SET @sSQLRequest =
            'INSERT INTO #crb_daily (type_pt_id, volume_crb_daily)'
          + ' SELECT type_pt_id, ISNULL (sum (volume), 0) AS volume'
          + ' FROM gtm_crb_daily'
          + ' WHERE datepoint BETWEEN CONVERT (DATE, @dtStartParam, 103) AND CONVERT (DATE, @dtEndParam, 103)'
          + ' AND compteur_id = @cpt_id'
          + ' AND mixte = 0'
          + ' AND type_pt_id in (select type_pt_id from gtm_type_preference where lot_id = 2)'
          + ' GROUP BY type_pt_id'
   SET @ParmDefinition =
          N'@dtStartParam DATETIME, @dtEndParam DATETIME, @cpt_id INT';
 
   -- Exécution de la requête
   -- -----------------------
   EXEC
      sp_executesql @sSQLRequest,
      @ParmDefinition,
      @dtStartParam   = @dtStartParam,
      @dtEndParam     = @dtEndParam,
      @cpt_id         = @cpt_id
 
   -- Récupération des données sur la table GTM_CRB_MONTHLY
   -- ---------------------------------------------------
   DECLARE @volGTMCrbMonthly   DECIMAL (15, 5)
   SET @volGTMCrbMonthly = 0
   SET @sSQLRequest =
            'INSERT INTO #crb_monthly (type_pt_id, volume_crb_monthly)'
          + ' SELECT type_pt_id, ISNULL (sum (volume), 0) AS volume'
          + ' FROM gtm_crb_monthly'
          + ' WHERE year between year(@dtStartParam) and year(@dtEndParam) AND [month] between month(@dtStartParam) and month(@dtEndParam)'
          + ' AND compteur_id = @cpt_id'
          + ' AND mixte = 0'
          + ' AND type_pt_id in (select type_pt_id from gtm_type_preference where lot_id = 2)'
          + ' GROUP BY type_pt_id'
   SET @ParmDefinition =
          N'@dtStartParam DATETIME, @dtEndParam DATETIME, @cpt_id INT';
 
   -- Exécution de la requête
   -- -----------------------
   EXEC
      sp_executesql @sSQLRequest,
      @ParmDefinition,
      @dtStartParam   = @dtStartParam,
      @dtEndParam     = @dtEndParam,
      @cpt_id         = @cpt_id
 
   -- Récupération des données sur la table GTM_CRB_CONSOLIDE_PT10
   -- ---------------------------------------------------
   DECLARE @volGTMCrbConsolidePt10   DECIMAL (15, 5)
   SET @volGTMCrbConsolidePt10 = 0
 
   DECLARE @iNbRecordConsolided   INTEGER
   SET @iNbRecordConsolided = 0
 
   SET @sSQLRequest =
            'INSERT INTO #consolide_pt10 (type_pt_id, volume_crb_consolide_pt10)'
          + ' SELECT type_pt_id, ISNULL (sum (puissance) / 6,0) AS volume'
          + ' FROM gtm_crb_consolide_pt10'
          + ' WHERE datepoint BETWEEN @dtStartParam AND dateadd(day,1, @dtEndParam)'
          + ' AND compteur_id = @cpt_id'
          + ' AND mixte = 0'
          + ' AND type_pt_id in (select type_pt_id from gtm_type_preference where lot_id = 2)'
          + ' GROUP BY type_pt_id'
   SET @ParmDefinition =
          N'@dtStartParam DATETIME, @dtEndParam DATETIME, @cpt_id INT';
 
   -- Exécution de la requête
   -----------------------
   EXEC
      sp_executesql @sSQLRequest,
      @ParmDefinition,
      @dtStartParam   = @dtStartParam,
      @dtEndParam     = @dtEndParam,
      @cpt_id         = @cpt_id
 
   INSERT INTO #check_consolidation (
                  compteur_id,
                  dtstartparam,
                  dtendparam,
                  loginUser,
                  type_pt_id,
                  volume_crb,
                  volume_crb_consolide_pt10,
                  volume_crb_daily,
                  vol_crb_monthly,
                  nbpointtoconsolide,
                  vol_diff_gtm_crb_daily,
                  vol_diff_gtm_crb_consolide_pt10,
                  vol_diff_gtm_crb_daily_crb_consolide_pt10,
                  vol_diff_gtm_crb_monthly,
                  vol_diff_gtm_crb_consolide_pt10_monthly,
                  vol_diff_gtm_crb_daily_crb_monthly)
      SELECT @cpt_id
                AS compteur_id,
             @dtStartParam
                AS dtstartparam,
             @dtEndParam
                AS dtendparam,
             @sLoginUser
                AS loginUser,
             crb.type_pt_id
                AS type_pt_id,
             ISNULL (round (crb.volume_crb, 3), 0)
                AS volume_crb,
             ISNULL (round (pt10.volume_crb_consolide_pt10, 3), 0)
                AS volume_crb_consolide_pt10,
             ISNULL (round (d.volume_crb_daily, 3), 0)
                AS volume_crb_daily,
             ISNULL (round (m.volume_crb_monthly, 3), 0)
                AS volume_crb_monthly,
             crb.[count]
                AS nbpointtoconsolide,
               ISNULL (round (crb.volume_crb, 3), 0)
             - ISNULL (round (d.volume_crb_daily, 3), 0)
                AS vol_diff_gtm_crb_daily,
               ISNULL (round (crb.volume_crb, 3), 0)
             - ISNULL (round (pt10.volume_crb_consolide_pt10, 3), 0)
                AS vol_diff_gtm_crb_consolide_pt10,
               ISNULL (round (d.volume_crb_daily, 3), 0)
             - ISNULL (round (pt10.volume_crb_consolide_pt10, 3), 0)
                AS vol_diff_gtm_crb_daily_crb_consolide_pt10,
               ISNULL (round (crb.volume_crb, 3), 0)
             - ISNULL (round (m.volume_crb_monthly, 3), 0)
                AS vol_diff_gtm_crb_monthly,
               ISNULL (round (pt10.volume_crb_consolide_pt10, 3), 0)
             - ISNULL (round (m.volume_crb_monthly, 3), 0)
                AS vol_diff_gtm_crb_consolide_pt10_monthly,
               ISNULL (round (d.volume_crb_daily, 3), 0)
             - ISNULL (round (m.volume_crb_monthly, 3), 0)
                AS vol_diff_gtm_crb_daily_crb_monthly
      FROM #crb_table crb
           FULL OUTER JOIN #consolide_pt10 pt10
              ON pt10.type_pt_id = crb.type_pt_id
           FULL OUTER JOIN #crb_daily d ON d.type_pt_id = crb.type_pt_id
           FULL OUTER JOIN #crb_monthly m ON m.type_pt_id = crb.type_pt_id
 
   IF EXISTS
         (SELECT *
          FROM #check_consolidation
          WHERE    abs(vol_diff_gtm_crb_daily) > 0.0011
                OR abs(vol_diff_gtm_crb_consolide_pt10) >  0.0011
                OR abs(vol_diff_gtm_crb_daily_crb_consolide_pt10) >  0.0011
                OR abs(vol_diff_gtm_crb_monthly) >  0.0011
                OR abs(vol_diff_gtm_crb_consolide_pt10_monthly) >  0.0011
                OR abs(vol_diff_gtm_crb_daily_crb_monthly) >  0.0011)
      BEGIN
         INSERT INTO gtm_crb_consolide_pt10_checkresult (
                        compteur_id,
                        vol_gtm_crb,
                        vol_crb_daily,
                        vol_crb_consolid,
                        vol_crb_monthly,
                        vol_diff_gtm_crb_daily,
                        vol_diff_gtm_crb_consolide_pt10,
                        vol_diff_gtm_crb_daily_crb_consolide_pt10,
                        vol_diff_gtm_crb_monthly,
                        vol_diff_gtm_crb_consolide_pt10_monthly,
                        vol_diff_gtm_crb_daily_crb_monthly,
                        type_pt_id,
                        dtstartparam,
                        dtendparam,
                        nbpointtoconsolide,
                        loginUser)
            SELECT compteur_id,
                   volume_crb,
                   volume_crb_daily,
                   volume_crb_consolide_pt10,
                   vol_crb_monthly,
                   vol_diff_gtm_crb_daily,
                   vol_diff_gtm_crb_consolide_pt10,
                   vol_diff_gtm_crb_daily_crb_consolide_pt10,
                   vol_diff_gtm_crb_monthly,
                   vol_diff_gtm_crb_consolide_pt10_monthly,
                   vol_diff_gtm_crb_daily_crb_monthly,
                   type_pt_id,
                   dtstartparam,
                   dtendparam,
                   nbpointtoconsolide,                   
                   loginUser
            FROM #check_consolidation
            WHERE    abs(vol_diff_gtm_crb_daily) >  0.0011
                  OR abs(vol_diff_gtm_crb_consolide_pt10) >  0.0011
                  OR abs(vol_diff_gtm_crb_daily_crb_consolide_pt10) >  0.0011
                  OR abs(vol_diff_gtm_crb_monthly) >  0.0011
                  OR abs(vol_diff_gtm_crb_consolide_pt10_monthly) >  0.0011
                  OR abs(vol_diff_gtm_crb_daily_crb_monthly) >  0.0011
                   SET @checkResultId = SCOPE_IDENTITY ();
 
-- Indique que le compteur a été contrôlé avec succès sur la période
         -- ----------------------------------------------------------------
         INSERT INTO gtm_crb_consolide_pt10_checklog (compteur_id,
                                                      dtstartparam,
                                                      dtendparam,
                                                      check_date,
                                                      start_month,
                                                      start_year,
                                                      end_month,
                                                      end_year,
                                                      state,
                                                      loginUser)
         VALUES (@cpt_id,
                 @dtStartParam,
                 @dtEndParam,
                 getdate (),
                 month (@dtStartParam),
                 year (@dtStartParam),
                 month (@dtEndParam),
                 year (@dtEndParam),
                 'KO',
                 @sLoginUser)                  
 
 
      END
   ELSE
      BEGIN
         -- Indique que le compteur a été contrôlé avec succès sur la période
         -- ----------------------------------------------------------------
         INSERT INTO gtm_crb_consolide_pt10_checklog (compteur_id,
                                                      dtstartparam,
                                                      dtendparam,
                                                      check_date,
                                                      start_month,
                                                      start_year,
                                                      end_month,
                                                      end_year,
                                                      state,
                                                      loginUser)
         VALUES (@cpt_id,
                 @dtStartParam,
                 @dtEndParam,
                 getdate (),
                 month (@dtStartParam),
                 year (@dtStartParam),
                 month (@dtEndParam),
                 year (@dtEndParam),
                 'OK',
                 @sLoginUser)
      END
END TRY
BEGIN CATCH
   -- Insertion dans le journal de log
   -- ---------------------------------
   PRINT ERROR_MESSAGE ()
 
   INSERT INTO gtm_sp_log (proc_name, dterror, log_msg)
   VALUES ('sp_check_gtm_crb_consolide', getdate (), ERROR_MESSAGE ())
END CATCH
GO

**SQLpro** · 27/04/2020, 16h04

Fonctionnellement qu'est ce que c'est sensé faire ? Expliquez en détail SVP.

A +

**SQLpro** · 27/04/2020, 16h12

Déjà quelques idioties :

plutôt que ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

right ('00000' + CONVERT (VARCHAR (20), @cpt_id), 5)

Utilisez la fonction FORMAT :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

FORMAT( @cpt_id, '00000')

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CONVERT (DATE, @dtStartParam, 103)

=> stupide car vous faites une conversion en DATE en utilisant un paramètre 103 réservé aux conversion en chaine de caractères !
Un simple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CAST(@dtStartParam AS DATE) est suffisant !

Ceci :
select type_pt_id from gtm_type_preference where lot_id = 2
ne pourfait-il pas être mis en constante (variable assignée une fois pour toute ?) ou en variable table ?

je ne comprend pas le sens de vos FULL OUTER JOIN (ligne 231-234)

Quelle est la structure de la table : gtm_crb_consolide_pt10_checkresult ?
Et ses index ?

A +

**Pfeffer** · 27/04/2020, 17h40

Je suis Ok pour les quelques idioties

!!
Voici la structure de la table, rien de bien méchant.
J'ai un index cluster suite à la PK et c'est tout.

Nom : Sans titre.png
Affichages : 178
Taille : 40,4 Ko

**aieeeuuuuu** · 27/04/2020, 17h55

Bonjour

Envoyé par SQLpro

Je pense que vous avez conçu un "monstre" dans le sens ou ce qu'il faut faire est de laisser gérer la charge de traitement pas le serveur et d'éviter de le faire côté applicatif !

C'est ce que je pense aussi.
Pourquoi avoir découpé le traitement ? ... et l'avoir découpé autant (je ne comprend pas l'utilité de toutes ces tables temporaires) ? Avez-vous bien plusieurs fichier de données pour la tempDB ?
Pourquoi ne pas faire tout le traitement en une seule requête ?

Envoyé par SQLpro

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CONVERT (DATE, @dtStartParam, 103)

=> stupide car vous faites une conversion en DATE en utilisant un paramètre 103 réservé aux conversion en chaine de caractères !
Un simple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CAST(@dtStartParam AS DATE) est suffisant !

Bah... ça dépend... en l’occurrence, ça permet de ne pas être dépendant du paramètre DATEFORMAT :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
SET DATEFORMAT MDY
 
DECLARE @x CHAR(10) = '03/04/2005'
 
 
SELECT 
	CONVERT(DATE, @x,  101) AS CONVERT_101
	,CONVERT(DATE ,@x, 103)  AS CONVERT_103
	,CAST(@x AS DATE) AS CAST_DATE

-->

CONVERT_101	CONVERT_103	CAST_DATE
2005-03-04	2005-04-03	2005-03-04

Procédure stockée, Multithread

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager