ISNULL ou COALESCE ?

Version imprimable

23/03/2013, 10h02
Papy214

ISNULL ou COALESCE ?

Bonjour,

Si j'ai tout compris (oui j'ai free), ISNULL et COALESCE sont équivalents, mis à part le nombre de paramètres. Mais est-ce qu'il y a une différence de performance entre les deux quand on peut utiliser l'une ou l'autre ?

Papy !
23/03/2013, 12h27
elsuket

Bonjour Papy214,

Il n'y a aucune différence de performances : l'opérateur qui implémente ces deux fonctions est le même.
En revanche ISNULL() est T-SQL, alors que COALESCE() fait partie du Standard SQL.

@++ ;)
24/03/2013, 10h31
Papy214

Merci, bon w-e

Bonjour,

Citation:

Envoyé par elsuket

l'opérateur qui implémente ces deux fonctions est le même.

Je ne suis pas tout à fait d'accord :
Lors de l'utilisation de paramètre hétérogènes, ISNULL transtype le second paramètre vers le type du premier (ISNULL n'accepte que deux arguments) alors que COALESCE transtype en suivant les règles de précédence.

Je n'ai pas fait de test, mais je pense effectivement que la différence de performance, s'il y en a, doit etre insignifiante.
Par contre la différence de comportement peut avoir des conséquences :
Exemple 1 /
Code:

1 2 3 4 5 6 DECLARE @a VARCHAR(3) SELECT ISNULL(@a, 'abcdef') AS Result_ISNULL, COALESCE(@a, 'abcdef') Result_COALESCE --Result_ISNULL Result_COALESCE --abc abcdef
Ici, ISNULL a transtypé implicitement 'abcdef' vers un VARCHAR(3)

Exemple 2/
Code:

1 2 3 4 5 6 7 DECLARE @a VARCHAR(3) = 'a' DECLARE @b INT SELECT ISNULL(@a, @b) --> renvoit 'a' SELECT COALESCE(@a, @b) --> erreur : Échec de la conversion de la valeur varchar 'a' en type de données int.

D’après mes tests COALESCE est légèrement plus rapide...

Code:

1
2
3
4
5
6
7
8
USE tempdb;
GO
 
CREATE TABLE T_TEST
(FONCTION   VARCHAR(8),
 DEBUT      DATETIME2(7),
 FIN        DATETIME2(7),
 DATA       INT)

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
SET NOCOUNT ON;
GO
 
INSERT INTO T_TEST VALUES ('ISNULL',  SYSDATETIME(), NULL, NULL);
GO
 
DECLARE @I INT;
SET @I = 0
WHILE @I < 100000
BEGIN
   UPDATE T_TEST SET DATA = ISNULL(NULL, 1) WHERE FONCTION = 'ISNULL';
   SET @I += 1;
END
GO
 
UPDATE T_TEST SET FIN = SYSDATETIME() WHERE FONCTION = 'ISNULL';
GO
 
INSERT INTO T_TEST VALUES ('COALESCE',  SYSDATETIME(), NULL, NULL);
GO
 
DECLARE @I INT;
SET @I = 0
WHILE @I < 100000
BEGIN
   UPDATE T_TEST SET DATA = COALESCE(NULL, 1) WHERE FONCTION = 'COALESCE';
   SET @I += 1;
END
GO
 
UPDATE T_TEST SET FIN = SYSDATETIME() WHERE FONCTION = 'COALESCE';
GO

Code:

1
2
SELECT FONCTION, DATEDIFF(ms, DEBUT, FIN) AS DUREE_ms
FROM T_TEST

Code:

1
2
3
4
FONCTION DUREE_ms
-------- -----------
ISNULL   8563
COALESCE 7104

A +

Citation:

Envoyé par SQLpro

D’après mes tests COALESCE est légèrement plus rapide...

Ton test chez moi donne un COALESCE 30% plus lent...

Par contre :

Citation:

Je n'ai pas fait de test,

J'ai pas pu m'empecher...

Citation:

mais je pense effectivement que la différence de performance, s'il y en a, doit être insignifiante.

pas toujours en fait, et je confirme que COALESCE n'utilise pas le même algo que ISNULL :
COALESCE semble remplacer purement et simplement par un CASE WHEN...Ce qui n'est pas le cas de ISNULL : on peut même avoir des plans d’exécution différents :

Jeu de données :
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 SET STATISTICS IO, TIME OFF IF OBJECT_ID('B', 'U') IS NOT NULL DROP TABLE B IF OBJECT_ID('A', 'U') IS NOT NULL DROP TABLE A CREATE TABLE A( id INT NOT NULL PRIMARY KEY IDENTITY , val VARCHAR(50) ) CREATE TABLE B( id INT NOT NULL PRIMARY KEY IDENTITY , id_A INT NOT NULL FOREIGN KEY REFERENCES A(id) , val VARCHAR(50) ) GO SET NOCOUNT ON INSERT INTO A(val) VALUES(newid()) GO 1000 INSERT INTO B(id_A, val) SELECT TOP(10) PERCENT id , NEWID() FROM A ORDER BY NEWID() GO 100000 DELETE FROM B WHERE id_A IN (SELECT TOP(60) PERCENT id FROM A) UPDATE TOP(60) PERCENT B SET val = NULL CREATE UNIQUE INDEX UIX_VAL ON A(val)
requêtes :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 SET STATISTICS IO, TIME ON SELECT ISNULL( (SELECT MAX(val) FROM B WHERE id_A = A.id) , (SELECT MAX(val) FROM B) ) FROM A SELECT COALESCE( (SELECT MAX(val) FROM B WHERE id_A = A.id) , (SELECT MAX(val) FROM B) ) FROM A
Avec ISNULL :

Citation:

Table 'Worktable'. Nombre d'analyses 0, lectures logiques 0, lectures physiques 0,...
Table 'B'. Nombre d'analyses 2, lectures logiques 141372, lectures physiques 0,...
Table 'A'. Nombre d'analyses 1, lectures logiques 9, lectures physiques 0,...

SQL Server \endash Temps d'exécution*:
, Temps UC = 3369*ms, temps écoulé = 3440*ms.

Avec COALESCE :

Citation:

Table 'Worktable'. Nombre d'analyses 400, lectures logiques 12044579, lectures physiques 0, ...
Table 'B'. Nombre d'analyses 3, lectures logiques 212058, lectures physiques 0,...
Table 'A'. Nombre d'analyses 1, lectures logiques 9, lectures physiques 0, ...

SQL Server \endash Temps d'exécution*:
, Temps UC = 24212*ms, temps écoulé = 24384*ms.

COALESCE est très largement contre performant (certes la requête aussi, mais c'est pour l'exemple:aie:) : presque 8 fois plus lent, et 85 fois plus d'io qu'avec ISNULL...

01/04/2013, 09h24
Papy214

Étant encore loin du niveau des spécialistes que vous êtes, je vais préférer COALESCE puisque c'est le standard SQL.

Mais ces tests sont forts intéressants. Merci à vous tous