Ecriture jointure réflexive compatible avec la création d'un index sur une vue

**Christophe Charron** · 26/11/2018, 09h57

Bonjour,

je crée une vue qui porte sur les tables suivantes

Code :

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

1
2
3
4
5
FROM            dbo.CLIENT AS CL 
                         INNER JOIN dbo.UCLIENT AS CLU ON CL.CLKTSOC = CLU.CLKTSOC AND CL.CLKTCODE = CLU.CLKTCODE
                         INNER JOIN dbo.PARAM AS PARAM ON CL.CLKTSOC = PARAM.PAKTSOC AND PARAM.PAKTNOPAR = '250' AND CL.CLCTACTIVI = PARAM.PAKTCODE
                         INNER JOIN dbo.REPRESEN AS REP1 ON CL.CLKTSOC = REP1.REKTSOC AND CL.CLCTREP1 = REP1.REKTCODE
                         INNER JOIN dbo.REPRESEN AS REP2 ON CL.CLKTSOC = REP2.REKTSOC AND CL.CLCTREP2 = REP2.REKTCODE

Mais lorsque j'essaie de créer un index dessus, j'obtiens le message suivant :

Code :

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

1
2
Msg*1947, Niveau*16, État*1, Ligne*3
Impossible de créer index sur la vue "ARCE.dbo.V1L_PROSPECTS_01" car celle-ci contient une jointure réflexive sur "ARCE.dbo.REPRESEN".

J'ai absolument besoin de conserver mes deux références à la table "REPRESEN". Je pressens qu'il est possible de "saucissonner" les jointures en utilisant les parenthèses mais je n'y arrive pas ...
Est-ce en effet possible ? Il y a-t-il une autre manière de faire ?
D'autant que je risque d'être obligé de faire plusieurs autres appels à la table "PARAM"...

D'avance, merci pour vos lumières.

**aieeeuuuuu** · 26/11/2018, 12h06

Bonjour,

Peut-on avoir la requête complète ainsi que la structure des tables (notamment dbo.REPRESEN ) ?

Dans quel but voulez-vous indexer la vue ?

**Christophe Charron** · 26/11/2018, 13h37

Envoyé par aieeeuuuuu

Bonjour,

Peut-on avoir la requête complète ainsi que la structure des tables (notamment dbo.REPRESEN ) ?

Dans quel but voulez-vous indexer la vue ?

Bonjour et merci pour votre prompte réponse.

La constitution de la vue :

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
CREATE VIEW [dbo].[V1L_PROSPECTS_01] with schemabinding
AS
SELECT        CL.CLKTSOC, CL.CLCTETS, CONVERT(varchar, CL.CLKTSOC) + CONVERT(varchar, CL.CLCTETS) + CONVERT(varchar, CL.CLKTCODE) AS INDEXCL1, CONVERT(varchar, CL.CLKTSOC) + CONVERT(varchar, 
                         CL.CLKTCODE) + CL.CLCTTYPE AS INDEXCL2, CONVERT(varchar, CL.CLKTSOC) + CONVERT(varchar, CL.CLKTCODE) AS INDEXCL3, CL.CLCTNOM, CL.CLKTCODE, CL.CLCTABRE, RTRIM(CL.CLCTRUE1) 
                         AS CLCTRUE1, RTRIM(CL.CLCTRUE2) AS CLCTRUE2, RTRIM(CL.CLCTRUE3) AS CLCTRUE3, CL.CLCTCP, CL.CLCTVILLE, CL.CLCTPAYS, CL.CLCTCPAYS, CL.CLCTSIRET, CL.CLCTNOTVA, CL.CLCTAPE, 
                         CL.CLCTCONTA1, CL.CLCTCONTA2, CL.CLCTTELEP1, CL.CLCTTELEP2, CL.CLCTFAX1, CL.CLCTFAX2, RTRIM(CL.CLCTEMAIL) AS CLCTEMAIL, RTRIM(CL.CLCTSITE) AS CLCTSITE, CL.CLCTNOFOUR, 
                         CL.CLCTCECHEA, CL.CLCTBECHEA, CL.CLCTCREG, CL.CLCTREGFIS, CL.CLCTTAXE, CL.CLCTBLOCAG, CL.CLCTREP1, COALESCE (REP1.RECTNOM, N'') AS RECTNOM1, CL.CLCTREP2, 
                         COALESCE (REP2.RECTNOM, N'') AS RECTNOM2, CL.CLCTACTIVI, COALESCE (PARAM.PACTEXT140, N'') AS LIBACTIVI, CL.CLCTREGIO, CL.CLCTDEPSTA, CL.CLCTABC, CL.CLCTHGROUP, CL.CLCTSEGMAR, 
                         CL.CLCTCENACH, CL.CLCTZONLIV, CL.CLCTGROUPE, CL.CLCJCRE, CL.CLCJMOU, CL.CLCTLIBRE1, CL.CLCTLIBRE2, CL.CLCTLIBRE3, CL.CLCTLIBRE4, CL.CLCTTYPE, CL.CLCTACTIF, CL.CLCNGPSX, 
                         CL.CLCNGPSY, CLU.CLKTDTERP, CLU.CLKTCOMPT, CLU.CLKTEXPERT, CLU.CLCTTRCA, CLU.CLCTCAO, CLU.CLCTINTCAO, CLU.CLCTSYNDIC, CLU.CLCTSTAT, CLU.CLCTVERS, CLU.CLCTINFOG, CLU.CLCDANNIV, 
                         CLU.CLCCAEFF, CLU.CLCRESCA, CLU.CLCEVORES, CLU.CLCCAEVO, CLU.CLCFPCS, CLU.CLCDATECRE, CLU.CLCEVOFP, CLU.CLCNTRCA, CLU.CLCNEFF, CLU.CLCRECH, CLU.CLCPOTEN, CLU.CLCCOLLAB, 
                         CLU.CLCTFAO, CLU.CLCTBI, CLU.CLCTAUTRE
FROM            dbo.CLIENT AS CL 
                         INNER JOIN dbo.UCLIENT AS CLU ON CL.CLKTSOC = CLU.CLKTSOC AND CL.CLKTCODE = CLU.CLKTCODE
                         INNER JOIN dbo.PARAM AS PARAM ON CL.CLKTSOC = PARAM.PAKTSOC AND PARAM.PAKTNOPAR = '250' AND CL.CLCTACTIVI = PARAM.PAKTCODE
                         INNER JOIN dbo.REPRESEN AS REP1 ON CL.CLKTSOC = REP1.REKTSOC AND CL.CLCTREP1 = REP1.REKTCODE
                         INNER JOIN dbo.REPRESEN AS REP2 ON CL.CLKTSOC = REP2.REKTSOC AND CL.CLCTREP2 = REP2.REKTCODE

La structure de la table REPRESEN

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
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[REPRESEN]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[REPRESEN](
	[REKTSOC] [char](3) COLLATE French_CS_AS_KS_WS NULL,
	[REKTCODE] [char](2) COLLATE French_CS_AS_KS_WS NULL,
	[RECTNOM] [char](40) COLLATE French_CS_AS_KS_WS NULL,
	[RECTRUE1] [char](40) COLLATE French_CS_AS_KS_WS NULL,
	[RECTRUE2] [char](40) COLLATE French_CS_AS_KS_WS NULL,
	[RECTBP] [char](6) COLLATE French_CS_AS_KS_WS NULL,
	[RECTCP] [char](6) COLLATE French_CS_AS_KS_WS NULL,
	[RECTVILLE] [char](40) COLLATE French_CS_AS_KS_WS NULL,
	[RECTPAYS] [char](20) COLLATE French_CS_AS_KS_WS NULL,
	[RECTCPAYS] [char](3) COLLATE French_CS_AS_KS_WS NULL,
	[RECTTELEP1] [char](20) COLLATE French_CS_AS_KS_WS NULL,
	[RECTTELEP2] [char](20) COLLATE French_CS_AS_KS_WS NULL,
	[RECTFAX] [char](20) COLLATE French_CS_AS_KS_WS NULL,
	[RECTCOMMEN] [char](40) COLLATE French_CS_AS_KS_WS NULL,
	[RECTGRPREP] [char](2) COLLATE French_CS_AS_KS_WS NULL,
	[RECTTYPERE] [char](1) COLLATE French_CS_AS_KS_WS NULL,
	[RECTTYPCOM] [char](1) COLLATE French_CS_AS_KS_WS NULL,
	[RECNTXPPAL] [decimal](14, 2) NULL,
	[RECNOBJECT] [decimal](14, 2) NULL,
	[RECJCREATI] [char](8) COLLATE French_CS_AS_KS_WS NULL,
	[RECJVENTES] [char](8) COLLATE French_CS_AS_KS_WS NULL
) ON [PRIMARY]
END
GO
 
SET ANSI_PADDING OFF
GO
 
 
/****** Object:  Index [KREPRESEN]    Script Date: 11/26/2018 12:30:20 ******/
IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[REPRESEN]') AND name = N'KREPRESEN')
CREATE UNIQUE NONCLUSTERED INDEX [KREPRESEN] ON [dbo].[REPRESEN] 
(
	[REKTSOC] ASC,
	[REKTCODE] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Et je souhaite l'indexer, car brut de pomme, la table client contient actuellement 282150 enregistrements ...
Et que peut-être, en indexant la vue, sur certaines colonnes, les temps de réponses pourraient être améliorés?

**SQLpro** · 26/11/2018, 15h05

Envoyé par Christophe Charron

Impossible de créer index sur la vue "ARCE.dbo.V1L_PROSPECTS_01" car celle-ci contient une jointure réflexive sur "ARCE.dbo.REPRESEN".

Cela fait partie des limitations des vues indexées.

A +

**Christophe Charron** · 26/11/2018, 15h40

Merci pour votre réponse.
Il me reste donc deux choix

- sortir les jointures qui risquent d'être réflexives de la vue et en les invoquant lors du select portant sur la vue,
- ne pas indexer la vue

et challenger pour essayer de déterminer ce qui est le plus pertinent en terme de performance.

**aieeeuuuuu** · 26/11/2018, 16h10

de ce que l'on voit, votre table REPRESEN n'a pas de clef primaire, et pas d'index cluster.
il s'agit donc d'une table heap, ce qui peut nuire aux performances. et ça a tendance à s'agraver au fur et à mesure des mises à jour de la table.

est-ce le cas aussi pour les autres tables ? des contraintes d'intégrité référentielles sont-elles déclarée ?

Sinon, à mon avis, c'est par là qu'il faut commencer : déclarer des clefs primaires et étrangères.

**Christophe Charron** · 26/11/2018, 16h15

LE problème, l'éternel problème, c'est que je n'ai pas la main sur le MCD.
Il a été écrit comme cela il y a 22 ans et n'a guère évolué depuis. Comme je faisais partie, à l'époque, de l'équipe de développement et que nous faisions avec ce que nous savions et pouvions, je ne vais pas jeter trop la pierre ...
C'est un petit peu pour pallier au lacunes du MCD, que j'essaie de "bricoler" certaines vues.

Merci à tous pour vos lumières.

**aieeeuuuuu** · 26/11/2018, 16h44

Si cela date d'il y a 22 ans, alors raison de plus !

J’espère qu'il y a au moins des reconstructions régulières de vos tables heap ?!? elles ont la particularité de très mal vieillir !

Concernant les modifications de MCD, je comprends sur le fond, mais c'est je pense la principale source de vos mauvaises performances...

et il y a des choses faisables avec des impacts minimes.
Par exemple, sur la table que vous dont vous avez fourni la structure, on peut voir que l'index KREPRESEN semble être un ersatz de clef primaire. La déclarer en tant que telle n'aurait a priori pas d'impact négatif sur l'existant.
Vous pourriez au moins le passer en index cluster, ce qui serait déjà un bon début à défaut de mieux.

Car je pense qu'en créant des vues indexées à tout va pour pallier les défauts de modélisation des tables sous-jacentes, ça va vite faire effet boule de neige et finir par empirer les choses dans leur ensemble et dégrader notablement les opérations de mise à jour.

**SQLpro** · 28/11/2018, 10h09

Envoyé par Christophe Charron

Merci pour votre réponse.
Il me reste donc deux choix

- sortir les jointures qui risquent d'être réflexives de la vue et en les invoquant lors du select portant sur la vue,
- ne pas indexer la vue

et challenger pour essayer de déterminer ce qui est le plus pertinent en terme de performance.

Optez pour la première solution....

A +

Ecriture jointure réflexive compatible avec la création d'un index sur une vue

Développement SQL Server

Vue hybride

Discussions similaires

Partager

Partager