[2005] ROW_NUMBER peu performant

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:

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:

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:

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:

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:

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.