Requête plus rapide ?

**nemo67** · 16/09/2010, 23h18

Bonjour à tous

Dans SQL 2005 édition standard

J'ai crée une requête qui cumule des montants en fonction de certains critères. Cette requête est basée sur des agregats avec des group by ainsi que l'option ROLL UP. J'utilise également des jointures gauches.

La table principale contient plusieurs centaines de milliers de lignes correspondant à un historique de vente.

La requête fonctionne bien et est relativement rapide mais je cherche quand même à l'optimiser. En effet je me dis que avec cette méthode SQL recalcule toujours les mêmes aggregats sur les données historisées ce qui me semble dommage ! il faudrait simplement actualiser les nouvelles données ...

J'ai lu les articles de SQLPro concernant les vues indexées et cela me semblait parfaitement correspondre au besoin mais à priori elles ne seraient pas compatibles avec le ROLL UP. Remettre en question cette fonctionalité est un trop gros boulot car l'applic utilise le résultat et cela signifierait modifier l'appli pour que celle ci calcule les résultats intermédiaires.

Avez vous une idée d'optimisation ?

**Ptit_Dje** · 17/09/2010, 09h29

Bonjour,

Vous pouvez penser a utiliser SSAS qui est prevu pour cela.

**elsuket** · 17/09/2010, 10h05

Bonjour,

La requête fonctionne bien et est relativement rapide mais je cherche quand même à l'optimiser

Quelles sont les valeurs que vous récupérez à l'aide de SET STATISTICS IO pour un jeu de requêtes type ?
Quels sont les index sur cette table ? Combien de niveaux intermédiaires comptent-ils ?
Que dit votre plan de requête ?

@++

**nemo67** · 17/09/2010, 22h01

Bonjour,

Merci de vous interesser à mon problème

Pour SSAS oui j'ai vu que ca pouvait correspondre mais je ne connais pas du tout et je ne pense avoir les outils pour faire cela.

Je précise que je suis débutant SQL

Concernant le plan de requête, j'espère que ce j'ai mis dans le fichier excel ci joint correspond à votre demande ?

Merci

**SQLpro** · 17/09/2010, 23h02

Vous pouvez aussi utiliser une vue indexée....

Postez quelques unes des requêtes les plus classiques.

A +

**nemo67** · 17/09/2010, 23h29

Merci SQLPro

Ci joint un exemple de requête :

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
 
WITH RES ( LIB1, LIB2, COL3, COL4, COL6, COL7, COL9, COL10, COL12, COL13, COL15, 
     COL16, GRP1, GRP2 ) 
     AS (SELECT T029_21.STAT + ' - ' + T029_21.LIB, 
                T029_22.STAT + ' - ' + T029_22.LIB, 
                SUM(CASE 
                      WHEN YEAR(PIDT) = 2009 THEN TOTALQTE * SENS2 
                      ELSE 0 
                    END), 
                SUM(CASE 
                      WHEN YEAR(PIDT) = 2010 THEN TOTALQTE * SENS2 
                      ELSE 0 
                    END), 
                SUM(CASE 
                      WHEN YEAR(PIDT) = 2009 THEN TOTALCA * SENS2 
                      ELSE 0 
                    END), 
                SUM(CASE 
                      WHEN YEAR(PIDT) = 2010 THEN TOTALCA * SENS2 
                      ELSE 0 
                    END), 
                SUM(CASE 
                      WHEN YEAR(PIDT) = 2009 THEN TOTALMARGE1 * SENS2 
                      ELSE 0 
                    END), 
                SUM(CASE 
                      WHEN YEAR(PIDT) = 2010 THEN TOTALMARGE1 * SENS2 
                      ELSE 0 
                    END), 
                SUM(CASE 
                      WHEN YEAR(PIDT) = 2009 THEN TOTALMARGE2 * SENS2 
                      ELSE 0 
                    END), 
                SUM(CASE 
                      WHEN YEAR(PIDT) = 2010 THEN TOTALMARGE2 * SENS2 
                      ELSE 0 
                    END), 
                SUM(CASE 
                      WHEN YEAR(PIDT) = 2009 THEN TOTALMARGE3 * SENS2 
                      ELSE 0 
                    END), 
                SUM(CASE 
                      WHEN YEAR(PIDT) = 2010 THEN TOTALMARGE3 * SENS2 
                      ELSE 0 
                    END), 
                Grouping(T029_21.STAT + ' - ' + T029_21.LIB), 
                Grouping(T029_22.STAT + ' - ' + T029_22.LIB) 
         FROM   (SELECT MOUV.DOS, 
                        ENT.PIDT, 
                        MOUV.TIERS, 
                        MOUV.REF, 
                        MOUV.OP, 
                        MOUV.DEPO, 
                        MOUV.REPR_0001, 
                        MOUV.SYREFFACQTE                         AS TOTALQTE, 
                        Round(MOUV.PUSTAT * MOUV.SYREFFACQTE, 2) AS TOTALCA, 
                        0                                        AS TOTALMARGE1, 
                        0                                        AS TOTALMARGE2, 
                        0                                        AS TOTALMARGE3, 
                        ( CASE 
                            WHEN T020.SENS = 1 THEN -1 
                            ELSE 1 
                          END )                                  AS SENS2, 
                        ENT.PINO                                 AS PIECE 
                 FROM   MOUV 
                        JOIN ENT 
                          ON ENT.DOS = MOUV.DOS 
                             AND ENT.TICOD = 'C' 
                             AND ENT.PICOD = 4 
                             AND ENT.CE4 = 1 
                             AND ENT.PINO = MOUV.FANO 
                        JOIN T020 
                          ON T020.DOS = 999 
                             AND T020.CEBIN = 20 
                             AND T020.OP = MOUV.OP 
                        JOIN CLI 
                          ON ENT.DOS = CLI.DOS 
                             AND ENT.TIERS = CLI.TIERS 
                             AND CLI.CE1 = 3 
                        JOIN ART 
                          ON MOUV.DOS = ART.DOS 
                             AND MOUV.REF = ART.REF 
                        LEFT JOIN T036 
                          ON T036.DOS = 999 
                             AND ART.PRODNAT = T036.PRODNAT 
                 WHERE  MOUV.TICOD = 'C' 
                        AND MOUV.CE8 = '1' 
                        AND MOUV.PICOD = 4 
                        AND ( ( ENT.PIDT >= { d '2009-01-01'} 
                                AND ENT.PIDT <= {d '2009-12-31'} ) 
                               OR ( ENT.PIDT >= { d '2010-01-01'} 
                                    AND ENT.PIDT <= {d '2010-12-31'} ) ) 
                        AND ( ENT.DOS = 50 
                              AND ENT.CE4 = '1' 
                              AND ENT.TICOD = 'C' 
                              AND ENT.PICOD = 4 
                              AND MOUV.FACE4 = 1 
 
                              AND ( T036.SYTYPEFRAIS IS NULL 
                                     OR T036.SYTYPEFRAIS IN ( 0, 1 ) ) )) AS 
                BASE 
                LEFT JOIN CLI 
                  ON CLI.DOS = BASE.DOS 
                     AND CLI.TIERS = BASE.TIERS 
                     AND CLI.CE1 = 3 
                LEFT JOIN T029 AS T029_21 
                  ON T029_21.DOS = BASE.DOS 
                     AND T029_21.CEBIN = 29 
                     AND T029_21.STATNO = 2 
                     AND T029_21.STAT = LEFT(CLI.STAT_0002, 1) 
                LEFT JOIN T029 AS T029_22 
                  ON T029_22.DOS = BASE.DOS 
                     AND T029_22.CEBIN = 29 
                     AND T029_22.STATNO = 2 
                     AND T029_22.STAT = LEFT(CLI.STAT_0002, 3) 
         GROUP  BY T029_21.STAT + ' - ' + T029_21.LIB, 
                   T029_22.STAT + ' - ' + T029_22.LIB WITH ROLLUP) 
SELECT * 
FROM   RES

Comme dit j'ai bien vu les vues indexées mais sauf erreur elles ne supportent pas le ROLLUP. Est ce exact ?

**lopesmauricio** · 21/09/2010, 17h01

Bonjour,

As-tu trouvé une solution pour ton problème de temps d'exécution du rollup?
J'ai ce même problème...
D'ailleurs, combien de temps ça prends?

Merci,
Mauricio

**nemo67** · 21/09/2010, 21h47

non je n'ai pas de solution ...

Ma table principale fait 300 000 lignes et la requête met environ 3 secondes à s'afficher la première fois et ensuite c'est immédiat (merci le cache)

C'est raisonnable et les utilisateurs ne s'en plaignent pas mais sur le principe le fait que la requête refasse toujours les mêmes cumuls (sur l'historique) me perturbe ...

Je pense qu'il y a vraiment moyen d'avoir quelque chose d'immédiat du 1er coup mais franchement je sais pas comment aborder le problème.

J'attends de voir si le cadors du forum ont des pistes ...

**Bluedeep** · 22/09/2010, 10h58

Envoyé par nemo67

J'attends de voir si le cadors du forum ont des pistes ...

Comme déjà dit plus haut, utiliser des vues indexées.

**mikedavem** · 22/09/2010, 11h44

L'utilisation d'une vue indexée pourrait en effet vous aider.
Mais avant cela il faudrait modifier votre requête pour supprimer la clause WITH ROLLUP qui ne fonctionnera pas si vous voulez créer une vue indexée.

++

**nemo67** · 22/09/2010, 19h30

J'ai lu les articles de SQLPro concernant les vues indexées et cela me semblait parfaitement correspondre au besoin mais à priori elles ne seraient pas compatibles avec le ROLL UP. Remettre en question cette fonctionalité est un trop gros boulot car l'applic utilise le résultat et cela signifierait modifier l'appli pour que celle ci calcule les résultats intermédiaires.

Oui c'est ce que j'indiquais dans mon post de départ.

J'en déduis qu'il n'y a pas moyen d'optimiser sans changer l'appli ... dommage !

**mikedavem** · 22/09/2010, 19h42

Si vous avez la main sur la requête dans l'application, cela est possible en enlevant le rollup de votre requête et en créant la vue indexée adéquate.

Il suffit ensuite d'adapter votre requête en utilisant la vue et en insérant des GROUPING SETS par exemple pour avoir le résultat escompté. Vu de loin cela semble possible.

Cependant si vous n'avez pas la main cela semble difficile en effet

++

**nemo67** · 22/09/2010, 20h00

Merci mikedavem

Je ne connais pas groupings sets je vais regarder

**Waldar** · 24/09/2010, 11h37

Par curiosité, est-ce que cette requête-ci donne les mêmes résultats ?
J'ai élagué dans ce qui ne me paraissait pas nécessaire, j'ai rajouté les dbo devant les noms de tables, il doit y avoir un peu moins de calculs au niveau des colonnes et vous gagnez une table dans la jointure.
Ça ne devrait pas changer grand-chose aux performances mais vous devriez gagner un peu de CPU.

Ah si j'ai rajouté les année N et N-1 en dynamique par rapport au jour d'exécution de la requête, c'est un besoin récurrent !

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
  SELECT T029_21.STAT + ' - ' + T029_21.LIB AS LIB1, 
         T029_22.STAT + ' - ' + T029_22.LIB AS LIB2, 
         SUM(CASE 
               WHEN ENT.PIDT BETWEEN DATEADD(year, DATEDIFF(year,  0, getdate())-1,  0)
                                 AND DATEADD(year, DATEDIFF(year, -1, getdate())-1, -1)
               THEN CASE T020.SENS WHEN 1 THEN -1 ELSE 1 END * MOUV.SYREFFACQTE
               ELSE 0 
             END) AS COL3,
         SUM(CASE 
               WHEN ENT.PIDT BETWEEN DATEADD(year, DATEDIFF(year,  0, getdate())  ,  0)
                                 AND DATEADD(year, DATEDIFF(year, -1, getdate())  , -1)
               THEN CASE T020.SENS WHEN 1 THEN -1 ELSE 1 END * MOUV.SYREFFACQTE
               ELSE 0 
             END) AS COL4,
         SUM(CASE 
               WHEN ENT.PIDT BETWEEN DATEADD(year, DATEDIFF(year,  0, getdate())-1,  0)
                                 AND DATEADD(year, DATEDIFF(year, -1, getdate())-1, -1)
               THEN CASE T020.SENS WHEN 1 THEN -1 ELSE 1 END * Round(MOUV.PUSTAT * MOUV.SYREFFACQTE, 2)
               ELSE 0 
             END) AS COL6,
         SUM(CASE 
               WHEN ENT.PIDT BETWEEN DATEADD(year, DATEDIFF(year,  0, getdate())  ,  0)
                                 AND DATEADD(year, DATEDIFF(year, -1, getdate())  , -1)
               THEN CASE T020.SENS WHEN 1 THEN -1 ELSE 1 END * Round(MOUV.PUSTAT * MOUV.SYREFFACQTE, 2)
               ELSE 0 
             END) AS COL7,
         0 AS COL9,
         0 AS COL10,
         0 AS COL12,
         0 AS COL13,
         0 AS COL15,
         0 AS COL16,
         Grouping(T029_21.STAT + ' - ' + T029_21.LIB) AS GRP1, 
         Grouping(T029_22.STAT + ' - ' + T029_22.LIB) AS GRP2
    FROM dbo.MOUV 
         INNER JOIN dbo.ENT 
           ON ENT.DOS  = MOUV.DOS 
          AND ENT.PINO = MOUV.FANO 
         INNER JOIN dbo.T020 
           ON T020.OP = MOUV.OP 
         INNER JOIN dbo.CLI 
           ON CLI.DOS   = ENT.DOS
          AND CLI.TIERS = ENT.TIERS 
         INNER JOIN dbo.ART 
           ON MOUV.DOS = ART.DOS 
          AND MOUV.REF = ART.REF 
         LEFT OUTER JOIN dbo.T036 
           ON T036.PRODNAT = ART.PRODNAT
          AND T036.DOS          = 999
          AND T036.SYTYPEFRAIS IN (0, 1)
         LEFT OUTER JOIN dbo.T029 AS T029_21 
           ON T029_21.DOS    = BASE.DOS 
          AND T029_21.STAT   = LEFT(CLI.STAT_0002, 1) 
          AND T029_21.CEBIN  = 29 
          AND T029_21.STATNO = 2 
         LEFT OUTER JOIN dbo.T029 AS T029_22 
           ON T029_22.DOS    = BASE.DOS 
          AND T029_22.STAT   = LEFT(CLI.STAT_0002, 3) 
          AND T029_22.CEBIN  = 29 
          AND T029_22.STATNO = 2 
   WHERE MOUV.TICOD = 'C' 
     AND MOUV.CE8   = '1' 
     AND MOUV.PICOD = 4
     AND ENT.TICOD  = 'C' 
     AND ENT.PICOD  = 4 
     AND ENT.CE4    = 1 
     AND T020.CEBIN = 20 
     AND T020.DOS   = 999
     AND CLI.CE1    = 3
     AND ENT.PIDT BETWEEN DATEADD(year, DATEDIFF(year,  0, getdate())-1,  0)
                      AND DATEADD(year, DATEDIFF(year, -1, getdate())  , -1)
     AND ENT.DOS    = 50 
     AND ENT.CE4    = '1' 
     AND ENT.TICOD  = 'C' 
     AND ENT.PICOD  = 4 
     AND MOUV.FACE4 = 1 
GROUP BY T029_21.STAT + ' - ' + T029_21.LIB, 
         T029_22.STAT + ' - ' + T029_22.LIB WITH ROLLUP;

Une question, dans la table T020, SENS peut prendre quelles valeurs ?
Si c'est juste {-1, 0, 1, null}, au lieu de faire le CASE il suffit de multiplier par -1 !

**nemo67** · 26/09/2010, 22h43

Merci Waldar pour votre réponse

pour les dbo effectivement ! j'oublie toujours de les mettre même si je sais que ca permet d'être plus efficace ...

Par contre, désolé mais la requête ne fonctionne pas car il manque la déclaration de BASE

la colonne T020.SENS peut prendre la valeur 1 ou 2

**Waldar** · 27/09/2010, 09h27

J'ai tout regroupé dans une seule requête, pas besoin de base donc.
Vous auriez pu la lancer...

**nemo67** · 27/09/2010, 23h22

je l'ai lancé ??

c'est comme ca que j'ai vu que la table BASE n'était pas définie !

Franchement rien qu'à la lecture perso j'ai pas le niveau pour trouver par moi même les erreurs !!

**Waldar** · 28/09/2010, 17h36

Au temps pour moi vous avez raison, j'avais laissé deux coquilles :

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
  SELECT T029_21.STAT + ' - ' + T029_21.LIB AS LIB1, 
         T029_22.STAT + ' - ' + T029_22.LIB AS LIB2, 
         SUM(CASE 
               WHEN ENT.PIDT BETWEEN DATEADD(year, DATEDIFF(year,  0, getdate())-1,  0)
                                 AND DATEADD(year, DATEDIFF(year, -1, getdate())-1, -1)
               THEN CASE T020.SENS WHEN 1 THEN -1 ELSE 1 END * MOUV.SYREFFACQTE
               ELSE 0 
             END) AS COL3,
         SUM(CASE 
               WHEN ENT.PIDT BETWEEN DATEADD(year, DATEDIFF(year,  0, getdate())  ,  0)
                                 AND DATEADD(year, DATEDIFF(year, -1, getdate())  , -1)
               THEN CASE T020.SENS WHEN 1 THEN -1 ELSE 1 END * MOUV.SYREFFACQTE
               ELSE 0 
             END) AS COL4,
         SUM(CASE 
               WHEN ENT.PIDT BETWEEN DATEADD(year, DATEDIFF(year,  0, getdate())-1,  0)
                                 AND DATEADD(year, DATEDIFF(year, -1, getdate())-1, -1)
               THEN CASE T020.SENS WHEN 1 THEN -1 ELSE 1 END * Round(MOUV.PUSTAT * MOUV.SYREFFACQTE, 2)
               ELSE 0 
             END) AS COL6,
         SUM(CASE 
               WHEN ENT.PIDT BETWEEN DATEADD(year, DATEDIFF(year,  0, getdate())  ,  0)
                                 AND DATEADD(year, DATEDIFF(year, -1, getdate())  , -1)
               THEN CASE T020.SENS WHEN 1 THEN -1 ELSE 1 END * Round(MOUV.PUSTAT * MOUV.SYREFFACQTE, 2)
               ELSE 0 
             END) AS COL7,
         0 AS COL9,
         0 AS COL10,
         0 AS COL12,
         0 AS COL13,
         0 AS COL15,
         0 AS COL16,
         Grouping(T029_21.STAT + ' - ' + T029_21.LIB) AS GRP1, 
         Grouping(T029_22.STAT + ' - ' + T029_22.LIB) AS GRP2
    FROM dbo.MOUV 
         INNER JOIN dbo.ENT 
           ON ENT.DOS  = MOUV.DOS 
          AND ENT.PINO = MOUV.FANO 
         INNER JOIN dbo.T020 
           ON T020.OP = MOUV.OP 
         INNER JOIN dbo.CLI 
           ON CLI.DOS   = ENT.DOS
          AND CLI.TIERS = ENT.TIERS 
         INNER JOIN dbo.ART 
           ON MOUV.DOS = ART.DOS 
          AND MOUV.REF = ART.REF 
         LEFT OUTER JOIN dbo.T036 
           ON T036.PRODNAT = ART.PRODNAT
          AND T036.DOS          = 999
          AND T036.SYTYPEFRAIS IN (0, 1)
         LEFT OUTER JOIN dbo.T029 AS T029_21 
           ON T029_21.DOS    = MOUV.DOS 
          AND T029_21.STAT   = LEFT(CLI.STAT_0002, 1) 
          AND T029_21.CEBIN  = 29 
          AND T029_21.STATNO = 2 
         LEFT OUTER JOIN dbo.T029 AS T029_22 
           ON T029_22.DOS    = MOUV.DOS 
          AND T029_22.STAT   = LEFT(CLI.STAT_0002, 3) 
          AND T029_22.CEBIN  = 29 
          AND T029_22.STATNO = 2 
   WHERE MOUV.TICOD = 'C' 
     AND MOUV.CE8   = '1' 
     AND MOUV.PICOD = 4
     AND ENT.TICOD  = 'C' 
     AND ENT.PICOD  = 4 
     AND ENT.CE4    = 1 
     AND T020.CEBIN = 20 
     AND T020.DOS   = 999
     AND CLI.CE1    = 3
     AND ENT.PIDT BETWEEN DATEADD(year, DATEDIFF(year,  0, getdate())-1,  0)
                      AND DATEADD(year, DATEDIFF(year, -1, getdate())  , -1)
     AND ENT.DOS    = 50 
     AND ENT.CE4    = '1' 
     AND ENT.TICOD  = 'C' 
     AND ENT.PICOD  = 4 
     AND MOUV.FACE4 = 1 
GROUP BY T029_21.STAT + ' - ' + T029_21.LIB, 
         T029_22.STAT + ' - ' + T029_22.LIB WITH ROLLUP;

**nemo67** · 29/09/2010, 22h57

Oui Waldar ca donne la même chose.

Par contre c'est beaucoup plus lent que la requête d'origine ?

Je ne sais pas quelle cde utiliser pour analyser ca : afficher les 2 plans d'exécutions peut être ?

Requête plus rapide ?

Développement SQL Server

Discussions similaires

Partager

Partager