Index sur vue contenant une fonction

**Wil14** · 23/05/2012, 19h31

Bonjour,

J'ai le message d'erreur suivant lorsque je veux créer un index sur une vue contenant une fonction scalaire :

Impossible de créer index dans la vue "basename.dbo.mavue", car la fonction "dbo.mafonction" référencée par la vue permet uniquement l'accès aux données par l'utilisateur ou le système. (Microsoft SQL Server, Erreur*: 10133)

la vue et la fonction sont créées avec WITH SCHEMABINDING

Pouvez-vous m'aider ?

**SQLpro** · 23/05/2012, 23h11

Postez le DDL de la vue et de la fonction.

A +

**Wil14** · 24/05/2012, 08h02

Bonjour,

Voici :

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
 
CREATE function [dbo].[fn_Taf_GetLastAction2] (@demande int)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
	DECLARE @I INT
 
	SET @I=(SELECT MAX(act_id) from dbo.taf_action 
	inner join dbo.taf_type_action on tac_code=act_action 
	where act_demande=@demande and tac_majstatut=1 
	and act_dt=(SELECT MAX(act_dt) 
		from dbo.taf_action 
		inner join dbo.taf_type_action on tac_code=act_action 
		where act_demande=@demande and tac_majstatut=1)
	)
    RETURN(@I)
END
 
create view [dbo].[v_taf_demande]
with SCHEMABINDING
as
select dem_id, act_id, act_action, dbo.fn_Taf_GetLastPlanification(dem_id) dem_lastplanif 
from dbo.taf_demande
inner join dbo.taf_action on act_id=dbo.fn_Taf_GetLastAction2(dem_id)

**SQLpro** · 24/05/2012, 12h14

Une vue indexée ne peut pas avoir de sous requête. Or votre UDF contient une requête. L'appel de cet UDF dans une requête est une sous requête.

Tentez de réintégrer le code SQL de votre UDF dans la requête principale et sans sous requêtes (avec des jointures).

Cela dit, il n'y a aucun intérêt à faire dune vue indexée dans votre cas. Le gain serait quasi null.

L'intérêt des vues indexées est de précalculer notamment des agrégats, afin d'éviter de scruter des millions de lignes ou encore de réduire des jointures très distantes.

A +

**Wil14** · 24/05/2012, 12h23

Ok, merci pour votre réponse rapide.
Dans mon cas, je souhaite (très souvent dans l'appli) filtrer sur la dernière action qui a eu lieu.
Faut-il mieux que je dénormalise ? (Avec trigger pour mise à jour et index dessus)

**SQLpro** · 24/05/2012, 18h22

Une requête bien faite et un bon index suffira largement. Sauf si vous avez plusieurs centaines de millions de lignes et une cardinalité des différentes actions d'un même groupe très importantes.

A +

**Wil14** · 24/05/2012, 18h51

Pour le moment je n'ai que 6700 lignes.
Ma requête doit ressortir uniquement 36 lignes.
En testant le modèle actuel et un modèle non normalisé j'obtiens un coût 75%/25% (2,24/0,76)
J'abuse, mais voyez-vous un moyen d'optimiser la requête sur le modèle normalisé :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
select dem_id, cli_rsoc, dem_domaine, (SELECT MIN(act_dt) from dbo.taf_action where act_demande=dem_id) dt_creat,
dem_priorite, dvp_cdc, tda_demande, dvp_echeance, dem_titre, act_action, dvp_arecetterpar,
ISNULL(pla_assignea,dvp_assignea) assignea, pla_dt, pla_nbjestime
from dbo.taf_demande
left outer join dbo.taf_dvp on dvp_demande=dem_id
left outer join dbo.taf_da on tda_demande=dem_id
left outer join dbo.taf_planification on pla_id=dbo.fn_Taf_GetLastPlanification(dem_id)
inner join taf_action on act_id=dbo.fn_Taf_GetLastAction2(dem_id)
inner join gal_client on cli_id=dem_client
left outer join gal_personne on per_id=ISNULL(pla_assignea,dvp_assignea)
where act_action='SUP'
order by dt_creat desc

**SQLpro** · 25/05/2012, 12h37

1) Postez la définition des tables, des index et des UDF associés.
2) réintégrez le code des UDF dans la requête, sauf s'il s'agit d'UDF scalaire ne lisant pas de tables.
3) Ajoutez des alias à toutes vos tables et préfixez toutes vos colonnes par ces alias. Cela nous permet de savoir d’où elle viennent.

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
SELECT cli_rsoc, 
       dem_id, dem_domaine, dem_priorite, dem_titre, 
       dvp_cdc, dvp_echeance, dvp_arecetterpar,
       tda_demande, 
       act_action, 
       (SELECT MIN(act_dt) 
        FROM   dbo.taf_action 
        WHERE  act_demande=dem_id) AS dt_creat,
       COALESCE(pla_assignea, dvp_assignea) assignea, pla_dt, pla_nbjestime
FROM   dbo.taf_demande
      LEFT OUTER JOIN dbo.taf_dvp 
           ON dvp_demande=dem_id
      LEFT OUTER JOIN dbo.taf_da 
           ON tda_demande=dem_id
      LEFT OUTER JOIN dbo.taf_planification 
           ON pla_id=dbo.fn_Taf_GetLastPlanification(dem_id)
      INNER JOIN taf_action 
           ON act_id=dbo.fn_Taf_GetLastAction2(dem_id)
      INNER JOIN gal_client 
           ON cli_id=dem_client
      LEFT OUTER JOIN gal_personne 
           ON per_id = COALESCE(pla_assignea, dvp_assignea)
WHERE act_action='SUP'
ORDER BY dt_creat DESC

Comme cela on pourra vous donner un plan d'indexation efficace.

Déjà :
y a t-il un index sur dbo.taf_action (act_demande, act_dt) ?

A +

**Wil14** · 25/05/2012, 14h16

Bonjour,

Voici les définitions :

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
 
CREATE TABLE [dbo].[taf_demande](
	[dem_id] [int] IDENTITY(1,1) NOT NULL,
	[dem_titre] [nvarchar](100) NOT NULL,
	[dem_groupe] [smallint] NULL,
	[dem_client] [int] NOT NULL,
	[dem_agence] [int] NULL,
	[dem_domaine] [nvarchar](3) NULL,
	[dem_description] [nvarchar](max) NULL,
	[dem_demandeur] [int] NULL,
	[dem_hardware] [int] NULL,
	[dem_programme] [nvarchar](50) NULL,
	[dem_dll] [nvarchar](50) NULL,
	[dem_priorite] [tinyint] NOT NULL,
	[dem_baf] [bit] NULL,
	[dem_categ] [nchar](3) NULL,
 CONSTRAINT [PK__taf_dema__00AA6D8157B623F6] PRIMARY KEY CLUSTERED 
(
	[dem_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
GO
CREATE TABLE [dbo].[taf_type_action](
	[tac_code] [nvarchar](3) NOT NULL,
	[tac_lib] [nvarchar](45) NULL,
	[tac_sc] [bit] NOT NULL,
	[tac_dvp] [bit] NOT NULL,
	[tac_mat] [bit] NOT NULL,
	[tac_tache] [bit] NOT NULL,
	[tac_majstatut] [bit] NOT NULL,
	[tac_visible] [bit] NOT NULL,
	[tac_trsp] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[tac_code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
CREATE TABLE [dbo].[taf_action](
	[act_id] [int] IDENTITY(1,1) NOT NULL,
	[act_demande] [int] NOT NULL,
	[act_personne] [int] NOT NULL,
	[act_action] [nvarchar](3) NOT NULL,
	[act_dt] [datetime] NOT NULL,
	[act_obs] [nvarchar](max) NULL,
	[act_au] [datetime] NULL,
	[act_bafclient] [bit] NULL,
	[act_trsp] [nvarchar](32) NULL,
	[act_suivi] [nvarchar](32) NULL,
	[act_bl] [int] NULL,
	[act_vers] [int] NULL,
 CONSTRAINT [PK__taf_acti__EBC8309567A74151] PRIMARY KEY CLUSTERED 
(
	[act_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
 
GO
ALTER TABLE [dbo].[taf_action]  WITH CHECK ADD  CONSTRAINT [FK_taf_action_taf_type_action1] FOREIGN KEY([act_action])
REFERENCES [dbo].[taf_type_action] ([tac_code])
GO
CREATE NONCLUSTERED INDEX [IX_act_demande] ON [dbo].[taf_action]
(
	[act_demande] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_act_action] ON [dbo].[taf_action]
(
	[act_action] ASC
)
INCLUDE ( 	[act_id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE TABLE [dbo].[taf_dvp](
	[dvp_demande] [int] NOT NULL,
	[dvp_projet] [int] NULL,
	[dvp_echeance] [datetime] NULL,
	[dvp_arecetterpar] [char](1) NULL,
	[dvp_theme] [nvarchar](32) NULL,
	[dvp_cdc] [bit] NOT NULL,
	[dvp_assignea] [int] NULL,
PRIMARY KEY CLUSTERED 
(
	[dvp_demande] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
ALTER TABLE [dbo].[taf_dvp]  WITH CHECK ADD  CONSTRAINT [FK_taf_dvp_taf_demande] FOREIGN KEY([dvp_demande])
REFERENCES [dbo].[taf_demande] ([dem_id])
GO
 
ALTER TABLE [dbo].[taf_dvp] CHECK CONSTRAINT [FK_taf_dvp_taf_demande]
GO
CREATE TABLE [dbo].[taf_da](
	[tda_demande] [int] NOT NULL,
	[tda_perappelant] [int] NULL,
	[tda_perdepanner] [int] NULL,
	[tda_categ] [nvarchar](3) NULL,
	[tda_type] [nvarchar](3) NULL,
	[tda_complexite] [int] NULL,
	[tda_moss] [int] NULL,
	[tda_origine] [nvarchar](3) NULL,
PRIMARY KEY CLUSTERED 
(
	[tda_demande] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
ALTER TABLE [dbo].[taf_da]  WITH CHECK ADD  CONSTRAINT [FK_taf_da_taf_demande] FOREIGN KEY([tda_demande])
REFERENCES [dbo].[taf_demande] ([dem_id])
GO
 
ALTER TABLE [dbo].[taf_da] CHECK CONSTRAINT [FK_taf_da_taf_demande]
GO
USE [NciDesk]
GO
CREATE TABLE [dbo].[taf_planification](
	[pla_id] [int] IDENTITY(1,1) NOT NULL,
	[pla_demande] [int] NOT NULL,
	[pla_dt] [date] NOT NULL,
	[pla_nbjestime] [decimal](5, 3) NULL,
	[pla_dtcreation] [datetime] NOT NULL,
	[pla_creationpar] [int] NOT NULL,
	[pla_assignea] [int] NULL,
	[pla_action] [nvarchar](3) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[pla_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
ALTER TABLE [dbo].[taf_planification]  WITH CHECK ADD  CONSTRAINT [FK_taf_planification_taf_demande] FOREIGN KEY([pla_demande])
REFERENCES [dbo].[taf_demande] ([dem_id])
GO
 
ALTER TABLE [dbo].[taf_planification] CHECK CONSTRAINT [FK_taf_planification_taf_demande]
GO
 
ALTER TABLE [dbo].[taf_planification]  WITH CHECK ADD  CONSTRAINT [FK_taf_planification_taf_type_action] FOREIGN KEY([pla_action])
REFERENCES [dbo].[taf_type_action] ([tac_code])
GO
CREATE NONCLUSTERED INDEX [IX_pla_demande] ON [dbo].[taf_planification]
(
	[pla_demande] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
 
 
ALTER TABLE [dbo].[taf_planification] CHECK CONSTRAINT [FK_taf_planification_taf_type_action]
GO
CREATE TABLE [dbo].[gal_client](
	[cli_id] [int] IDENTITY(1,1) NOT NULL,
	[cli_code] [nvarchar](10) NOT NULL,
	[cli_rsoc] [nvarchar](50) NOT NULL,
	[cli_groupe] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[cli_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
CREATE TABLE [dbo].[gal_personne](
	[per_id] [int] IDENTITY(1,1) NOT NULL,
	[per_nom] [nvarchar](32) NOT NULL,
	[per_prenom] [nvarchar](64) NOT NULL,
	[per_tel] [nvarchar](20) NOT NULL,
	[per_email] [nvarchar](128) NOT NULL,
	[per_fax] [nvarchar](20) NOT NULL,
	[per_login] [nvarchar](64) NOT NULL,
	[per_cil] [bit] NOT NULL,
	[per_groupe] [int] NOT NULL,
	[per_client] [int] NOT NULL,
	[per_agence] [int] NOT NULL,
 CONSTRAINT [PK__gal_pers__32A15E675770E1D2] PRIMARY KEY CLUSTERED 
(
	[per_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
CREATE function [dbo].[fn_Taf_GetLastPlanification] (@demande int)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
	DECLARE @I INT
 
	SET @I=(SELECT MAX(pla_id) from dbo.taf_planification
	where pla_demande=@demande and pla_dt=(SELECT MAX(pla_dt) 
		from dbo.taf_planification		
		where pla_demande=@demande)
	)
    RETURN(@I)
END
 
GO
CREATE function [dbo].[fn_Taf_GetLastAction2] (@demande int)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
	DECLARE @I INT
 
	SET @I=(SELECT MAX(act_id) from dbo.taf_action 
	inner join dbo.taf_type_action on tac_code=act_action 
	where act_demande=@demande and tac_majstatut=1 
	and act_dt=(SELECT MAX(act_dt) 
		from dbo.taf_action 
		inner join dbo.taf_type_action on tac_code=act_action 
		where act_demande=@demande and tac_majstatut=1)
	)
    RETURN(@I)
END

Je préfixe déjà mes colonnes dans leur nom :
taf_demande -> dem_
taf_da -> tda_
taf_dvp -> dvp_
taf_action -> act_
taf_planification -> pla_
gal_client -> cli_
gal_personne -> per_

Sur taf_action il existe 2 index :
-act_demande
et
-act_action (include act_id)

COALESCE et meilleur que ISNULL ?

**SQLpro** · 25/05/2012, 15h56

Voici un début :

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
WITH 
Taf_GetLastPlanification AS
(
SELECT MAX(P.pla_id) as pla_id_max, P.pla_demande
FROM   dbo.taf_planification AS P
WHERE  pla_dt =(SELECT MAX(pla_dt) 
		        FROM   dbo.taf_planification AS P2		
		        WHERE  P2.pla_demande=P.pla_demande)
GROUP  BY pla_demande		        
),
Taf_GetLastAction2 AS
(
SELECT MAX(A.act_id) AS act_id_max,  A.act_demande
FROM   dbo.taf_action AS A
	   INNER JOIN dbo.taf_type_action  AS TA
	         ON TA.tac_code=A.act_action 
WHERE  TA.tac_majstatut=1 
  AND  A.act_dt=(SELECT MAX(act_dt) 
		         FROM   dbo.taf_action AS A2
		                INNER JOIN dbo.taf_type_action AS TA2
		                      ON   TA2.tac_code=A2.act_action 
		         WHERE  A2.act_demande=A.act_demande
		           AND  tac_majstatut=1)
GROUP  BY A.act_demande		           
)
SELECT cli_rsoc, 
       dem_id, dem_domaine, dem_priorite, dem_titre, 
       dvp_cdc, dvp_echeance, dvp_arecetterpar,
       tda_demande, 
       act_action, 
       (SELECT MIN(act_dt) 
        FROM   dbo.taf_action 
        WHERE  act_demande=dem_id) AS dt_creat,
       COALESCE(pla_assignea, dvp_assignea) assignea, pla_dt, pla_nbjestime
FROM   dbo.taf_demande
       LEFT OUTER JOIN dbo.taf_dvp 
            ON dvp_demande=dem_id
       LEFT OUTER JOIN dbo.taf_da 
            ON tda_demande=dem_id
       LEFT OUTER JOIN dbo.taf_planification 
            ON pla_id= ??? --remplacez par Taf_GetLastPlanification
       INNER JOIN taf_action 
            ON act_id = ??? --Remplacez par Taf_GetLastAction2
       INNER JOIN gal_client 
            ON cli_id=dem_client
       LEFT OUTER JOIN gal_personne 
            ON per_id = COALESCE(pla_assignea, dvp_assignea)
WHERE  act_action='SUP'
ORDER  BY dt_creat DESC

mais SVP rerspectez ce que l'on vous demande :

3) Ajoutez des alias à toutes vos tables et préfixez toutes vos colonnes par ces alias. Cela nous permet de savoir d’où elle viennent.

je ne peut pas passer mon temps à essayer de chercher partout vos colonnes...

A +

**Wil14** · 29/05/2012, 14h00

Bonjour,

Voici la version d'origine avec alias

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 TDEMANDE.dem_id, TCLIENT.cli_rsoc, TDEMANDE.dem_domaine, 
(SELECT MIN(rechercheaction.act_dt) FROM dbo.taf_action AS rechercheaction WHERE rechercheaction.act_demande=TDEMANDE.dem_id) AS dt_creat,
TDEMANDE.dem_priorite, TDVP.dvp_cdc, TDA.tda_demande, TDVP.dvp_echeance, TDEMANDE.dem_titre, TACTION.act_action, TDVP.dvp_arecetterpar,
ISNULL(TPLANIF.pla_assignea,TDVP.dvp_assignea) assignea, TPLANIF.pla_dt, TPLANIF.pla_nbjestime
FROM dbo.taf_demande AS TDEMANDE
LEFT OUTER JOIN dbo.taf_dvp AS TDVP ON dvp_demande=TDEMANDE.dem_id
LEFT OUTER JOIN dbo.taf_da AS TDA ON tda_demande=TDEMANDE.dem_id
LEFT OUTER JOIN dbo.taf_planification AS TPLANIF ON TPLANIF.pla_id=dbo.fn_Taf_GetLastPlanification(TDEMANDE.dem_id)
INNER JOIN taf_action AS TACTION ON TACTION.act_id=dbo.fn_Taf_GetLastAction2(TDEMANDE.dem_id)
INNER JOIN gal_client AS TCLIENT ON TCLIENT.cli_id=TDEMANDE.dem_client
LEFT OUTER JOIN gal_personne AS TPERSONNE ON TPERSONNE.per_id=ISNULL(TPLANIF.pla_assignea,TDVP.dvp_assignea)
WHERE TACTION.act_action='SUP'
ORDER BY dt_creat DESC

Et votre version :

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
 
WITH 
Taf_GetLastPlanification AS
(
SELECT MAX(P.pla_id) AS pla_id_max, P.pla_demande
FROM   dbo.taf_planification AS P
WHERE  pla_dt =(SELECT MAX(pla_dt) 
		        FROM   dbo.taf_planification AS P2		
		        WHERE  P2.pla_demande=P.pla_demande)
GROUP  BY pla_demande		        
),
Taf_GetLastAction2 AS
(
SELECT MAX(A.act_id) AS act_id_max,  A.act_demande
FROM   dbo.taf_action AS A
	   INNER JOIN dbo.taf_type_action  AS TA
	         ON TA.tac_code=A.act_action 
WHERE  TA.tac_majstatut=1 
  AND  A.act_dt=(SELECT MAX(act_dt) 
		         FROM   dbo.taf_action AS A2
		                INNER JOIN dbo.taf_type_action AS TA2
		                      ON   TA2.tac_code=A2.act_action 
		         WHERE  A2.act_demande=A.act_demande
		           AND  tac_majstatut=1)
GROUP  BY A.act_demande		           
)
SELECT TCLIENT.cli_rsoc, 
       TDEMANDE.dem_id, TDEMANDE.dem_domaine, TDEMANDE.dem_priorite, TDEMANDE.dem_titre, 
       TDVP.dvp_cdc, TDVP.dvp_echeance, TDVP.dvp_arecetterpar,
       TDA.tda_demande, 
       TACTION.act_action, 
       (SELECT MIN(TMINACT.act_dt) 
        FROM   dbo.taf_action AS TMINACT
        WHERE  TMINACT.act_demande=TDEMANDE.dem_id) AS dt_creat,
       COALESCE(TPLANIF.pla_assignea, TDVP.dvp_assignea) assignea, TPLANIF.pla_dt, TPLANIF.pla_nbjestime
FROM   dbo.taf_demande AS TDEMANDE
		LEFT OUTER JOIN Taf_GetLastPlanification AS CTELASTPLANIF
			ON CTELASTPLANIF.pla_demande=TDEMANDE.dem_id
		LEFT OUTER JOIN Taf_GetLastAction2 AS CTELASTACTION
			ON CTELASTACTION.act_demande=TDEMANDE.dem_id
       LEFT OUTER JOIN dbo.taf_dvp AS TDVP
            ON TDVP.dvp_demande=TDEMANDE.dem_id
       LEFT OUTER JOIN dbo.taf_da AS TDA
            ON TDA.tda_demande=TDEMANDE.dem_id
       LEFT OUTER JOIN dbo.taf_planification AS TPLANIF
            ON TPLANIF.pla_id= CTELASTPLANIF.pla_id_max
       INNER JOIN taf_action AS TACTION
            ON TACTION.act_id = CTELASTACTION.act_id_max
       INNER JOIN gal_client AS TCLIENT
            ON TCLIENT.cli_id=TDEMANDE.dem_client
       LEFT OUTER JOIN gal_personne AS TPERSONNE
            ON TPERSONNE.per_id = COALESCE(TPLANIF.pla_assignea, TDVP.dvp_assignea)
WHERE  TACTION.act_action='SUP'
ORDER  BY dt_creat DESC

Lors de l’exécution, j'ai l'impression que votre version est plus rapide en revanche le coût est plus élevé (2,25/3,8)

Les 2 CTE ne vont pas parcourir entièrement les tables à chaque fois ?

**SQLpro** · 29/05/2012, 16h52

Une fonction table est estimée systématiquement avec une cardinalité de 1 ligne, ce qui fausse le cout des plans de requête. En effet il est impossible d'estimer le nombre de ligne d'une table dont la génération des données dépend d'une variable que l'on passera en argument... Or c'est bien le cas d'une UDF de type table... A mon sens ce genre de chose constitue un "trou noir" pour l'optimisation.
De ce fait il est logique que le coût du plan vous apparaisse comme meilleur avec les UDFs table, alors qu'en réalité il va s'avérer moins bon !!!

Ensuite, pour optimiser encore votre requête, ajoutez les index adéquat en commençant par les CTE de tête.

Par exemple pour la 1er CTE, assurez vous d'avoir les index suivants :

Code :

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

1
2
3
4
5
6
CREATE INDEX X1 
   ON dbo.taf_planification (pla_demande, pla_dt) ;
 
CREATE INDEX X2 
   ON dbo.taf_planification (pla_dt, pla_id)    
   INCLUDE (pla_demande);

Pour mesurer les coûts réels, utilisez les paramètres
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

A +

**Wil14** · 29/05/2012, 18h16

Merci pour vos éclaircissements.

Voici les stats TIME :
Version fonctions : Temps UC = 1531*ms, temps écoulé = 1764*ms
Version CTE : Temps UC = 125*ms, temps écoulé = 178*ms
Avec les nouveaux index :
Version fonctions : Temps UC = 1375*ms, temps écoulé = 1525*ms
Version CTE : Temps UC = 140*ms, temps écoulé = 150*ms

**SQLpro** · 29/05/2012, 23h00

Pas flagrant, il faut continuer à poser des index, voire, créer une vue indexée pour simplifier les CTE.

A +

**Wil14** · 30/05/2012, 22h54

Bonsoir,

Je suis parti sur la création de vues indexées mais sans succès.
En effet, impossible de créer une vue indexée si celle-ci contient une CTE, une sous requête ou bien l’agrégat MAX.

Si l'on ne peut indexer seulement sur une vue contenant un select "classique", quel est l'avantage de réaliser une vue indexé par rapport à un index classique sur la table ?

Index sur vue contenant une fonction

Développement SQL Server

Discussions similaires

Partager

Partager