Discussion :

[rad_hass]

Bonjour à tous,

J'ai une requête que je considère comme tout sauf optimale, mais le problème est que je ne sais pas comment l'optimiser. Vous trouverez plus bas le script pour les tables et un jeu de teste.

Donc le contexte est, une gestion d'article pour un vendeur, il y a 3 niveau :
- Un pays crée des articles partagé par tout les vendeurs.
- Un groupe crée des articles partagé par les vendeurs appartenant au groupe et il a la possibilité de modifié les statuts des articles pays.
- Un vendeur peut crée ses propres articles et modifié les statuts des articles groupes et pays.

Si on veut récupérer les articles pour un vendeur on doit récupérer les articles du vendeur, de son groupe et du pays ... Mais il doivent être statut en ligne (donc le statut est celui du vendeur sinon voir groupe sinon voir par défaut).

voici la 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
DECLARE @PK_VENDEUR_ID uniqueidentifier
SET @PK_VENDEUR_ID = '' -- preciser valeur
 
DECLARE @VENDEUR_PREFIXE varchar(50)
SET @VENDEUR_PREFIXE = '' -- preciser valeur
 
SELECT 
		art.OWNER_CATEGORY,
		art.PK_ART_ID					as FK_ART_ID,
		art.ART_URL_COMPLETE,			
		(SELECT TPT_URL FROM TEMPLATE WHERE PK_TPT_ID = art.FK_TPT_ID)		as DESTINATION_URL		
 
FROM	ARTICLE as art
WHERE   
	(
		(OWNER_CATEGORY = 'OWNER_COUNTRY' 
			AND 
			((ART_IS_TO_PUBLISH = 1) AND (NOT EXISTS(SELECT 1 FROM  STATUT_VENDEUR WHERE  FK_VENDEUR_ID = @PK_VENDEUR_ID AND FK_ART_ID = art.PK_ART_ID AND ART_IS_TO_PUBLISH = 0)) 
			AND NOT EXISTS(SELECT 1 FROM  STATUT_GROUPE as sg INNER JOIN VENDEUR as v ON sg.FK_GROUPE_ID = v.FK_GROUPE_ID WHERE  PK_VENDEUR_ID = @PK_VENDEUR_ID AND FK_ART_ID = art.PK_ART_ID AND ART_IS_TO_PUBLISH = 0))
			OR
			(EXISTS(SELECT 1 FROM  STATUT_VENDEUR WHERE FK_VENDEUR_ID = @PK_VENDEUR_ID AND FK_ART_ID = art.PK_ART_ID AND ART_IS_TO_PUBLISH = 1))
		)
		OR 
		((art.OWNER = (SELECT GROUP_PREFIXE FROM GROUPE INNER JOIN VENDEUR ON FK_GROUPE_ID = PK_GROUPE_ID WHERE VENDEUR_PREFIXE = @VENDEUR_PREFIXE) AND OWNER_CATEGORY = 'OWNER_GROUP')
			AND ((ART_IS_TO_PUBLISH = 1) AND (NOT EXISTS(SELECT 1 FROM  STATUT_VENDEUR WHERE  FK_VENDEUR_ID = @PK_VENDEUR_ID AND FK_ART_ID = art.PK_ART_ID AND ART_IS_TO_PUBLISH = 0)) 
			OR
			(EXISTS(SELECT 1 FROM  STATUT_VENDEUR WHERE  FK_VENDEUR_ID = @PK_VENDEUR_ID AND FK_ART_ID = art.PK_ART_ID AND ART_IS_TO_PUBLISH = 1))
			)
		)
	)

Merci d'avance

Voici les tables :

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
USE [TEST]
GO
/****** Object:  Table [dbo].[GROUPE]    Script Date: 06/17/2009 12:29:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[GROUPE](
	[PK_GROUPE_ID] [uniqueidentifier] NOT NULL,
	[GROUP_PREFIXE] [varchar](50) NULL,
 CONSTRAINT [PK_GROUPE] PRIMARY KEY CLUSTERED 
(
	[PK_GROUPE_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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[TEMPLATE]    Script Date: 06/17/2009 12:29:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TEMPLATE](
	[PK_TPT_ID] [uniqueidentifier] NOT NULL,
	[TPT_URL] [varchar](200) NULL,
 CONSTRAINT [PK_TEMPLATE] PRIMARY KEY CLUSTERED 
(
	[PK_TPT_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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[VENDEUR]    Script Date: 06/17/2009 12:29:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[VENDEUR](
	[PK_VENDEUR_ID] [uniqueidentifier] NOT NULL,
	[FK_GROUPE_ID] [uniqueidentifier] NOT NULL,
	[VENDEUR_PREFIXE] [varchar](50) NULL,
 CONSTRAINT [PK_VENDEUR] PRIMARY KEY CLUSTERED 
(
	[PK_VENDEUR_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
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[STATUT_GROUPE]    Script Date: 06/17/2009 12:29:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[STATUT_GROUPE](
	[PK_STATUT_GROUPE_ID] [uniqueidentifier] NOT NULL,
	[FK_GROUPE_ID] [uniqueidentifier] NULL,
	[FK_ART_ID] [uniqueidentifier] NOT NULL,
	[ART_IS_TO_PUBLISH] [bit] NULL,
 CONSTRAINT [PK_STATUT_GROUPE] PRIMARY KEY CLUSTERED 
(
	[PK_STATUT_GROUPE_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
/****** Object:  Table [dbo].[STATUT_VENDEUR]    Script Date: 06/17/2009 12:29:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[STATUT_VENDEUR](
	[PK_STATUT_VENDEUR_ID] [uniqueidentifier] NOT NULL,
	[FK_VENDEUR_ID] [uniqueidentifier] NULL,
	[FK_ART_ID] [uniqueidentifier] NOT NULL,
	[ART_IS_TO_PUBLISH] [bit] NULL,
 CONSTRAINT [PK_STATUT_VENDEUR] PRIMARY KEY CLUSTERED 
(
	[PK_STATUT_VENDEUR_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
/****** Object:  Table [dbo].[ARTICLE]    Script Date: 06/17/2009 12:29:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ARTICLE](
	[PK_ART_ID] [uniqueidentifier] NOT NULL,
	[OWNER_CATEGORY] [varchar](50) NOT NULL,
	[ART_URL_COMPLETE] [varchar](200) NOT NULL,
	[FK_TPT_ID] [uniqueidentifier] NULL,
	[ART_IS_TO_PUBLISH] [bit] NULL,
	[OWNER] [varchar](50) NULL,
 CONSTRAINT [PK_ARTICLE] PRIMARY KEY CLUSTERED 
(
	[PK_ART_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
SET ANSI_PADDING OFF
GO
/****** Object:  ForeignKey [FK_ARTICLE_TEMPLATE]    Script Date: 06/17/2009 12:29:18 ******/
ALTER TABLE [dbo].[ARTICLE]  WITH CHECK ADD  CONSTRAINT [FK_ARTICLE_TEMPLATE] FOREIGN KEY([FK_TPT_ID])
REFERENCES [dbo].[TEMPLATE] ([PK_TPT_ID])
GO
ALTER TABLE [dbo].[ARTICLE] CHECK CONSTRAINT [FK_ARTICLE_TEMPLATE]
GO
/****** Object:  ForeignKey [FK_STATUT_GROUPE_ARTICLE]    Script Date: 06/17/2009 12:29:23 ******/
ALTER TABLE [dbo].[STATUT_GROUPE]  WITH CHECK ADD  CONSTRAINT [FK_STATUT_GROUPE_ARTICLE] FOREIGN KEY([FK_ART_ID])
REFERENCES [dbo].[ARTICLE] ([PK_ART_ID])
GO
ALTER TABLE [dbo].[STATUT_GROUPE] CHECK CONSTRAINT [FK_STATUT_GROUPE_ARTICLE]
GO
/****** Object:  ForeignKey [FK_STATUT_GROUPE_GROUPE]    Script Date: 06/17/2009 12:29:23 ******/
ALTER TABLE [dbo].[STATUT_GROUPE]  WITH CHECK ADD  CONSTRAINT [FK_STATUT_GROUPE_GROUPE] FOREIGN KEY([FK_GROUPE_ID])
REFERENCES [dbo].[GROUPE] ([PK_GROUPE_ID])
GO
ALTER TABLE [dbo].[STATUT_GROUPE] CHECK CONSTRAINT [FK_STATUT_GROUPE_GROUPE]
GO
/****** Object:  ForeignKey [FK_STATUT_VENDEUR_ARTICLE]    Script Date: 06/17/2009 12:29:25 ******/
ALTER TABLE [dbo].[STATUT_VENDEUR]  WITH CHECK ADD  CONSTRAINT [FK_STATUT_VENDEUR_ARTICLE] FOREIGN KEY([FK_ART_ID])
REFERENCES [dbo].[ARTICLE] ([PK_ART_ID])
GO
ALTER TABLE [dbo].[STATUT_VENDEUR] CHECK CONSTRAINT [FK_STATUT_VENDEUR_ARTICLE]
GO
/****** Object:  ForeignKey [FK_STATUT_VENDEUR_VENDEUR]    Script Date: 06/17/2009 12:29:26 ******/
ALTER TABLE [dbo].[STATUT_VENDEUR]  WITH CHECK ADD  CONSTRAINT [FK_STATUT_VENDEUR_VENDEUR] FOREIGN KEY([FK_VENDEUR_ID])
REFERENCES [dbo].[VENDEUR] ([PK_VENDEUR_ID])
GO
ALTER TABLE [dbo].[STATUT_VENDEUR] CHECK CONSTRAINT [FK_STATUT_VENDEUR_VENDEUR]
GO
/****** Object:  ForeignKey [FK_VENDEUR_GROUPE]    Script Date: 06/17/2009 12:29:28 ******/
ALTER TABLE [dbo].[VENDEUR]  WITH CHECK ADD  CONSTRAINT [FK_VENDEUR_GROUPE] FOREIGN KEY([FK_GROUPE_ID])
REFERENCES [dbo].[GROUPE] ([PK_GROUPE_ID])
GO
ALTER TABLE [dbo].[VENDEUR] CHECK CONSTRAINT [FK_VENDEUR_GROUPE]
GO

Jeu de test :

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
INSERT INTO [TEST].[dbo].[ARTICLE] ([PK_ART_ID] ,[OWNER_CATEGORY],[ART_URL_COMPLETE],[FK_TPT_ID],[ART_IS_TO_PUBLISH])
     VALUES (newid(),'OWNER_COUNTRY','/offre-du-moi/luxure','A8A7973C-BF49-4B20-B31E-02C55F580056',1)
 
INSERT INTO [TEST].[dbo].[ARTICLE] ([PK_ART_ID] ,[OWNER_CATEGORY],[ART_URL_COMPLETE],[FK_TPT_ID],[ART_IS_TO_PUBLISH],[OWNER])
     VALUES (newid(),'OWNER_GROUPE','/offre-du-moi/douceur','45FB1E6A-0B06-473E-9F3B-E4837C2AF698',1,'IDF')
 
INSERT INTO [TEST].[dbo].[ARTICLE] ([PK_ART_ID] ,[OWNER_CATEGORY],[ART_URL_COMPLETE],[FK_TPT_ID],[ART_IS_TO_PUBLISH])
     VALUES (newid(),'OWNER_COUNTRY','/offre-du-moi/evasion','45FB1E6A-0B06-473E-9F3B-E4837C2AF698',1)
 
INSERT INTO [TEST].[dbo].[TEMPLATE] ([PK_TPT_ID], [TPT_URL]) VALUES (newid() ,'article/article.aspx')
 
INSERT INTO [TEST].[dbo].[TEMPLATE] ([PK_TPT_ID], [TPT_URL]) VALUES (newid() ,'article/listearticles.aspx')
 
INSERT INTO [TEST].[dbo].[VENDEUR] ([PK_VENDEUR_ID],[FK_GROUPE_ID],[VENDEUR_PREFIXE]) VALUES (newid() ,'B4429345-E93D-4BF5-A2E2-B7152004402E','paris')
 
INSERT INTO [TEST].[dbo].[GROUPE] ([PK_GROUPE_ID],[GROUP_PREFIXE]) VALUES (newid(), 'IDF')
 
INSERT INTO [TEST].[dbo].[STATUT_GROUPE] ([PK_STATUT_GROUPE_ID] ,[FK_GROUPE_ID] ,[FK_ART_ID] ,[ART_IS_TO_PUBLISH]) 
	VALUES (newid(),'B4429345-E93D-4BF5-A2E2-B7152004402E','EC807BBA-BB00-4088-9149-319619CAC775',0)
 
INSERT INTO [TEST].[dbo].[STATUT_VENDEUR] ([PK_STATUT_VENDEUR_ID],[FK_VENDEUR_ID],[FK_ART_ID],[ART_IS_TO_PUBLISH])
     VALUES (newid(),'4AA9EA17-1B90-40D4-8A4C-50486F6927E9','953CBA6D-D630-46EF-A71E-9F7CA254E8FD',1)
 
INSERT INTO [TEST].[dbo].[STATUT_VENDEUR] ([PK_STATUT_VENDEUR_ID],[FK_VENDEUR_ID],[FK_ART_ID],[ART_IS_TO_PUBLISH])
     VALUES (newid(),'8D366962-A322-464A-B849-41D9BCEF5919','EC807BBA-BB00-4088-9149-319619CAC775',1)