IF EXISTS/IF COUNT beaucoup trop lent

**Krison** · 14/09/2015, 15h43

Lorsque j'exécute un Query "SELECT NULL...MyQuery...", le résultat est instantané. (0 seconde!)
Lorsque j'exécute ce même Query dans un IF (EXISTS ou COUNT > 0...), l'exécution prend plusieurs minutes.

Mon Query travaille sur deux tables se trouvant sur deux bases de données différentes (même instance; lecture en "WITH (NOLOCK)")

Je ne comprends pas pourquoi il y a une telle différence... ou plutôt pourquoi il y a une différence d'intérprétation.

Merci d'avance pour votre avis sur la question

Détails SQL:

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
--Link Server : ERP(Auftrag)-PDM(Project)
-- Part 0 - Select...
SELECT 'Part 0 - Start - [' + CONVERT(VARCHAR(12), getdate(), 114) + ']'
SELECT COUNT(*)
	FROM PDB.pdb.masteritems MI WITH (NOLOCK)
		INNER JOIN ERP.dbo.Auftrag au WITH (NOLOCK)
			ON MI.as_mi__project IS NOT NULL
				AND MI.as_mi__org_id = 0
				AND DATEDIFF(year, au.DAT_AEN, getdate()) < 2
				AND LTRIM(RTRIM(substring(MI.as_mi__project,0,CHARINDEX('_',MI.as_mi__project)))) = LTRIM(RTRIM(au.Auftrag))
	WHERE 1=1
	AND au.status_auf <> '10'
	AND MI.as_mi__version NOT IN ('tmp','TMP')
	AND MI.as_mi__status <> 'finished'
	--return 0
SELECT 'Part 0 - Stop  - [' + CONVERT(VARCHAR(12), getdate(), 114) + ']'
GO

=> 220 ms

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
 
-- Part 1 - IF EXISTS...
SELECT 'Part 1 - Start - [' + CONVERT(VARCHAR(12), getdate(), 114) + ']'
--DECLARE @Now DATETIME = getdate()
IF EXISTS(SELECT NULL
						-- TOP 1 MI.as_mi__id
						FROM PDB.pdb.masteritems MI WITH (NOLOCK)
							INNER JOIN ERP.dbo.Auftrag au WITH (NOLOCK)
								ON MI.as_mi__project IS NOT NULL
									AND MI.as_mi__org_id = 0
									AND DATEDIFF(year, au.DAT_AEN, getdate()) < 2
									--AND DATEDIFF(year, au.DAT_AEN, @Now) < 2
									AND LTRIM(RTRIM(substring(MI.as_mi__project,0,CHARINDEX('_',MI.as_mi__project)))) = LTRIM(RTRIM(au.Auftrag))
						WHERE 1=1
							AND au.status_auf <> '10'
							AND MI.as_mi__version NOT IN ('tmp','TMP')
							AND MI.as_mi__status <> 'finished'
					)
BEGIN
	PRINT 'Exists (Part 1 - IF EXISTS)'
END
ELSE
BEGIN
	PRINT 'Not Exists (Part 1 - IF EXISTS)'
END
SELECT 'Part 1 - Stop  - [' + CONVERT(VARCHAR(12), getdate(), 114) + ']'
GO

=> 4minutes 30s!!!

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
 
-- Part 2 - IF COUNT() > 0...
SELECT 'Part 2 - Start - [' + CONVERT(VARCHAR(12), getdate(), 114) + ']'
IF (SELECT COUNT(*)
			FROM PDB.pdb.masteritems MI WITH (NOLOCK)
				INNER JOIN ERP.dbo.Auftrag au WITH (NOLOCK)
					ON MI.as_mi__project IS NOT NULL
						AND MI.as_mi__org_id = 0
						AND DATEDIFF(year, au.DAT_AEN, getdate()) < 2
						AND LTRIM(RTRIM(substring(MI.as_mi__project,0,CHARINDEX('_',MI.as_mi__project)))) = LTRIM(RTRIM(au.Auftrag))
			WHERE 1=1
				AND au.status_auf <> '10'
				AND MI.as_mi__version NOT IN ('tmp','TMP')
				AND MI.as_mi__status <> 'finished'
			) > 0
BEGIN
	PRINT 'Exists (Part 2 - IF Count)'
END
ELSE
BEGIN
	PRINT 'Not Exists (Part 2 - IF Count)'
END
SELECT 'Part 2 - Stop  - [' + CONVERT(VARCHAR(12), getdate(), 114) + ']'
GO

=> 4minutes 36s!!!

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
 
-- Part 3 - @Counter = COUNT()...
SELECT 'Part 3 - Start - [' + CONVERT(VARCHAR(12), getdate(), 114) + ']'
DECLARE @Counter INT
SELECT @Counter = COUNT(*)
			FROM PDB.pdb.masteritems MI WITH (NOLOCK)
				INNER JOIN ERP.dbo.Auftrag au WITH (NOLOCK)
					ON MI.as_mi__project IS NOT NULL
						AND MI.as_mi__org_id = 0
						AND DATEDIFF(year, au.DAT_AEN, getdate()) < 2
						--AND DATEDIFF(year, au.DAT_AEN, @Now) < 2
						AND LTRIM(RTRIM(substring(MI.as_mi__project,0,CHARINDEX('_',MI.as_mi__project)))) = LTRIM(RTRIM(au.Auftrag))
			WHERE 1=1
					--AND MI.as_mi__project IS NOT NULL
					--AND MI.as_mi__org_id = 0
					--AND DATEDIFF(year, au.DAT_AEN, getdate()) < 2
				AND au.status_auf <> '10'
				AND MI.as_mi__version NOT IN ('tmp','TMP')
				AND MI.as_mi__status <> 'finished'
SELECT 'Part 3 - Counter = ' + ISNULL(CONVERT(VARCHAR(10), @Counter), 'NULL') + '...'
 
IF @Counter > 0
BEGIN
	PRINT 'Exists (Part 3 - @Counter)'
END
ELSE
BEGIN
	PRINT 'Not Exists (Part 3 - @Counter)'
END
SELECT 'Part 3 - Stop  - [' + CONVERT(VARCHAR(12), getdate(), 114) + ']'
GO

=>406 ms

Execution plan Part 0 & Part 1:
Nom : 20150914_SQL_SelectNULLPart0.png
Affichages : 188
Taille : 26,5 Ko

Nom : 20150914_SQL_ExistsPart02.png
Affichages : 203
Taille : 29,0 Ko

**elsuket** · 15/09/2015, 04h29

Vous avez visiblement un problème d'estimation de cardinalités. En effet les flèches dans le second plan sont moins épaisses, ce qui laisse à penser que l'optimisation a produit un plan moins efficace pour ces requêtes que pour votre première requête. Ceci n'est pas étonnant au vu de l'expression de votre prédicat de jointure :

Code :

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

AND LTRIM(RTRIM(substring(MI.as_mi__project,0,CHARINDEX('_',MI.as_mi__project)))) = LTRIM(RTRIM(au.Auftrag))

Un tel prédicat n'est pas cherchable, puisque les statistiques sous-jacentes à l'estimation de cardinalité représentent à l'optimiseur la distribution des valeurs dans les colonnes as_mi__project et au.Auftrag, mais pas les valeurs de toutes les fonctions que l'on peut appliquer sur ces mêmes colonnes. Donc le moteur n'a d'autre choix que celui se lire entièrement la table, d'où l'opérateur Clustered Index Scan. Pour comprendre pourquoi le plan produit par la première requête est plus efficace, il faudrait voir comment il a été compilé, ce que l'on trouve facilement avec SQL Sentry Plan Explorer.

Par ailleurs :

le prédicat EXISTS est optimisé pour l'usage de l'étoile, tout comme l'opérateur COUNT().
AND DATEDIFF(year, au.DAT_AEN, getdate()) < 2 n'est pas cherchable non plus; on peut écrire à la place : AND au.DAT_AEN > DATEADD(year, -2, GETDATE())
l'ensemble des prédicats de filtres ci-dessous ne sont pas cherchables non plus, puisque SQL Server sait par les statistiques comment sont distribuées les valeurs qui sont dans la colonne, mais il ne dispose évidemment pas de la même information pour celles qui n'y sont pas :

AND MI.as_mi__project IS NOT NULL -- si cette colonne est une chaîne, on peut écrire MI.as_mi__project > ''
AND au.status_auf <> '10' -- Stocker un entier sous le type chaîne est très moyen ...
AND MI.as_mi__version NOT IN ('tmp','TMP') -- identiquement ici, il aurait fallu utiliser la même casse partout; voyez si vous ne pouvez pas mettre les autrs statuts plutôt; en tout cas ce la prouve une erreur de modélisation
AND MI.as_mi__status <> 'finished' -- même remarque ici

@++

**mikedavem** · 15/09/2015, 06h49

Hello,

Si j'ai bien suivi le post, la différence de cardinalité entre les 2 plans s'explique par le fait que dans le 2ème plan on utilise IF EXISTS, ce qui amène l'optimiseur de requêtes à changer de stratégie. En effet dans ce 2ème cas, l'évaluation peut s'arrêter à la première ligne trouvée. L'optimiseur n'a pas besoin d'aller lire le reste des données des tables sous-jacentes. On voit d'ailleurs l'apparition d'un opérateur Constant Scan dans le 2ème plan d'exécution prévu à cet effet.

En revanche je serai curieux de voir la différence de plan d'exécution entre une requête avec IF EXISTS et IF COUNT() > 0. En principe depuis 2005, l'optimiseur est capable de changer un IF COUNT(*) > 0 vers son équivalent IF EXISTS. Est-ce qu'il serait possible d'avoir ces plans d'exécutions ?

++

**SQLpro** · 15/09/2015, 09h09

Plumiers choses à dire sur votre requête :
1) le NOLOCK est généralement hautement stupide. EN effet il est susceptible de produire des résultats faux et n’accélère pas le traitement de la requête contrairement à une idée largement répandue...
2) le prédicat :

Code :

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

LTRIM(RTRIM(substring(MI.as_mi__project,0,CHARINDEX('_',MI.as_mi__project)))) = LTRIM(RTRIM(au.Auftrag))

montre que votre modèle est foireux car vous violez la première forme normale (information atomique.
En effet si vous aviez atomisé le contenu de la colonne as_mi__project vous ne seriez pas obligé de faire une telle contorsion, qui rend la requête non cherchable (sargable).
Vous avez cependant un moyen de faire en sorte de minimiser l'impact de cette horreur, c'est de créer une colonne calculée persistante indexée.
3) de la même façon il serait intéressant pour des raisons de performances, de rendre aussi cherchable cette expression :

Code :

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

DATEDIFF(year, au.DAT_AEN, getdate()) < 2

en la transformant en :

Code :

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

au.DAT_AEN > DATEADD(year, -2, GETDATE())

De là l'ensemble des problèmes serait résolu et votre requête, avec les bon index serait encore plus performante !!!!

A +

**Krison** · 15/09/2015, 09h30

Envoyé par elsuket

... Ceci n'est pas étonnant au vu de l'expression de votre prédicat de jointure :

Code :

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

AND LTRIM(RTRIM(substring(MI.as_mi__project,0,CHARINDEX('_',MI.as_mi__project)))) = LTRIM(RTRIM(au.Auftrag))

...
AND au.status_auf <> '10' -- Stocker un entier sous le type chaîne est très moyen ...
AND MI.as_mi__version NOT IN ('tmp','TMP') -- identiquement ici, il aurait fallu utiliser la même casse partout; voyez si vous ne pouvez pas mettre les autrs statuts plutôt; en tout cas ce la prouve une erreur de modélisation
AND MI.as_mi__status <> 'finished' -- même remarque ici
...

C'est clair que ce n'est pas optimal. Mais je n'ai malheureusement pas la main sur la modélisation des DBs (deux programmes différents de deux fournisseurs differents (PDM+ERP))
ça ne me plait pas mais je dois faire avec...(il y a pire avec les PK en VARCHAR...)

Pour information, ce script permet de détecter un problème logique lorsqu'un projet est bloqué dans le système ERP mais ne l'est pas dans le système PDM.

Envoyé par elsuket

AND MI.as_mi__project IS NOT NULL -- si cette colonne est une chaîne, on peut écrire MI.as_mi__project > ''

Ha bon ?!?... J'aurai cru que

Code :

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

> ''

rejeterait également les champs vides! Dans mes débuts en SQL (Sybase), j'ai eu la mauvaise expérience de comparer une valeur sans gérer spécialement le cas NULL => comparaison NULL = NULL qui ne donnait pas le résultat espéré. Depuis je gère toujours avec "IS NULL" / "IS NOT NULL" / ISNULL(, xxx)

Envoyé par elsuket

[*]AND DATEDIFF(year, au.DAT_AEN, getdate()) < 2 n'est pas cherchable non plus; on peut écrire à la place : AND au.DAT_AEN > DATEADD(year, -2, GETDATE())

On en apprend tous les jours ;-)

Envoyé par elsuket

SQL Sentry Plan Explorer

Je ne connais pas... je vais me renseigner la dessus...

Execution Plan / IF (SELECT COUNT(*)...) > 0
Nom : 20150914_SQL_CountPart02.png
Affichages : 247
Taille : 32,3 Ko

Merci beaucoup pour le retour!

**Krison** · 15/09/2015, 09h55

Envoyé par SQLpro

...
1) le NOLOCK est généralement hautement stupide. EN effet il est susceptible de produire des résultats faux et n’accélère pas le traitement de la requête contrairement à une idée largement répandue...

Ha bon... plus d'explications s'il vous plait?

Envoyé par SQLpro

2) le prédicat :

Code :

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

LTRIM(RTRIM(substring(MI.as_mi__project,0,CHARINDEX('_',MI.as_mi__project)))) = LTRIM(RTRIM(au.Auftrag))

montre que votre modèle est foireux car vous violez la première forme normale (information atomique.
En effet si vous aviez atomisé le contenu de la colonne as_mi__project vous ne seriez pas obligé de faire une telle contorsion, qui rend la requête non cherchable (sargable).
Vous avez cependant un moyen de faire en sorte de minimiser l'impact de cette horreur, c'est de créer une colonne calculée persistante indexée.

C'est clair! Mais comme je l'ai indiqué plus haut, je n'ai pas la main sur la modélisation des données et je ne peux pas modifier la DB comme je veux. (... crée un index calculé ne devrait pas perturber le programme PDM...)

Envoyé par SQLpro

3) de la même façon il serait intéressant pour des raisons de performances, de rendre aussi cherchable cette expression :

Code :

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

DATEDIFF(year, au.DAT_AEN, getdate()) < 2

en la transformant en :

Code :

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

au.DAT_AEN > DATEADD(year, -2, GETDATE())

En effet (comme indiqué plus haut). Dans ce cas, je comprends bien que je dois simplement changer mon habitude d'écriture.

Je tiens à faire remarquer que l'origine de mon post était de comprendre la différence de performance.
Je sais que l'écriture n'est vraiment pas plaisante mais il faut savoir relativisé:
- Le job ne se lance qu'une fois par semaine
- Le simple "select count" coûte 0.22 s... l'idée était que l'équivalent en IF EXISTS / IF COUNT couterait également 0.22 s.

Encore merci pour le retour

**elsuket** · 15/09/2015, 10h45

Comme le disait Mikedavem, la stratégie de construction d'un plan pour EXISTS est différente, ce qui nous amène donc (potentiellement pour le cas général) à deux estimations de cardinalités différentes.

En ce qui concerne SQL Sentry Plan Explorer, je vous ai mis un lien dans ma première réponse : c'est un billet que j'ai écrit et qui détaille l'usage de cette application, notamment l'anonymisation des plans pour que vous puissiez les partager ici, de façon à ce que l'on puisse comprendre l'origine de votre problème. Cela étant, l'expression de votre prédicat de jointure et de vos filtres ne permet pas de réaliser une estimation de cardinalité précise, d'où la différence d'efficacité entre les plans.

Vous pouvez pour simplifier créer des colonnes calculées suivant ces expressions, et les indexer; mais est-ce bien utile si c'est un lot que l'on exécute une fois par semaine sur une base de données à laquelle on n'a pas les mains libres ?

@++

**SQLpro** · 15/09/2015, 11h00

Envoyé par Krison

Ha bon... plus d'explications s'il vous plait?

C'est clair! Mais comme je l'ai indiqué plus haut, je n'ai pas la main sur la modélisation des données et je ne peux pas modifier la DB comme je veux. (... crée un index calculé ne devrait pas perturber le programme PDM...)

C'est justement le but ! Le fait de rajouter une colonne calculée persistante et de l'indexer n'a pas d'influence sur votre application, mais permet de faire de la dénormalisation intelligente et donc de booster les performance. Le modèle est préservé !

**elsuket** · 15/09/2015, 11h04

En général pour faire modifier un programme par un éditeur, ce n'est pas simple.
Donc là si Krison ne peut pas modifier la base de données, puisque sinon la garantie de l'éditeur ne tiendrait plus, il est pieds et poings liés !
Après on est d'accord, il y a malfaçon.

@++

IF EXISTS/IF COUNT beaucoup trop lent [2008]

Développement SQL Server

Discussions similaires

Partager

Partager