"IF Exists" plus lent que "SELECT COUNT + IF"?!?
Bonjour,
J'ai un comportement que je ne m explique pas avec un "IF EXISTS": celui-ci prend plusieurs minutes d'exécution... alors que "SELECT @Counter" + "IF @Counter..." ne prend que quelques millisecondes.
PS:Le script "SELECT ..." est exactement le même (copier/coller)
Code "IF EXISTS":
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
IF EXISTS (SELECT NULL
FROM AVGV300 WITH (NOLOCK)
INNER JOIN PRMD000 sk WITH (NOLOCK) ON sk.RECORDID = AVGV300.REF_RECID AND AVGV300.FELDNAME = 'ZeichnungGedruckt'
INNER JOIN CTI_Trig_PRMD0x0 ae WITH (NOLOCK) ON ae.BDE_NR = sk.BDE_NR AND ae.TableName = 'PRMD000'
WHERE ISNULL(AVGV300.PAR_VALUE, '0.0') <> '10.0'
AND sk.Auftrag NOT LIKE '60%'
AND LTRIM(RTRIM(ISNULL(sk.Zeichnung, ''))) <> ''
AND (ISNULL(ae.Zeichnung_N, '') <> ISNULL(ae.Zeichnung_O, '')
OR ISNULL(ae.Zeich_Idx_N, '') <> ISNULL(ae.Zeich_Idx_O, '')
)
AND ISNULL(AVGV300.DAT_AEN, AVGV300.DAT_NEU) IS NOT NULL
AND ae.InsertDate IS NOT NULL
AND ae.InsertDate > ISNULL(AVGV300.DAT_AEN, AVGV300.DAT_NEU)
)
BEGIN
PRINT 'Exists...'
END
ELSE
BEGIN
PRINT 'Not exists...'
END |
(> 9 minutes !)
Code "SELECT @Counter" + "IF @Counter...":
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
|
DECLARE @Counter INTEGER
SELECT @Counter = COUNT(*)
FROM AVGV300 WITH (NOLOCK)
INNER JOIN PRMD000 sk WITH (NOLOCK) ON sk.RECORDID = AVGV300.REF_RECID AND AVGV300.FELDNAME = 'ZeichnungGedruckt'
INNER JOIN CTI_Trig_PRMD0x0 ae WITH (NOLOCK) ON ae.BDE_NR = sk.BDE_NR AND ae.TableName = 'PRMD000'
WHERE ISNULL(AVGV300.PAR_VALUE, '0.0') <> '10.0'
AND sk.Auftrag NOT LIKE '60%'
AND LTRIM(RTRIM(ISNULL(sk.Zeichnung, ''))) <> ''
AND (ISNULL(ae.Zeichnung_N, '') <> ISNULL(ae.Zeichnung_O, '')
OR ISNULL(ae.Zeich_Idx_N, '') <> ISNULL(ae.Zeich_Idx_O, '')
)
AND ISNULL(AVGV300.DAT_AEN, AVGV300.DAT_NEU) IS NOT NULL
AND ae.InsertDate IS NOT NULL
AND ae.InsertDate > ISNULL(AVGV300.DAT_AEN, AVGV300.DAT_NEU)
SELECT @Counter
IF @Counter > 0
BEGIN
PRINT 'Exists...'
END
ELSE
BEGIN
PRINT 'Not exists...'
END |
(< 1 seconde)
Je travaille sur SQL-Server 2008 (Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: ) )
Quelqu'un peut il m'expliquer ?
Merci d'avance
(AVGV300 -> 17780 rows ; CTI_Trig_PRMD0x0 -> 293.788 rows)
