2 pièce(s) jointe(s)
IF EXISTS/IF COUNT beaucoup trop lent
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:
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:
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:
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:
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:
Pièce jointe 187833Pièce jointe 187834
