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?