Discussion :

[Sergejack]

Bonjour,

En travaillant sur un query j'ai été surpris de constaté la lenteur d'une approche avec ROW_NUMBER() et j'aimerais comprendre le pourquoi de cette lenteur.

Le but de mon query est de me permettre de retrouver dans une table d'handicaps de golf, l'entrée la plus récente pour chaque membre.

La table :

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
 
CREATE TABLE [dbo].[tHandicaps](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[IdMember] [bigint] NOT NULL,
	[EntryDate] [datetime] NOT NULL,
	[Handicap] [decimal](4, 1) NOT NULL,
	[OriginClubId] [int] NOT NULL,
	[Formula] [nvarchar](50) NOT NULL,
	[Stableford] [tinyint] NULL,
	[Csa] [tinyint] NULL,
	[CsaStableford] [tinyint] NULL,
	[Remark] [nvarchar](200) NOT NULL,
	[IdCompetition] [int] NULL,
	[QualificationStatus] [tinyint] NOT NULL CONSTRAINT [DF_tHandicaps_QualificationStatus]  DEFAULT ((3)),
	[ForeignKey] [bigint] NULL,
	[TypeOfAdjustement] [tinyint] NOT NULL DEFAULT ((1)),
	[EventDate] [datetime] NULL,
	[NbHoles] [tinyint] NULL,
	[OldHcp] [decimal](4, 1) NOT NULL,
	[roundID] [int] NULL,
 CONSTRAINT [PK_tHandicaps] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
 
GO
ALTER TABLE [dbo].[tHandicaps]  WITH CHECK ADD  CONSTRAINT [FK_tHandicaps_tClubs] FOREIGN KEY([OriginClubId])
REFERENCES [dbo].[tClubs] ([Id])
GO
ALTER TABLE [dbo].[tHandicaps] CHECK CONSTRAINT [FK_tHandicaps_tClubs]
GO
ALTER TABLE [dbo].[tHandicaps]  WITH CHECK ADD  CONSTRAINT [FK_tHandicaps_tMembers] FOREIGN KEY([IdMember])
REFERENCES [dbo].[tMembers] ([Id])
GO
ALTER TABLE [dbo].[tHandicaps] CHECK CONSTRAINT [FK_tHandicaps_tMembers]

Pour un membr, l'entrée la plus récente est celle de "ses" entrées ayant la valeur EntryDate la plus élevée (et en cas d'égalité, ayant l'id le plus élevé parmi les ex aequo).

J'ai, en plus de l'indexe clustered de la PK, deux indexes sur cette table dont un particulièrement approprié pour ce type de requête :

Code :

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

1
2
3
4
5
6
7
 
CREATE NONCLUSTERED INDEX [IDX_IdMember_EntryDate] ON [dbo].[tHandicaps] 
(
	[IdMember] ASC,
	[EntryDate] ASC
)
INCLUDE ( [TypeOfAdjustement]) 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]

Et un autre qui, je le pense, n'a aucun impact dans tout ceci :

Code :

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

1
2
3
4
5
6
7
 
CREATE NONCLUSTERED INDEX [IDX_OriginClubId_roundID_IdMember] ON [dbo].[tHandicaps] 
(
	[OriginClubId] ASC,
	[roundID] ASC,
	[IdMember] 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]

Passons aux queries.

Un query rapide < 1s (testé pour obtenir le dernier handicap des membres 2 et 3) :

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
WITH OrderedLocalHCP (
	idMember
	, lochcp
	, hcpdate
)
AS
(
	SELECT 
		IdMember
		, Handicap
		, EntryDate
	FROM dbo.tHandicaps AS HCP
	WHERE NOT EXISTS (
		SELECT TOP(1) 1
		FROM dbo.tHandicaps AS Hcp2
		WHERE Hcp2.IdMember = Hcp.IdMember
		AND	(
			Hcp2.EntryDate > Hcp.EntryDate
			OR (
				Hcp2.EntryDate = Hcp.EntryDate
				AND
				Hcp2.id > Hcp.id
			)
		)
	)
)

SELECT *
FROM OrderedLocalHCP
WHERE idMember in (2, 3)

La version ROW_NUMBER lente > 14s (testé pour obtenir le dernier handicap des membres 2 et 3) :

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
WITH OrderedLocalHCP (
	idMember
	, lochcp
	, hcpdate
)
AS
(
	SELECT 
		IdMember
		, Handicap
		, EntryDate
	FROM (
		SELECT 
			IdMember
			, Handicap
			, EntryDate
			, ROW_NUMBER() OVER (PARTITION BY IdMember ORDER BY EntryDate DESC, id DESC) AS Row
		FROM dbo.tHandicaps AS HCP
	) AS Ext
	WHERE Row = 1
)

SELECT *
FROM OrderedLocalHCP
WHERE idMember in (2, 3)

Voilà.

Je ne comprends pas pourquoi la formule en ROW_NUMBER() ne semble pas tirer le moindre profit des indexes.
Car j'aurais pensé que du fait de la partition (PARTITION BY IdMember), la clause "WHERE idMember in (2, 3)" soit prise en compte au niveau même du ROW_NUMBER().

Merci de partager avec moi vos idées sur le sujet.

[SQLpro]

Pour qu'il soit efficace votre index devrait être (IdMember, EntryDate, id).

A +

[Sergejack]

Non.

Mais, aujourd'hui j'ai eu confirmation de ce que je suspectais (avec confirmation de ma troisième conclusion).

Merci à Adam Haines, et en particulier à son article au titre évocateur What You Will Not Learn In The Class Room.