Conseils pour optimiser une PS

Version imprimable

Sous SQL server 2005
Je veux à partir de la table suivante
qui stocke les lignes d'un devis vu de manière hièrarchique
cad des Chapitres , des ouvrages et des articles assemblés de manière
quelconque sur une profondeur non limitée (en théorie)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
CREATE TABLE [dbo].[T_DEVPART_DPT](
	[DPT_ID] [bigint] IDENTITY(1,1) NOT NULL,
	[DEV_ID] [bigint] NULL,
	[DPT_PID] [bigint] NULL,
	[DPT_ORD] [int] NOT NULL,
	[DPT_STYPE] [dbo].[D_SOUS_TYP_ELEMENT] NOT NULL CONSTRAINT [DF_T_DEVPART_DPT_DPT_STYPE]  DEFAULT ((1)),
	[DPT_FAMILLE] [dbo].[D_FAMILLE_ARTICLE] NULL,
	[DPT_SFAMILLE] [dbo].[D_FAMILLE_ARTICLE] NULL,
	[DPT_REF] [dbo].[D_REF_ARTICLE] NULL,
	[DPT_LIBELLE] [dbo].[D_LIBELLE_ARTICLE] NULL,
	[DPT_QTE] [dbo].[D_QTE_ARTICLE] NULL CONSTRAINT [DF_T_DEVPART_DPT_DPT_QTE]  DEFAULT ((1)),
	[DPT_UNITP] [dbo].[D_UNIT_PU] NULL CONSTRAINT [DF_T_DEVPART_DPT_DPT_UNITP]  DEFAULT ('UN'),
	[DPT_PUA1] [dbo].[D_MT_MONETAIRE] NULL,
CONSTRAINT [PK_T_DEVPART_DPT] PRIMARY KEY CLUSTERED 
(
	[DPT_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Citation:

Index colonne indivduelle sur toutes les colonnes qui interviennent dans une jointure ci dessous

Obtenir la table complètée de valeurs calculées avec cette PS

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
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
 
/* Powered by General SQL Parser (www.sqlparser.com) */
 
ALTER PROCEDURE [DBO].[USP_DEV_GET_CALC_DEVPART]
               @P_DPT_ID BIGINT
AS
  BEGIN
    SET NOCOUNT ON;
 
    DECLARE  @DEV_ID BIGINT
 
    DECLARE  @COEFF_GEN DECIMAL(15,4)
 
    -- recherche du devis
    SELECT @DEV_ID = DEV_ID
    FROM   T_DEVPART_DPT
    WHERE  DPT_ID = @P_DPT_ID
 
    SELECT @COEFF_GEN = DEV_COEFF_GEN
    FROM   T_DEVIS_DEV
    WHERE  DEV_ID = @DEV_ID
 
    -- tables temporaires de travail
    CREATE TABLE #TEMPDEVPART (
      ID        BIGINT PRIMARY KEY,
      PID       BIGINT,
      QTE       DECIMAL(15,4),
      PUA1      DECIMAL(15,4),
      PUA2      DECIMAL(15,4),
      PUV1      DECIMAL(15,4),
      PUVF      DECIMAL(15,4),
      PTA1      DECIMAL(15,4),
      PTA2      DECIMAL(15,4),
      PTV1      DECIMAL(15,4),
      PTVF      DECIMAL(15,4),
      NB_HRS_MO DECIMAL(15,4),
      MT_MO     DECIMAL(15,4)
 
    CREATE TABLE #DESCENDANTS (
      ID  BIGINT PRIMARY KEY,
      LVL INT);
 
    -- recherche des élements descendants de @P_DPT_ID
    WITH DESCENDANTS_CTE(ID,LVL)
         AS (SELECT A.DPT_ID,
                    0        AS LVL
             FROM   T_DEVPART_DPT A
             WHERE  A.DPT_ID = @P_DPT_ID
             UNION ALL
             SELECT B.DPT_ID,
                    C.LVL + 1
             FROM   T_DEVPART_DPT AS B
                    INNER JOIN DESCENDANTS_CTE AS C
                      ON B.DPT_PID = C.ID)
    INSERT INTO #DESCENDANTS
               (ID,
                LVL)
    SELECT ID,
           LVL
    FROM   DESCENDANTS_CTE;
 
    -- selection des éléments devis de type article (main oeuvre + materiel) avec recherche des coeff dans la table DEV_CALC
    WITH CTE1(ID,COEFF_ECO,COEFF_STD,COEFF_SPE)
         AS (SELECT B.DPT_ID,
                    C.DEC_COEFF_ECO,
                    C.DEC_COEFF_STD,
                    C.DEC_COEFF_SPE
             FROM   #DESCENDANTS AS A
                    INNER JOIN T_DEVPART_DPT AS B
                      ON (A.ID = B.DPT_ID)
                    INNER JOIN T_DEV_CALC_DEC AS C
                      ON (B.DPT_FAMILLE = C.DEC_FAMILLE)
                         AND (ISNULL(B.DPT_SFAMILLE,'0') = ISNULL(C.DEC_SFAMILLE,'0'))
             WHERE  B.DPT_STYPE = 4
                    AND C.DEV_ID = @DEV_ID),
         CTE2(ID,PUA1,PUA2,COEFF_STD,COEFF_SPE)
         AS (SELECT A.ID,
                    B.DPT_PUA1,
                    (B.DPT_PUA1 * A.COEFF_ECO) AS PUA2,
                    A.COEFF_STD,
                    A.COEFF_SPE
             FROM   CTE1 A
                    INNER JOIN T_DEVPART_DPT B
                      ON (A.ID = B.DPT_ID)),
         CTE3(ID,PUA1,PUA2,PUV1)
         AS (SELECT ID,
                    PUA1,
                    PUA2,
                    (PUA2 * @COEFF_GEN * COEFF_STD * COEFF_SPE) AS PUV1
             FROM   CTE2),
         CTE4(ID,PUA1,PUA2,PUV1,PUVF)
         AS (SELECT ID,
                    PUA1,
                    PUA2,
                    PUV1,
                    PUV1 AS PUVF
             FROM   CTE3),
         CTE5(ID,QTE,PUA1,PUA2,PUV1,PUVF,NB_HRS_MO,MT_MO)
         AS (SELECT A.ID,
                    B.DPT_QTE AS QTE,
                    A.PUA1,
                    A.PUA2,
                    A.PUV1,
                    A.PUVF,
                    1         AS NB_HRS_MO,
                    A.PUA2    AS MT_MO
             FROM   CTE4 AS A
                    INNER JOIN T_DEVPART_DPT AS B
                      ON (A.ID = B.DPT_ID)
             WHERE  GPG.DBO.UFN_FAMILLE_IS_MO(B.DPT_FAMILLE,B.DPT_SFAMILLE) = -1
             UNION ALL
             SELECT A.ID,
                    B.DPT_QTE,
                    A.PUA1,
                    A.PUA2,
                    A.PUV1,
                    A.PUVF,
                    NULL      AS NB_HRS_MO,
                    NULL      AS MT_MO
             FROM   CTE4 AS A
                    INNER JOIN T_DEVPART_DPT AS B
                      ON (A.ID = B.DPT_ID)
             WHERE  GPG.DBO.UFN_FAMILLE_IS_MO(B.DPT_FAMILLE,B.DPT_SFAMILLE) <> -1),
         CTE6(ID,UNITP)
         AS (SELECT DPT_ID,
                    GPG.DBO.UFN_GET_QTE_UNITPU(DPT_UNITP)  AS UNITP
             FROM   T_DEVPART_DPT)
    INSERT INTO #TEMPDEVPART
               (ID,
                PID,
                QTE,
                PUA1,
                PUA2,
                PUV1,
                PUVF,
                NB_HRS_MO,
                MT_MO,
                PTA1,
                PTA2,
                PTV1,
                PTVF)
    SELECT A.ID,
           C.DPT_PID,
           A.QTE,
           A.PUA1,
           A.PUA2,
           A.PUV1,
           A.PUVF,
           A.NB_HRS_MO,
           A.MT_MO,
           (A.PUA1 * A.QTE) * 1 / B.UNITP AS PTA1,
           (A.PUA2 * A.QTE) * 1 / B.UNITP AS PTA2,
           (A.PUV1 * A.QTE) * 1 / B.UNITP AS PTV1,
           (A.PUVF * A.QTE) * 1 / B.UNITP AS PTVF
    FROM   CTE5 AS A
           INNER JOIN CTE6 AS B
             ON (A.ID = B.ID)
           INNER JOIN T_DEVPART_DPT AS C
             ON (B.ID = C.DPT_ID)
 
    -- selection des éléments devis de type ajustement de prix
    INSERT INTO #TEMPDEVPART
               (ID,
                PID,
                QTE,
                PUA1,
                PUA2,
                PUV1,
                PUVF,
                PTA1,
                PTA2,
                PTV1,
                PTVF)
    SELECT B.DPT_ID,
           B.DPT_PID,
           1                      AS QTE,
           B.DPT_PUA1             AS PUA1,
           B.DPT_PUA1             AS PUA2,
           B.DPT_PUA1             AS PUV1,
           B.DPT_PUA1             AS PUVF,
           B.DPT_PUA1 * B.DPT_QTE AS PTA1,
           B.DPT_PUA1 * B.DPT_QTE AS PUA2,
           B.DPT_PUA1 * B.DPT_QTE AS PTV1,
           B.DPT_PUA1 * B.DPT_QTE AS PTVF
    FROM   #DESCENDANTS AS A
           INNER JOIN T_DEVPART_DPT AS B
             ON (A.ID = B.DPT_ID)
    WHERE  B.DPT_STYPE = 5
 
    -- iteration dans l'ordre inverse de LVL sur les elements qui sont des noeuds
    DECLARE DEVISPART_CURSOR CURSOR LOCAL FAST_FORWARD FOR
    SELECT   A.ID
    FROM     #DESCENDANTS A
             INNER JOIN T_DEVPART_DPT B
               ON (A.ID = B.DPT_ID)
    WHERE    B.DPT_STYPE IN (1,2,3)
    ORDER BY A.LVL DESC
 
    DECLARE  @ID BIGINT
 
    OPEN DEVISPART_CURSOR
 
    FETCH NEXT FROM DEVISPART_CURSOR
    INTO @ID
 
    WHILE @@FETCH_STATUS = 0
      BEGIN
        WITH CTE7(ID,PID,QTE,UNITP)
             AS (SELECT DPT_ID,
                        DPT_PID,
                        DPT_QTE,
                        GPG.DBO.UFN_GET_QTE_UNITPU(DPT_UNITP)  AS UNITP
                 FROM   T_DEVPART_DPT
                 WHERE  DPT_ID = @ID),
             CTE8(PUA1,PUA2,PUV1,PUVF,NB_HRS_MO,MT_MO)
             AS (SELECT SUM(PTA1),
                        SUM(PTA2),
                        SUM(PTV1),
                        SUM(PTVF),
                        SUM(NB_HRS_MO * QTE),
                        SUM(MT_MO * QTE)
                 FROM   #TEMPDEVPART
                 WHERE  PID = @ID)
        INSERT INTO #TEMPDEVPART
                   (ID,
                    PID,
                    QTE,
                    PUA1,
                    PUA2,
                    PUV1,
                    PUVF,
                    NB_HRS_MO,
                    MT_MO,
                    PTA1,
                    PTA2,
                    PTV1,
                    PTVF)
        SELECT A.ID,
               A.PID,
               A.QTE,
               B.PUA1,
               B.PUA2,
               B.PUV1,
               B.PUVF,
               B.NB_HRS_MO,
               B.MT_MO,
               B.PUA1 * A.QTE,
               B.PUA2 * A.QTE,
               B.PUV1 * A.QTE,
               B.PUVF * A.QTE
        FROM   CTE7 AS A,
               CTE8 AS B;
 
        FETCH NEXT FROM DEVISPART_CURSOR
        INTO @ID
      END
 
    SELECT   --A.*
             A.DPT_ID,
             A.DPT_PID,
             A.DPT_FAMILLE,
             A.DPT_QTE,
             B.PUA1,
             B.PUA2,
             B.PUV1,
             B.PUVF,
             B.PTA1,
             B.PTA2,
             B.PTV1,
             B.PTVF,
             B.NB_HRS_MO,
             B.MT_MO,
    FROM     T_DEVPART_DPT AS A
             INNER JOIN #TEMPDEVPART AS B
               ON (A.DPT_ID = B.ID)
    ORDER BY B.ID
  END

Cette procedure (pas tout à fait complète) me fait les calculs par ligne et les totaux par parents depuis la base jusqu'a la racine tel que voulu
La base est sur un serveur Dell sur lequel pour l'instant je suis seul connecté et qui a été prévu pour 25 utilisateurs connectés à la base, la table DEVPART contient environ 1 million lignes
le temps d'execution sur un devis type pour nous cad environ 2500 articles dans 400 ensembles sur 5 niveau (sans compter la racine) est de 2 à 3 secondes ce qui est trop élévé car cette ps sera constamment invoquée à chaque changement coté client, je n'arrive pas à l'optimiser, je souhaiterais les conseils de gens avisés (Mr Frédéric Brouard par exemple)
mais tous les autres sont les bienvenus :lol:

Il faut la compacter un peu comme ci dessous

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
 
/* Powered by General SQL Parser (www.sqlparser.com) */
 
SET ANSI_NULLS ON 
 
SET QUOTED_IDENTIFIER ON 
 
GO
 
-- =============================================
-- Author:		
-- Create date: 
-- Description:	
-- =============================================
ALTER PROCEDURE [DBO].[USP_DEV_GET_CALC_DEVPART_ESS]
               @P_DPT_ID BIGINT
AS
  BEGIN
    SET NOCOUNT ON;
 
    DECLARE  @DEV_ID BIGINT
 
    DECLARE  @COEFF_GEN DECIMAL(15,4)
 
    -- recherche du devis
    SELECT @DEV_ID = DEV_ID
    FROM   T_DEVPART_DPT
    WHERE  DPT_ID = @P_DPT_ID
 
    SELECT @COEFF_GEN = DEV_COEFF_GEN
    FROM   T_DEVIS_DEV
    WHERE  DEV_ID = @DEV_ID
 
    -- tables temporaires de travail
    CREATE TABLE #TEMPDEVPART (
      ID       BIGINT PRIMARY KEY,
      PID      BIGINT,
      LVL      INT,
      STYPE    INT,
      FAMILLE  VARCHAR(16),
      SFAMILLE VARCHAR(16),
      QTE      DECIMAL(15,4),
      UNITP    VARCHAR(3),
      PUA1     DECIMAL(15,4),
      PUA2     DECIMAL(15,4),
      PUV1     DECIMAL(15,4),
      PUVF     DECIMAL(15,4),
      PTA1     DECIMAL(15,4),
      PTA2     DECIMAL(15,4),
      PTV1     DECIMAL(15,4),
      PTVF     DECIMAL(15,4),
      MT_AJUST DECIMAL(15,4));
 
    --CREATE INDEX IX_TempDevPart ON #TempDevPart ( PID ASC )
    ;
 
    -- recherche des élements descendants de @P_DPT_ID
    WITH DESCENDANTS_CTE(ID,LVL)
         AS (SELECT A.DPT_ID,
                    0        AS LVL
             FROM   T_DEVPART_DPT A
             WHERE  A.DPT_ID = @P_DPT_ID
             UNION ALL
             SELECT B.DPT_ID,
                    C.LVL + 1
             FROM   T_DEVPART_DPT AS B
                    INNER JOIN DESCENDANTS_CTE AS C
                      ON B.DPT_PID = C.ID)
    INSERT INTO #TEMPDEVPART
               (ID,
                PID,
                LVL,
                STYPE,
                FAMILLE,
                SFAMILLE,
                QTE,
                UNITP,
                PUA1)
    SELECT B.DPT_ID,
           B.DPT_PID,
           A.LVL,
           B.DPT_STYPE,
           B.DPT_FAMILLE,
           B.DPT_SFAMILLE,
           B.DPT_QTE,
           B.DPT_UNITP,
           B.DPT_PUA1
    FROM   DESCENDANTS_CTE A
           INNER JOIN T_DEVPART_DPT B
             ON (A.ID = B.DPT_ID);
 
    WITH CTE1(ID,UNITP,PUA1,COEFF_ECO,COEFF_STD,COEFF_SPE)
         AS (SELECT A.ID,
                    GPG.DBO.UFN_GET_QTE_UNITPU(A.UNITP),
                    A.PUA1,
                    B.DEC_COEFF_ECO,
                    B.DEC_COEFF_STD,
                    B.DEC_COEFF_SPE
             FROM   #TEMPDEVPART AS A
                    INNER JOIN T_DEV_CALC_DEC AS B
                      ON (A.FAMILLE = B.DEC_FAMILLE)
                         AND (ISNULL(A.SFAMILLE,'0') = ISNULL(B.DEC_SFAMILLE,'0'))
             WHERE  A.STYPE = 4
                    AND B.DEV_ID = @DEV_ID),
         CTE2(ID,UNITP,PUA1,PUA2,COEFF_STD,COEFF_SPE)
         AS (SELECT ID,
                    UNITP,
                    PUA1,
                    (PUA1 * COEFF_ECO) AS PUA2,
                    COEFF_STD,
                    COEFF_SPE
             FROM   CTE1),
         CTE3(ID,UNITP,PUA1,PUA2,PUV1)
         AS (SELECT ID,
                    UNITP,
                    PUA1,
                    PUA2,
                    (PUA2 * @COEFF_GEN * COEFF_STD * COEFF_SPE) AS PUV1
             FROM   CTE2),
         CTE4(ID,UNITP,PUA1,PUA2,PUV1,PUVF)
         AS (SELECT ID,
                    UNITP,
                    PUA1,
                    PUA2,
                    PUV1,
                    PUV1  AS PUVF
             FROM   CTE3)
    UPDATE #TEMPDEVPART
    SET    PTA1 = (B.PUA1 * A.QTE) * 1 / B.UNITP,
           PTA2 = (B.PUA2 * A.QTE) * 1 / B.UNITP,
           PTV1 = (B.PUV1 * A.QTE) * 1 / B.UNITP,
           PTVF = (B.PUVF * A.QTE) * 1 / B.UNITP
    FROM   #TEMPDEVPART AS A
           INNER JOIN CTE4 AS B
             ON (A.ID = B.ID)
 
    -- iteration dans l'ordre inverse de LVL sur les elements qui sont des noeuds
    DECLARE DEVISPART_CURSOR CURSOR LOCAL FAST_FORWARD FOR
    SELECT   ID
    FROM     #TEMPDEVPART
    WHERE    STYPE IN (1,2,3)
    ORDER BY LVL DESC
 
    DECLARE  @ID BIGINT
 
    OPEN DEVISPART_CURSOR
 
    FETCH NEXT FROM DEVISPART_CURSOR
    INTO @ID
 
    WHILE @@FETCH_STATUS = 0
      BEGIN
        WITH CTE7(ID,UNITP)
             AS (SELECT ID,
                        GPG.DBO.UFN_GET_QTE_UNITPU(UNITP)  AS UNITP
                 FROM   #TEMPDEVPART
                 WHERE  ID = @ID),
             CTE8(PUA1,PUA2,PUV1,PUVF)
             AS (SELECT SUM(PTA1),
                        SUM(PTA2),
                        SUM(PTV1),
                        SUM(PTVF)
                 FROM   #TEMPDEVPART
                 WHERE  PID = @ID)
        UPDATE #TEMPDEVPART
        SET    PUA1 = C.PUA1,
               PUA2 = C.PUA2,
               PUV1 = C.PUV1,
               PUVF = C.PUVF,
               PTA1 = C.PUA1 * A.QTE,
               PTA2 = C.PUA2 * A.QTE,
               PTV1 = C.PUV1 * A.QTE,
               PTVF = C.PUVF * A.QTE
        FROM   #TEMPDEVPART AS A,
               CTE7 AS B,
               CTE8 AS C
        WHERE  A.ID = @ID
 
        FETCH NEXT FROM DEVISPART_CURSOR
        INTO @ID
      END
 
    SELECT B.*
    FROM   T_DEVPART_DPT AS A
           INNER JOIN #TEMPDEVPART AS B
             ON (A.DPT_ID = B.ID)
 
  END

09/08/2007, 19h02
PickEpique

Dans ce cas j'obtiens effectivement moins de une seconde
sur le devis type sur lequel j'évalue la vitesse, je n'en reste pas moins étonné
sur une requète de ce type je pensais pouvoir obtenir qqc de quasi instantané
Note : l'ajout de l'index sur PID (commenté dans cette version) a plutot
tendance à légerement degrader les chose, nouvel étonnement de ma part
09/08/2007, 22h18
SQLpro

1) je pense que vous pouvez suprimer les tables temporaires et faire des vues (éventuellement indexées), de même vous pouvez certainement vous passer de curseur (gorumand et très contre performant)
2) évitez de modéliser un arbre en auto référence et préférez la modélisation par intervalle, vous n'aurez plus alors de récursivité très contre performante à gérer pour vos requtes

Pour 2) lisez l'article que j'ai écrit sur la gestion d'arbre en mode intervallaire :
http://sqlpro.developpez.com/cours/arborescence/

A +
10/08/2007, 11h49
PickEpique

merci pour votre réponse
Justement je ne vois pas trop comment utiliser une vue à la place
de la table temporaire dans mon 2 eme exemple ou je fais des update successif sur celle ci
Pour supprimer le curseur je vais creuser un peu plus et suivre votre conseil.
Je suivrai peut être aussi votre conseil sur l'abandon de la table en autojointure si je vois une dégradation des performances en cours de montée en charge mais pour l'instant par exemple sur cette ps si j'extrait la recherche des descendants, 1 ere cte, celle ci a une durée d'execution de 93 ms
sur un devis cette fois comprenant environ 4700 lignes (4200 feuilles, 500 noeuds sur 4 niveaux) ; sur le même devis la recherche de tous les chemins
( DEBUT, FIN , LONGUEUR) donne environ 420 ms avec l'exemple transfermeture extraite du bouquin de Celko.
Je pensais en fait que le problème se situait sur le reste