Impact des fonctions sur le parcours des indexes

En pleine révision de la certification 70-461 Querying Microsoft SQL Server 2012 je me retrouve donc à lire le livre : Querying Microsoft SQL Server 2012: Exam 70-461 Training Kit et oui j’avoue, je ne suis pas fan de la méthode bachotage de dump certexam, tant qu’à utiliser mon temps je préfère ne pas apprendre des QCM par cœur 😉 bref, ceci paie puisque j’apprends ou revois certaines fonctionnalité que je vais partager avec vous.

L’utilisation de fonctions dans les clauses (where, on …) peut entraîner la non utilisation des indexes et ça c’est mal, prenons en exemple une table avec 2 colonnes (id, nom).

Je fais une requête simple pour compter le nombre de noms :

SELECT Nom
,COUNT(id) AS NbNom
FROM dbo.Table1
GROUP BY Nom

Le résultat est le suivant :

Pas d’inquiétude sur l’égale répartition des résultats, j’ai copié les même noms le même nombre de fois c’est tout 🙂

Le résultat s’affiche en 28 secondes (301 989 888 lignes)

Je veux maintenant afficher tous les noms sauf Felicia, ma requête devient donc :

SELECT Nom
,COUNT(id) AS NbNom
FROM dbo.Table1
WHERE Nom <> N'Felicia'
GROUP BY Nom

Le résultat de notre requête est donc :

Généré en 27s c’est trop long, je vais créer l’index suivant :

CREATE NONCLUSTERED INDEX [IX_Nom]
ON [dbo].[Table1] ([Nom])

La requête prend maintenant 9 secondes, c’est beaucoup mieux.

Toutefois, nous avons un problème, la requête précédente est fausse je n’ai pas exclu que Felicia mais aussi NULL car nous avons un champs qui accepte la valeur NULL, il ne s’agit donc plus de logique binaire, mais de logique ternaire avec 3 valeurs possibles (Vrai, Faux et NULL) or le prédicat pouvant être évalué à Vrai ou Faux, le NULL est considéré comme FAUX (car inconnu).

Je vais donc réécrire ma requête de la manière suivante :

SELECT Nom
	,COUNT(id) AS NbNom
FROM dbo.Table1
WHERE ISNULL(Nom,N'') <> N'Felicia'
GROUP BY Nom

Ce qui nous donne :

Damned, le résultat est bon, mais ma requête prend maintenant 16 secondes, ah oui je me souvient, ISNULL n’est pas très performant, il est préférable d’utiliser COALESCE

 SELECT Nom
	,COUNT(id) AS NbNom
FROM dbo.Table1
WHERE COALESCE(Nom,N'') <> N'Felicia'
GROUP BY Nom

Le résultat reste le même et la requête est généré en 18 secondes, sensiblement la même chose, je dois donc ne pas être dans les clous de l’optimisation de COALESCE, mais pourquoi ma requête est elle si longue ?

Voyons le plan d’exécution de :

SELECT Nom
	,COUNT(id) AS NbNom
FROM dbo.Table1
WHERE ISNULL(Nom,N'') <> N'Felicia'
GROUP BY Nom

Ah tien un index Scan, ceci explique le temps anormalement long de la requête, en effet l’utilisation de fonction non sargable dans les clauses pousse à faire un index scan, ou pire ne pas utiliser l’index du tout.

Je vais donc réécrire ma requête pour ne pas utiliser de fonction :

SELECT Nom
	,COUNT(id) AS NbNom
FROM dbo.Table1
WHERE (Nom <> N'Felicia' OR Nom IS NULL)

GROUP BY Nom

Ce qui nous donne :

Un index Scan, pour une raison que je n’explique pas … bref je sèche pour ce coup là si un lecteur éclairé pouvait me dire pourquoi je n’ai pas un seek index sur ce coup là …

Bref on ne se décourage pas, je veux un index Seek, donc je me tourne vers cette requête (pas parfaite) :

SELECT Nom
	,COUNT(id) AS NbNom
FROM dbo.Table1
WHERE (Nom IS NULL)

GROUP BY Nom
UNION 
SELECT Nom
	,COUNT(id) AS NbNom
FROM dbo.Table1
WHERE (Nom <> N'Felicia')
GROUP BY Nom

Le résultat est retourné en 12 secondes, c’est mieux, mais on paie la requête en deux fois …

Conclusion:

Évitons d’utiliser les fonctions dans les clauses, en effet un LEFT(name,3) = ‘Fel’ sera moins performant qu’un name LIKE ‘Fel%’, d’autres exemples sont disponibles ici et dans la majorité des cas, évitez donc les ISNULL(), … même si dans mon exemple cela n’a manifestement pas d’impact :s

Quelques liens pour approfondir:

•  ISNULL VS COLAESCE
• SARGABLE Functions