Bonjour,

je rencontre un gros soucis de temps de réponses à l'excécution de certaines requêtes.
Ce ne sont poutant pas des requêtes excessivement complexe et j'ai la confiance de dire que le query est bien pensé.

Le problème se situe dans une view qui a un moment donné doit ne me renvoyer que x premiers éléments suite à un tri.
Pour faire ce tri qui me permet d'éliminer (ou conserver selon le point de vue) des éléments j'ai utilisé plusieurs approches.

1 - Utilisation de ROW_NUMBER, facile et généralement rapide.
Le résultat fut catastrophique*

2 - Utilisation de rejet des lignes pour lesquels il existerait une ligne mieux placée dans le tri avec la clause EXISTS.
Le résultat fut très mauvais*

3 - Utilisation de rejet des lignes pour lesquels il existerait une ligne mieux placée dans le tri avec l'utilisation d'une jointure externe et verification IS NULL.
Le résultat fut un plus plus mauvais que celui de 2*

* en terme de performance

J'en suis revennu à mon premier amour, ROW_NUMBER même s'il m'offre le pire résultat.

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
 
ALTER VIEW [dbo].[vMembersCompCategory]
AS
WITH AllCategories (
	catID
	, compID
	, memberID
	, withFee
	, [name]
	, initials
	, forTee
	, gameType
	, AgeDiff
	, HcpDiff	
	, IsExactMatch
)
AS 
(
	SELECT
		CC.catID
		, CC.compID
		, M.Id AS memberID
 
		, FEE.withFee
 
		, NAME.name
		, NAME.initials
 
		, TEE.forTee
		, TEE.gameType
 
		, AGE.age - C.ageMin AS AgeDiff
		, HCP.hcp - C.hcpMin AS HcpDiff
 
		, CAST(CASE 
			WHEN AGE.age < C.ageMax AND HCP.hcp < C.hcpMax THEN 1
			ELSE 0
			END
		AS BIT) AS IsExactMatch
 
	-- Category's main elements
	FROM dbo.tCompCategory AS CC
	INNER JOIN dbo.tCategory AS C ON (
		C.id = CC.catID
	)
	-- (Subscribed?) Member's elements
	CROSS JOIN MyGolf.dbo.tMembers AS M
	INNER JOIN dbo.vGolfAge AS AGE ON (
		AGE.id = M.id
	)
	INNER JOIN dbo.vHcpCompetition AS HCP ON (
		HCP.memberID = M.id
		AND
		HCP.compID = CC.compID
	)
	-- Category's sub-elements
	LEFT JOIN dbo.tCategoryFee AS FEE ON (
		FEE.catID = CC.catID
	)
	LEFT JOIN dbo.tCategoryName AS [NAME] ON (
		NAME.catID = CC.catID
	)
	LEFT JOIN dbo.tCategoryTee AS TEE ON (
		TEE.catID = CC.catID
		AND
		TEE.gender = M.Sex
	)
	-- Filter
	WHERE AGE.age >= C.ageMin
	AND  HCP.hcp >= C.hcpMin
	AND (C.isOnlyForGender IS NULL OR C.isOnlyForGender = M.Sex)
 
)
, TypedCategories (
	catID
	, compID
	, memberID
	, withFee
	, [name]
	, initials
	, forTee
	, gameType
	, AgeDiff
	, HcpDiff	
	, IsExactMatch
 
	, [Type]
)
AS
(
	SELECT
		*
		, 'Tee' AS [Type]
	FROM AllCategories
	WHERE forTee IS NOT NULL
	UNION
	SELECT
		*
		, 'Result' AS [Type]
	FROM AllCategories
	WHERE [name] IS NOT NULL
	UNION
	SELECT
		*
		, 'Fee' AS [Type]
	FROM AllCategories
	WHERE withFee IS NOT NULL
)
 
, OrderedTypedCategories (
	catID
	, compID
	, memberID
	, withFee
	, [name]
	, initials
	, forTee
	, gameType
	, AgeDiff
	, HcpDiff	
	, IsExactMatch
 
	, [Type]
	, Row
)
AS
(
	SELECT
		AC.*
		, ROW_NUMBER() OVER (
			PARTITION BY
				compID
				, memberID
 
			ORDER BY
				AgeDiff ASC
				, HcpDiff ASC
		) AS Row		
	FROM TypedCategories AS AC
)
 
SELECT
	*
FROM OrderedTypedCategories
WHERE Row = 1

Voici deux query qui mettent tout deux plus de 20 secondes à s'excécuter (il faut savoir que sans aucun mécanisme de tri élimination/conserveation ces requêtes mettent moins d'une seconde à s'excécuter).

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
 
select *
from vMembersCompCategory
where compid = 464
28 secondes : +/- 170.000 lignes de résultat

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
 
select *
from vMembersCompCategory
where compid = 464
and memberid = 1
22 secondes : 1 seule lignes de résultat

Manifestement, dans ma view le filtre sur memberID n'a pas d'impact en amont mais seulement en aval de l'exécution plan.
Et pourtant je partition bien sur memberID dans l'utilisation de ROW_NUMBER !

Je ne comprends pas pourquoi et j'aimerais savoir comment faire.

Merci pour votre aide.


EDIT :

ci-joints 2 estimated execution plan.
bad_plan est le plan que j'obtiens
good_plan est le plan que j'aimerais avoir (je l'obtiens en placant dans la view, le filtre directement où je désire qu'il s'opère).
Ces deux plans amènent au même résultat, le mauvais en 22 secondes le bon en moins d'une seconde.