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 : 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
 
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]
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 : 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
 
/* 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