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) ?

**cmako** · 18/09/2009, 16h37

Envoyé par Waldar

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

7 minutes

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

Et bien vous avez votre réponse, ce temps-là est incompressible (à moins de voir si la vue est bien ou mal écrite).

**cmako** · 18/09/2009, 17h08

Envoyé par Waldar

Et bien vous avez votre réponse, ce temps-là est incompressible (à moins de voir si la vue est bien ou mal écrite).

Si je remplace

Code :

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

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

par '20090831', ça ne prends que 2 minutes. Je pensais que l'on pourrait s'y approcher.

**Waldar** · 18/09/2009, 17h30

Oui mais vous me dites qu'il faut 7 minutes pour exécuter ceci :

Code :

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

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

**Sergejack** · 18/09/2009, 22h54

Je tenterais l'utilisation de WITH.

**cmako** · 21/09/2009, 08h30

Envoyé par Waldar

Oui mais vous me dites qu'il faut 7 minutes pour exécuter ceci :

Code :

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

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

Non, j'ai mal compris la requête est quasi instantanée, c'est une vue qui est définie comme ça:

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE VIEW V_DWH_REP_DAT AS
	SELECT
		COD_EXT AS LABEL,
		LIB AS DATE
	FROM
		dbo.DWH_LIB
	WHERE
		COD_NAME = 'D_REPORTING'
	AND LANG = 'FR'
GO

la table DHW_LIB contient une centaine de lignes.

Ce qui prends 7 minutes c'est la requête avec CROSS JOIN

**Leelith** · 21/09/2009, 09h03

pourquoi ne pas utiliser des index?

Mes notions de BDD (plus sur Oracle dailleurs) remontes à quelques temps, mais il me semble que de précalculer certaines choses fait gagner pas mal de temps au calcul.

**Waldar** · 21/09/2009, 09h16

Je suis un peu perdu dans le typage de votre donnée !
Si le champ LIB de dbo.DWH_LIB est du varchar, il faut le convertir en date.

Si c'est une date, le laisser en l'état.

La factorisation avec la syntaxe WITH pourrait fonctionner, mais je serai très étonné que Business Objects sache la gérer.

**cmako** · 21/09/2009, 11h30

Envoyé par Sergejack

Je tenterais l'utilisation de WITH.

Envoyé par Waldar

Je suis un peu perdu dans le typage de votre donnée !
Si le champ LIB de dbo.DWH_LIB est du varchar, il faut le convertir en date.

Si c'est une date, le laisser en l'état.

La factorisation avec la syntaxe WITH pourrait fonctionner, mais je serai très étonné que Business Objects sache la gérer.

La colonne DWH_LIB.LIB est Varchar donc le champ V_DWH_REP_DAT.DATE est de type Varchar également. C'est pour ça qu'il y a des COVERT partout. J'ai essayé, afin d'améliorer les perfs, de définir dans ma vue avec le champ DATE de type DATE. Mais cela n'a pas amélioré les perfs.

J'ai essayé aussi avec WITH, mais cela n'améliore pas les perfs.

Dans BO on peut ajouter des choses en fin d'SQL et aussi (depuis la version XI R3.1) au début d'SQL.

**Waldar** · 21/09/2009, 12h00

Question bête, tous vos champs auxquels vous comparez cette valeur sont bien de type date (FACT_RES_APP.RES_D_DEB_VER, FACT_RES_APP.RES_D_FIN_VER, et cetera) ?

**cmako** · 21/09/2009, 12h26

Envoyé par Waldar

Question bête, tous vos champs auxquels vous comparez cette valeur sont bien de type date (FACT_RES_APP.RES_D_DEB_VER, FACT_RES_APP.RES_D_FIN_VER, et cetera) ?

Oui.

**Waldar** · 21/09/2009, 12h45

Il faudrait comparer les plans d'exécution entre la requête avec la valeur en dur et celle avec la requête rajoutée dans le from.

**SQLpro** · 21/09/2009, 16h32

Vous ne nous avez pas dit en quelle version vous travaillez... 2000, 2005 ? 2008 ???

Sachez qu'à partir de 2005 IL EST IMPÉRATIF de suffixer tous vos objets par le schéma (même si c'est partout dbo) sous peine de perte de performances très importantes (résolution de nom, perte du plan cache...).

A +

**cmako** · 21/09/2009, 16h50

Envoyé par SQLpro

Vous ne nous avez pas dit en quelle version vous travaillez... 2000, 2005 ? 2008 ???

Sachez qu'à partir de 2005 IL EST IMPÉRATIF de suffixer tous vos objets par le schéma (même si c'est partout dbo) sous peine de perte de performances très importantes (résolution de nom, perte du plan cache...).

A +

C'était bien du 2005, mais je n'ai jamais lu ce genre de choses. C'est écrit sur MSDN?

Problème d'optimisation de requête

Développement SQL Server

Discussions similaires

Partager

Partager