Problème d'optimisation de requête

**cmako** · 18/09/2009, 11h33

J'ai une requête qui est générée par BusinessObjects qui prends un temps fou.

Voici la fameuse 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
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
SELECT
  (SELECT CONVERT(DATETIME, DATE, 103) FROM V_DWH_REP_DAT  WHERE LABEL = ( 'FIN MOIS PREC' )),
  V_DIM_POL_LIB.POL_NO_POL,
  V_DIM_POL_LIB.POL_D_EFF,
  V_DIM_POL_LIB.POL_C_DEV,
  V_DIM_POL_LIB.POL_PAY_FISC_LIB,
  V_DIM_PRO_LIB.PRO_C_PRO, 
  V_DIM_PRO_LIB.PRO_LABEL,
  V_DIM_GAR_LIB.GAR_C_GAR,
  FACT_PUC.PUC_UC_QTE,
  FACT_PUC.PUC_M_RM_VAL,
  CASE WHEN ( FACT_PUC.PUC_M_RM ) < 0 THEN 'D' ELSE 'C' END,
  V_DIM_FDS_LIB.FDS_ID,
  V_DIM_FDS_LIB.FDS_FDS_LIB,
  V_DIM_FDS_LIB.FDS_FAM_LIB,
  V_DIM_FDS_LIB.FDS_C_FAM,
  V_DIM_FDS_LIB.FDS_COMP_INT_LIB,
  V_DIM_FDS_LIB.FDS_C_COMP_INT,
  V_DIM_FDS_LIB.FDS_CAT_LIB,
  V_DIM_FDS_LIB.FDS_C_CAT,
  V_DIM_FDS_LIB.FDS_DEV_LIB,
  V_DIM_FDS_LIB.FDS_C_DEV,
  FACT_PUC.PUC_D_RES,
  FACT_PUC.PUC_D_VNI,
  FACT_PUC.PUC_VNI,
  FACT_PUC.PUC_TX_CHG,
  FACT_PUC.PUC_M_RM,
  V_DIM_FDS_LIB.FDS_SIT_LIB,
  V_DIM_FDS_LIB.FDS_C_SIT,
  V_DIM_POL_LIB.POL_D_FIN,
  FACT_RES_APP.RES_AP1_ID,
  FACT_RES_APP.RES_AP2_ID,
  FACT_RES_APP.RES_INS_ID,
  V_TIE_INS.TIE_NOM1,
  V_TIE_INS.TIE_NOM2,
  V_TIE_CO1.TIE_ID,
  V_TIE_CO1.TIE_TYPE_LIB,
  V_TIE_CO1.TIE_NOM1,
  V_TIE_CO1.TIE_NOM2,
  V_TIE_CO2.TIE_ID,
  V_TIE_CO2.TIE_NOM1,
  V_TIE_CO2.TIE_NOM2,
  V_TIE_AS1.TIE_ID,
  V_FACT_SRL_CO1_LIB.SRL_ADR_LIGNE1,
  V_FACT_SRL_CO1_LIB.SRL_ADR_LIGNE2,
  V_FACT_SRL_CO1_LIB.SRL_ADR_LIGNE3,
  V_FACT_SRL_CO1_LIB.SRL_ADR_C_POST,
  V_FACT_SRL_CO1_LIB.SRL_ADR_VILLE,
  V_FACT_SRL_CO1_LIB.SRL_ADR_PAYS_LIB,
  V_TIE_CO1.TIE_LANG_LIB,
  V_TIE_CO1.TIE_NATIO_LIB,
  V_DIM_ADR_CO1.ADR_TYPE_ADR_LIB,
  V_DIM_ADR_CO1.ADR_ORDRE,
  V_DIM_ADR_CO1.ADR_NO_TEL,
  V_TIE_CO1.TIE_SEXE,
  V_TIE_CO1.TIE_D_NAISS
FROM V_DIM_TIE_LIB  V_TIE_INS 
RIGHT OUTER JOIN FACT_RES_APP ON (FACT_RES_APP.RES_INS_KEY=V_TIE_INS.TIE_KEY)
LEFT OUTER JOIN V_DIM_POL_LIB ON ((
  (SELECT CONVERT(DATETIME, DATE,103) FROM V_DWH_REP_DAT  WHERE LABEL ='FIN MOIS PREC')
 
  BETWEEN FACT_RES_APP.RES_D_DEB_VER AND FACT_RES_APP.RES_D_FIN_VER)
AND V_DIM_POL_LIB.POL_KEY=FACT_RES_APP.RES_POL_KEY)
INNER JOIN V_DIM_GAR_LIB ON (V_DIM_POL_LIB.POL_PRO_KEY=V_DIM_GAR_LIB.GAR_PRO_KEY)
INNER JOIN V_DIM_PRO_LIB ON (V_DIM_PRO_LIB.PRO_KEY=V_DIM_GAR_LIB.GAR_PRO_KEY)
LEFT OUTER JOIN V_FACT_ROL_CO2_LIB 
  ON (((SELECT CONVERT(DATETIME, DATE,103) FROM V_DWH_REP_DAT  WHERE LABEL ='FIN MOIS PREC')
    BETWEEN V_FACT_ROL_CO2_LIB.ROL_D_DEB_VER AND V_FACT_ROL_CO2_LIB.ROL_D_FIN_VER)
  AND V_FACT_ROL_CO2_LIB.ROL_POL_KEY=V_DIM_POL_LIB.POL_KEY)
 
LEFT OUTER JOIN V_DIM_TIE_LIB  V_TIE_CO2 
  ON (((SELECT CONVERT(DATETIME, DATE, 103) FROM V_DWH_REP_DAT  WHERE LABEL ='FIN MOIS PREC')
    BETWEEN V_TIE_CO2.TIE_D_DEB_VER AND V_TIE_CO2.TIE_D_FIN_VER)
  AND V_TIE_CO2.TIE_KEY=V_FACT_ROL_CO2_LIB.ROL_TIE_KEY)
 
LEFT OUTER JOIN V_FACT_ROL_CO1_LIB 
  ON (((SELECT CONVERT(DATETIME, DATE, 103) FROM V_DWH_REP_DAT  WHERE LABEL ='FIN MOIS PREC')
    BETWEEN V_FACT_ROL_CO1_LIB.ROL_D_DEB_VER AND V_FACT_ROL_CO1_LIB.ROL_D_FIN_VER )
  AND V_FACT_ROL_CO1_LIB.ROL_POL_KEY=V_DIM_POL_LIB.POL_KEY)
 
LEFT OUTER JOIN V_DIM_TIE_LIB  V_TIE_CO1 
  ON (((SELECT CONVERT(DATETIME, DATE, 103) FROM V_DWH_REP_DAT  WHERE LABEL ='FIN MOIS PREC')
    BETWEEN V_TIE_CO1.TIE_D_DEB_VER AND V_TIE_CO1.TIE_D_FIN_VER)
  AND V_TIE_CO1.TIE_KEY = V_FACT_ROL_CO1_LIB.ROL_TIE_KEY)
 
LEFT OUTER JOIN V_DIM_ADR_LIB  V_DIM_ADR_CO1 ON (V_DIM_ADR_CO1.ADR_TIE_KEY=V_TIE_CO1.TIE_KEY)
LEFT OUTER JOIN V_FACT_SRL_CO1_LIB ON (V_FACT_SRL_CO1_LIB.SRL_POL_KEY=V_FACT_ROL_CO1_LIB.ROL_POL_KEY)
LEFT OUTER JOIN V_FACT_ROL_AS1_LIB 
  ON (((SELECT CONVERT(DATETIME, DATE,103) FROM V_DWH_REP_DAT  WHERE LABEL ='FIN MOIS PREC')
    BETWEEN V_FACT_ROL_AS1_LIB.ROL_D_DEB_VER AND V_FACT_ROL_AS1_LIB.ROL_D_FIN_VER)
  AND V_FACT_ROL_AS1_LIB.ROL_POL_KEY=V_DIM_POL_LIB.POL_KEY)
 
LEFT OUTER JOIN V_DIM_TIE_LIB  V_TIE_AS1 
  ON (((SELECT CONVERT(DATETIME, DATE, 103) FROM V_DWH_REP_DAT  WHERE LABEL ='FIN MOIS PREC')
    BETWEEN V_TIE_AS1.TIE_D_DEB_VER AND V_TIE_AS1.TIE_D_FIN_VER)
  AND V_TIE_AS1.TIE_KEY=V_FACT_ROL_AS1_LIB.ROL_TIE_KEY)
 
LEFT OUTER JOIN FACT_PUC 
  ON (((SELECT CONVERT(DATETIME, DATE,103) FROM V_DWH_REP_DAT  WHERE LABEL ='FIN MOIS PREC')
    BETWEEN FACT_PUC.PUC_D_DEB_VER AND FACT_PUC.PUC_D_FIN_VER) 
  AND FACT_PUC.PUC_POL_KEY=V_DIM_POL_LIB.POL_KEY)
 
INNER JOIN V_DIM_FDS_LIB ON (V_DIM_FDS_LIB.FDS_KEY=FACT_PUC.PUC_FDS_KEY)
 
WHERE
  (
   FACT_PUC.PUC_D_RES  =  (SELECT CONVERT(DATETIME, DATE, 103) FROM V_DWH_REP_DAT  WHERE LABEL = ( 'FIN MOIS PREC' ))
 
   AND
   V_DIM_PRO_LIB.PRO_BRCH_LIB  =  'UNITES DE COMPTE'
   AND
   ( (((SELECT CONVERT(DATETIME, DATE, 103) FROM V_DWH_REP_DAT  WHERE LABEL = ( 'FIN MOIS PREC' ))
  ) BETWEEN V_DIM_POL_LIB.POL_D_DEB_VER AND V_DIM_POL_LIB.POL_D_FIN_VER)
OR  V_DIM_POL_LIB.POL_D_DEB_VER IS NULL
  )
   AND
   ( ((SELECT CONVERT(DATETIME, DATE, 103) FROM V_DWH_REP_DAT  WHERE LABEL = ( 'FIN MOIS PREC' ))
  ) BETWEEN FACT_PUC.PUC_D_DEB_VER AND FACT_PUC.PUC_D_FIN_VER OR FACT_PUC.PUC_D_DEB_VER IS NULL  )
   AND
   ( ( 
  (SELECT CONVERT(DATETIME, DATE, 103) FROM V_DWH_REP_DAT  WHERE LABEL = ( 'FIN MOIS PREC' ))
  ) BETWEEN V_DIM_FDS_LIB.FDS_D_DEB_VER AND V_DIM_FDS_LIB.FDS_D_FIN_VER OR V_DIM_FDS_LIB.FDS_D_DEB_VER IS NULL  )
   AND
   ( ( 
  (SELECT CONVERT(DATETIME, DATE, 103) FROM V_DWH_REP_DAT  WHERE LABEL = ( 'FIN MOIS PREC' ))
  )  BETWEEN FACT_RES_APP.RES_D_DEB_VER AND FACT_RES_APP.RES_D_FIN_VER
OR
FACT_RES_APP.RES_D_DEB_VER IS NULL  )
   AND
   ( ( 
  (SELECT CONVERT(DATETIME, DATE, 103) FROM V_DWH_REP_DAT  WHERE LABEL = ( 'FIN MOIS PREC' ))
  ) BETWEEN V_FACT_ROL_CO1_LIB.ROL_D_DEB_VER AND V_FACT_ROL_CO1_LIB.ROL_D_FIN_VER
OR  V_FACT_ROL_CO1_LIB.ROL_D_DEB_VER IS NULL
  )
   AND
   ( ( 
  (SELECT CONVERT(DATETIME, DATE, 103) FROM V_DWH_REP_DAT  WHERE LABEL = ( 'FIN MOIS PREC' ))
  ) BETWEEN V_FACT_ROL_CO2_LIB.ROL_D_DEB_VER AND V_FACT_ROL_CO2_LIB.ROL_D_FIN_VER
OR  V_FACT_ROL_CO2_LIB.ROL_D_DEB_VER IS NULL
  )
   AND
   ( ( 
  (SELECT CONVERT(DATETIME, DATE, 103) FROM V_DWH_REP_DAT  WHERE LABEL = ( 'FIN MOIS PREC' ))
  )  BETWEEN V_FACT_ROL_AS1_LIB.ROL_D_DEB_VER AND V_FACT_ROL_AS1_LIB.ROL_D_FIN_VER OR V_FACT_ROL_AS1_LIB.ROL_D_DEB_VER IS NULL  )
   AND
   ( ( 
  (SELECT CONVERT(DATETIME, DATE, 103) FROM V_DWH_REP_DAT  WHERE LABEL = ( 'FIN MOIS PREC' ))
  ) BETWEEN V_DIM_ADR_CO1.ADR_D_DEB_VER AND V_DIM_ADR_CO1.ADR_D_FIN_VER
OR
V_DIM_ADR_CO1.ADR_D_DEB_VER IS NULL  )
  )

Comme on peut voir l'expression SELECT CONVERT(DATETIME, DATE, 103) FROM V_DWH_REP_DAT WHERE LABEL = ( 'FIN MOIS PREC' ) apparait très souvent. Cette requête met 15 minutes à s'exécuter

, alors que si on remplace SELECT CONVERT(DATETIME, DATE, 103) FROM V_DWH_REP_DAT WHERE LABEL = ( 'FIN MOIS PREC' ) par '20090831' (qui est la valeur retournée) elle s'exécute en 2 minutes.

Cette requête retourne environs 300.000 lignes.

Comment ça se fait?
J'ai l'impression que SQL server évalue pour chaque ligne cette expression. N'y a-t-il pas moyen dans un SELECT de forcer à évaluer l'expression et ensuite la requête?

**Waldar** · 18/09/2009, 14h19

Il suffit de mettre votre expression dans le FROM (apparement elle ne retourne qu'une ligne, donc vous pouvez la mettre en produit cartésien sans modifier le nombre de lignes de votre requête), de lui donner un nom et de l'appeller.

Au lieu de :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT
    (SELECT CONVERT(DATETIME, DATE, 103) FROM V_DWH_REP_DAT  WHERE LABEL = ( 'FIN MOIS PREC' )),
...
FROM
    V_DIM_TIE_LIB
    RIGHT OUTER JOIN FACT_RES_APP
      ON FACT_RES_APP.RES_INS_KEY = V_TIE_INS.TIE_KEY
    LEFT OUTER JOIN V_DIM_POL_LIB
      ON (SELECT CONVERT(DATETIME, DATE,103) FROM V_DWH_REP_DAT  WHERE LABEL ='FIN MOIS PREC') BETWEEN FACT_RES_APP.RES_D_DEB_VER AND FACT_RES_APP.RES_D_FIN_VER
     AND V_DIM_POL_LIB.POL_KEY=FACT_RES_APP.RES_POL_KEY
...
WHERE
    FACT_PUC.PUC_D_RES = (SELECT CONVERT(DATETIME, DATE, 103) FROM V_DWH_REP_DAT  WHERE LABEL = ( 'FIN MOIS PREC' ))
...

Vous auriez :

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
SELECT
    DFMP.Dt,
....
FROM
    V_DIM_TIE_LIB
    CROSS JOIN (SELECT CONVERT(DATETIME, DATE, 103) AS Dt FROM V_DWH_REP_DAT  WHERE LABEL = 'FIN MOIS PREC' ) AS DFMP
    RIGHT OUTER JOIN FACT_RES_APP
      ON FACT_RES_APP.RES_INS_KEY = V_TIE_INS.TIE_KEY
    LEFT OUTER JOIN V_DIM_POL_LIB
      ON DFMP.Dt BETWEEN FACT_RES_APP.RES_D_DEB_VER AND FACT_RES_APP.RES_D_FIN_VER
     AND V_DIM_POL_LIB.POL_KEY = FACT_RES_APP.RES_POL_KEY
....
WHERE
    FACT_PUC.PUC_D_RES = DFMP.Dt
....

**cmako** · 18/09/2009, 15h45

Envoyé par Waldar

Il suffit de mettre votre expression dans le FROM (apparement elle ne retourne qu'une ligne, donc vous pouvez la mettre en produit cartésien sans modifier le nombre de lignes de votre requête), de lui donner un nom et de l'appeller.

Effectivement, c'est plus rapide (7 min au lieu de 15), mais on n'est pas encore à 2 min comme avec une valeur en dur.

**Waldar** · 18/09/2009, 15h53

On dirait que vous reconvertissez une date en date.
Quel est le type de V_DWH_REP_DAT.DATE ?

**cmako** · 18/09/2009, 15h58

Envoyé par Waldar

On dirait que vous reconvertissez une date en date.
Quel est le type de V_DWH_REP_DAT.DATE ?

Au départ c'était du varchar, après, la conversion se faisait dans la vue. Le Select qui prends 7 minutes, n'a pas de CONVERT, mais la vue V_DWH_REP_DAT dans sa définition en a un.

**Waldar** · 18/09/2009, 16h12

Donc pas besoin de le reconvertir en date alors :

Code :

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

(SELECT DATE AS Dt FROM V_DWH_REP_DAT WHERE LABEL = 'FIN MOIS PREC')

Edit : apparement c'est ce que vous avez fait

Pour information, combien de temps met cette requête à s'éxécuter (celle ci-dessus) ?

Problème d'optimisation de requête

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager