Discussion :

[Papy214]

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 !

[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.

@++

[Papy214]

Merci, bon w-e

[aieeeuuuuu]

Bonjour,

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 :

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

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 :

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

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.

[SQLpro]

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

Code :

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

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 :

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

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

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

Code :

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

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

A +

[aieeeuuuuu]

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

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

Par contre :

Je n'ai pas fait de test,

J'ai pas pu m'empecher...

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 :

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

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

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 :

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) : presque 8 fois plus lent, et 85 fois plus d'io qu'avec ISNULL...

[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