[SQL Server 2005] Optimisation de requête, votre avis !

**rad_hass** · 17/06/2009, 12h33

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)

**SQLpro** · 17/06/2009, 12h41

Ne cherchez pas....
Vous utilisez massivement le type uniqueidentifier comme clef, ce qui est la cause des performances lamentables de votre base.
70% des problèmes de performances viennent des erreurs de conceptions de la base et des mauvais choix de types.

Lisez l'article que j'ai écrit à ce sujet :
http://blog.developpez.com/sqlpro/p7...nt-le-verdict/

A +

**rad_hass** · 17/06/2009, 14h14

Envoyé par SQLpro

Ne cherchez pas....
Vous utilisez massivement le type uniqueidentifier comme clef, ce qui est la cause des performances lamentables de votre base.
70% des problèmes de performances viennent des erreurs de conceptions de la base et des mauvais choix de types.

Lisez l'article que j'ai écrit à ce sujet :
http://blog.developpez.com/sqlpro/p7...nt-le-verdict/

A +

Bonjour et merci pour votre réponse.
J'ai déjà lu cet article (et les autres aussi

), je sais que c'est problématique l'uniqueidentifier, mais le changer une fois que la base est en prod est ce raisonnable ? Je pense pas que ça soit simple. Perso j'aurais choisi un entier auto-incrémenté menfin ...

Néanmoins ma question n'étais pas sur ce point là, mais plutôt sur l'utilisation des not exists avec les select, était ce la meilleur façon d'exprimer la requête ?

**SQLpro** · 17/06/2009, 14h56

A priori oui.

En revanche les OR c'est pas bon....

Et sur les clefs : vous avez au moins 10 jointures, soit au minimum 40 fois plus mauvais qu'avec un INT (je dirais plutôt 100 fois plus mauvais en pratique).

A +

**rad_hass** · 17/06/2009, 15h17

Envoyé par SQLpro

A priori oui.

En revanche les OR c'est pas bon....

Ahhh, vous pensez à une factorisation ? Ou autre chose ?

Envoyé par SQLpro

Et sur les clefs : vous avez au moins 10 jointures, soit au minimum 40 fois plus mauvais qu'avec un INT (je dirais plutôt 100 fois plus mauvais en pratique).

Effectivement ça fait peur, je vais proposer la modification et étudier les impacts

En tout cas encore une fois, merci beaucoup pour votre aide.

**SQLpro** · 17/06/2009, 15h26

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

Sémantiquement je ferais une union ALL avec ajout d'un tag de position.

Code :

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

1
2
3
4
5
6
7
8
SELECT *, 1 AS TAG
FROM le statut est celui du vendeur 
UNION ALL
SELECT *, 2
FROM le statut est celui du groupe 
UNION ALL
SELECT *, 3
FROM le statut est celui par défaut

Puis retrouver les lignes par MIN(TAG)

Cela permet d'éviter les OR !

A +

[SQL Server 2005] Optimisation de requête, votre avis !

MS SQL Server

Discussions similaires

Partager

Partager