Problème de résultat avec une vue indexée

[elsuket]

Bonjour,

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

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
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 :

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 :

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

@++

[SQLpro]

AMHA : top 10 !

A +

[zinzineti]

difficile de faire du tunning sans jeu de données

[elsuket]

Ce que je ne comprend pas c'est qu'en exécutant la requête deux fois sur une copie de la base de données où je suis sûr d'être le seul à travailler, j'ai des résultat différents à chaque exécution ...

Je comprend que le TOP soit en cause, mais si le résultat change à chaque exécution, là y'a un problème ...

@++

[elsuket]

Je viens de trouver qu'en empêchant SQL Server d'utiliser la parallélisation à l'aide de l'indicateur de requête OPTION (MAXDOP 1), j'ai des résultats identiques de façon consistante.

Avez-vous rencontré des problèmes similaires ?

@++

[SQLpro]

C'est lié au TOP !!! En fait avec un seul thread possible il prend toujours les données de la même façon. Avec plusieurs thread il te rend les premières lignes d'un des thread...

Le TOP a toujours été inconsistant !!!!! Parce que pas relationnel...

essaye avec RANK

A +

[elsuket]

Le TOP a toujours été inconsistant !!!!! Parce que pas relationnel...

héhé je sais bien, mais il est des cas où malheureusement on en a besoin, surtout quand cela dépend du temps ... pas cool le TOP

Je viens d'essayer avec ceci :

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
37
38
39
40
41
42
43
44
45
46
47
48
49
SELECT	TOP 10 *
FROM	(
		SELECT		DISTINCT 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
				, ROW_NUMBER() OVER(ORDER BY T.ti_priority DESC) AS n
		FROM		dbo.team_task_claim_2 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
						)
					)
		INNER JOIN	dbo.team_task_user_preference AS tup
					ON tup.task_id = T.task_id
		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		r.userid = @user_id
		AND		tup.[user_id] = @user_id
		AND		tup.rec_status = 1
		AND		r.rec_status = 1
		AND		T.ti_person IS NULL
		AND		(
					T.skill_code = r.skill_code
					OR T.skill_code IS NULL
				)
	) AS S
WHERE	n <= 10

En terme de fréquence j'ai des résultats identiques plus fréquemment, mais après certaines exécutions, le problème revient.

En tout cas merci pour la suggestion !

Est-ce à dire que SQL Server collecterait le TOP des "sous-requêtes" pour les assembler ensuite avant de les retourner au client ?

@++

[mikedavem]

Ton problème est intéressant mais tu n'aurais pas un jeu de données pour voir ...

++

[iberserk]

Salut,
Quelle version de SqlServer? quel sp?

As tu essayé avec MAXDOP à 1 ?

[mikedavem]

As tu essayé avec MAXDOP à 1 ?

-->

Je viens de trouver qu'en empêchant SQL Server d'utiliser la parallélisation à l'aide de l'indicateur de requête OPTION (MAXDOP 1), j'ai des résultats identiques de façon consistante.

++

[iberserk]

oups pardon.

[elsuket]

Pas grave

Je suis sous SQL Server 2008 R2.
Je ne peux pas donner de jeu de données sans dévoiler le métier de l'entreprise qui m'emploie ...

Merci à tous pour votre aide

@++

[mikedavem]

Tu abuses .. tu pourrais anonymiser les données quand même ...

[SQLpro]

Le problème c'est qu'il ne sait pas anonymiser le thaï... parce qu'il ne sait pas l'écrire !!!

A +

[Waldar]

Il faudrait rechercher dans de plus vieilles discussions, mais SergeJack obtenait de bonnes performances en passant des TOP via des CROSS APPLY, c'est peut-être applicable ici.

Envoyé par elsuket

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.

Je le vois toujours l'horrible OR, ici, il ne faut pas faire le travail à moitié

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
dbo.team_task_claim AS T
INNER JOIN dbo.auth_user_in_role AS r
  ON r.roleid = T.task_instance_role_Id
			 (
				(
					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