Bonjour,

Voici donc la requête que je cherche à optimiser :

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
SELECT		DISTINCT TOP 10 TI.ti_id
		, T.task_id
		, T.batch_size
		, TI.ti_rowversion
		, T.task_autorelease
		, T.task_timeout
		, T.task_base_url
		, TM.training_method_icon
		, TR.training_content_url
		, TI.ti_priority
		, 2 AS status_code
FROM		dbo.team_task_instance AS TI
INNER JOIN	dbo.team_task AS T
			ON TI.task_id = T.task_id
			AND T.rec_status = 1
INNER JOIN	dbo.auth_user_in_role AS r
			 ON
			 (
				(
					TI.role_Id IS NOT NULL
					AND r.roleid = TI.role_id
				)
				OR
				(
					TI.role_Id IS NULL
					AND r.roleid = T.role_id
				)
			)
			AND r.userid = @user_id
			AND r.rec_status = 1
INNER JOIN	dbo.team_task_user_preference AS tup
			ON tup.task_id = T.task_id
			AND tup.[user_id] = @user_id
			AND tup.rec_status = 1
LEFT JOIN	dbo.team_training_resource AS TR
			ON  T.task_id = TR.task_id
			AND TR.language_id = @language_id
			AND TR.rec_status = 1
LEFT JOIN	dbo.team_training_method AS TM
			ON TR.training_method_id = TM.training_method_id
			AND TM.rec_status = 1
WHERE		TI.rec_status = 1
AND		TI.completed_when IS NULL
AND		TI.ti_locked_by IS NULL
AND		TI.ti_person IS NULL
AND		(
			TI.skill_code = r.skill_code
			OR TI.skill_code IS NULL
		)
ORDER BY	TI.ti_priority DESC
J'ai donc créé une vue indexée (le code dans quelques lignes), et j'ai changé l'horrible OR de la jointure en UNION.
Tout se passait bien, jusqu'à que je teste avec différentes valeurs de @user_id.
Si j'exécute la requête ci-dessus, j'ai un résultat, et si j'exécute la requête en référençant la vue indexée, j'ai très exactement le même jeu de données, sauf que la colonne ti_id de mon résultat contient des valeurs tout à fait différentes.

Au départ j'ai cru que j'avais fait une erreur dans l'UNION, et c'est pour cela que je suis revenu à la requête avec OR, pour être sûr d'avoir le résultat attendu.

Donc voici la vue indexée :

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
CREATE VIEW dbo.team_task_claim
	WITH SCHEMABINDING
AS
	SELECT		TI.ti_id
			, T.task_id
			, T.batch_size
			, TI.ti_rowversion
			, T.task_autorelease
			, T.task_timeout
			, T.task_base_url
			, TI.ti_priority
			, TI.ti_locked_by
			, TI.ti_person
			, COALESCE(TI.role_id, T.role_id) AS role_id
			, TI.skill_code
	FROM		dbo.team_task_instance AS TI
	INNER JOIN	dbo.team_task AS T
				ON TI.task_id = T.task_id
	WHERE		T.rec_status = 1
	AND		TI.rec_status = 1
	AND		TI.completed_when IS NULL
GO
 
CREATE UNIQUE CLUSTERED INDEX IXUQ_team_task_claim__ti_id__task_id
ON dbo.team_task_claim (ti_id, task_id)
GO
Et voici la requête qui utilise cette vue :

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
SELECT		DISTINCT TOP 10 T.ti_id
		, T.task_id
		, T.batch_size
		, T.ti_rowversion
		, T.task_autorelease
		, T.task_timeout
		, T.task_base_url
		, TM.training_method_icon
		, TR.training_content_url
		, T.ti_priority
		, 2 AS status_code
FROM		dbo.team_task_claim AS T WITH (NOEXPAND)
INNER JOIN	dbo.auth_user_in_role AS r
			 ON
			 (
				(
					T.task_instance_role_Id IS NOT NULL
					AND r.roleid = T.task_instance_role_Id
				)
				OR
				(
					T.task_instance_role_Id IS NULL
					AND r.roleid = T.task_role_id
				)
			)
			AND r.userid = @user_id
			AND r.rec_status = 1
INNER JOIN	dbo.team_task_user_preference AS tup
			ON tup.task_id = T.task_id
			AND tup.[user_id] = @user_id
			AND tup.rec_status = 1
LEFT JOIN	dbo.team_training_resource AS TR
			ON  T.task_id = TR.task_id
			AND TR.language_id = @language_id
			AND TR.rec_status = 1
LEFT JOIN	dbo.team_training_method AS TM
			ON TR.training_method_id = TM.training_method_id
			AND TM.rec_status = 1
WHERE		T.ti_locked_by IS NULL
AND		T.ti_person IS NULL
AND		(
			T.skill_code = r.skill_code
			OR T.skill_code IS NULL
		)
ORDER BY T.ti_priority DESC
Le pire, c'est que quand j'enlève l'indicateur NOEXPAND, j'ai un résultat identique à la requête originale, mais sans les performances

Je précise que le but premier de cette discussion n'est pas l'optimisation, mais seulement de comprendre pourquoi je n'obtiens pas strictement le même résultat.
J'ai du faire une boulette mais je ne la vois pas

Pour vous donner une idée, la taille de la table dbo.team_task est de 17 lignes, et sa copine dbo.team_task_instance est de quelques dizaines de millions.

J'ai essayé de tuner la requête avec des index sur les tables, mais je n'obtiens pas le gain de performance phénoménal et nécessaire au business : la procédure stockée doit s'exécuter en au plus 15ms, et la vue indexée est utilisée par 4 autres requêtes dans cette même procédure stockée.
Cela fonctionne très bien avec la vue indexée (je suis passé sous les 10ms), mais je n'ai pas le bon résultat

Je suis ouvert à toute proposition, y compris le remplacement de la vue indexée.

Merci de votre aide

@++