SQL Server vu par Christian Robert

SQL Server : La vérité sur le LIKE

Le LIKE est un opérateur qui est malheureusement mal connu et du coup mal utilisé. Cela est dommage car exploité correctement il est capable du meilleur. Tout ce qui suit s'applique à SQL Server 2000, 2005 & 2008.

A quoi sert-il ?

Il fournit une comparaison avec un masque de valeur. Ce masque n'est pas à proprement parler, une expression régulière. Comparé aux expressions régulières de .Net et d'Unix, le LIKE n'a pas le même potentiel.

On peut recherche une chaîne (équivalent au égal)

select *
from dbo.Contact
where lastname LIKE 'Adams'

Une chaîne avec un caractère manquant (le « _ » correspondant au caractère « joker » pour un caractère seul)

select *
from dbo.Contact
where lastname LIKE 'Ad_ms'

Une chaîne avec un morceau manquant ou rien de manquant (le « % » correspondant au caractère « joker » pour un morceau de chaîne ou rien, la chaîne 'As' est une réponse possible, tout comme 'Adams')

select *
from dbo.Contact
where lastname LIKE 'A%s'

Un groupe de lettre manquant ou à trouver (on recherche une chaîne commençant par A, suivant d'une des lettres c, d ou e, puis d'une lettre entre a et z, puis 0 ou n caractères quelconques)

select *
from dbo.Contact
where lastname LIKE 'A[cdef][a-z]%'

Dernier cas, on peut exclure un ou plusieurs caractères (on exclue b et c grâce au symbole « ^ »)

select *
from dbo.Contact
where lastname LIKE 'A[^bc]%'

Il prend en option une clause ESCAPE permettant de préfixer les caractères génériques permettant de réaliser des recherches sur ces derniers.

select *
from dbo.Contact
where lastname LIKE 'A\_%' ESCAPE '\'

Ici le caractère d'échappement est le "\" dès lors il signifie que le _ qui le suit n'est pas un caractère générique.

Les préjugés sur le LIKE

• Le LIKE n'est pas sensible aux majuscules et minuscules
  • C'est faux, il est comme le = sensible au classement (COLLATE) du champ
• Le LIKE n'est pas sensible aux accents
  • C'est faux, pour la même raison que ci-dessus
• Le LIKE n'utilise pas d'index si des caractères génériques sont dans la chaîne de recherche
  • Partiellement vrai, je détaille cela plus loin
• Le LIKE est plus lent que le = pour une recherche de chaîne simple
  • En fait non, s'il n'y a aucuns caractères génériques, il est considéré come un =, donc identique en performance

Le LIKE et les INDEX

C'est un opérateur et non une fonction, il est donc géré à 100% par le moteur. Cependant il faut savoir que le moteur de base de données n'apprécie pas les recherches trop « floues ». En effet il dispose de statistiques (échantillons de données) pour déterminer si une valeur est présente ou non, en quelle quantité. Cela fonctionne bien pour connaitre la probabilité de présence en nombre d'une valeur. Par contre pour un masque de chaîne c'est plus complexe.

Dans les exemples qui suivent je travaille sur une base de données exemple appelée AdventureWorks2000 (http://blogs.codes-sources.com/christian/archive/2007/11/27/sql-server-o-trouver-les-bases-de-donn-es-exemple-pubs-northwind-adventureworks.aspx), j'ajoute un index non clustered sur le champ LastName de la table Contact.

Premier cas

Recherche par le début de la chaîne, avec faible nombre d'enregistrements.

select *
from dbo.Contact
where lastname LIKE 'Ad%'

5 enregistrements renvoyés
SQL Server 2000 à 12 pages lues
SQL Server 2005 à 12 pages lues
è Le plan est une recherche d'index dans les 2 cas

Ici l'index joue son rôle, la recherche est interprétée comme une requête de plage de valeur entre 'Ad' et 'AE'. Remplacez d'ailleurs le LIKE par un couple >= et < et le résultat est le même !

Deuxième cas

Un peu plus générique que le premier.

select *
from dbo.Contact
where lastname LIKE 'A%'

46 enregistrements renvoyés
SQL Server 2000 à 105 pages lues
SQL Server 2005 à 20 pages lues
è Le plan est une recherche d'index dans les 1^er cas et un index scan (équivalent à un table scan ici) dans le second

SQL Server 2005 constate qu'il y a trop de valeurs à renvoyer pour que l'utilisation de l'index soit intéressante. SQL Server 2000 quant à lui se laisse piégé par l'opérateur et continue à rechercher via l'index, résultat il lit plus de données qu'il n'y en a dans la table. Pourquoi ? Nous verrons cela plus loin.

Troisième cas

Recherche d'une partie d'une chaîne

select *
from dbo.Contact
where lastname LIKE '%dam%'

4 enregistrements renvoyés
SQL Server 2000 à 19 pages lues
SQL Server 2005 à 13 pages lues
è Le plan est une recherche d'index dans les 2^ème cas et un index scan (équivalent à un table scan ici) dans le 1^er.

A nouveau SQL Server 2005 a opté pour un plan plus efficace que SQL Server 2000. Il a utilisé l'index alors que l'on ne recherche pas le début de la chaîne.

Quatrième cas

Autre recherche de partie de chaîne

select *
from dbo.Contact
where lastname LIKE '%bu%'

14 enregistrements renvoyés
SQL Server 2000 à 19 pages lues
SQL Server 2005 à 33 pages lues
è Le plan est une recherche d'index dans les 2^ème cas et un index scan (équivalent à un table scan ici) dans le 1^er.

Ici c'est SQL Server 2005 qui a opté pour le mauvais choix et qui va lire plus que nécessaire des données.

Pourquoi ?

Eh bien la réponse se trouve dans le fonctionnement des index pour les cas 1 et 2 sous SQL Server 2000. Les index sont efficaces pour la recherche par le début d'une chaîne de caractères. SQL Server 2000 adopte une attitude simple : On n'utilise l'index que quand le début de la chaîne est fourni ('Ad%' et non pas '%da%', ni '%ams'). Ce n'est pas le cas de SQL Server 2005 qui va utiliser l'index y compris pour le milieu ou la fin de la chaîne, ce qui est une avancée significative.

Pourquoi les 2 moteurs se trompent ils dans certains cas et pourquoi SQL Server 2005 se trompe « moins » que SQL Server 2000 ? La réponse est dans le plan d'exécution estimé de la requête. Dans notre dernier cas SQL Server 2005 estime le nombre d'enregistrements à renvoyer égal à 1,68644, il est donc logique pour lui d'utiliser l'index, malheureusement il y en 14, ce qui rend la recherche peut efficace. Fort heureusement ces erreurs sont peu fréquentes.

Une dernière chose qui aide SQL Server 2005 : Les statistiques (échantillons de données) inclus plus d'informations sur les champs de type caractères. Moralité SQL Server 2005 est plus à même d'estimer le nombre d'enregistrements à renvoyer et d'utiliser des index sur ces champs y compris pour rechercher une fin de chaîne ou un morceau de cette dernière. Un élément pour s'en convaincre si vous attacher la base de données AdventureWorks 2000 sans mettre à jour les statistiques (sp_updatestats) les résultats estimés sont quasi-aussi faux que dans 2000 !

Le LIKE a effectivement quelques petits défauts à connaître. SQL Server 2000 n'utilise les index avec le LIKE uniquement sur le début des chaînes de caractères, et peut se tromper dans ses estimations ! SQL Server 2005 quant à lui est bien plus efficace quand à l'utilisation de cet opérateur sur des champs indexés.

A utiliser sans modération ou presque… Il y a des approches plus intelligentes que le LIKE particulièrement pour les champs volumineux (FullText par exemple) ! Et les recherches trop génériques ruineront les performances de votre serveur (Forcez par exemple la saisie de 2 à 3 caractères minimum dans une recherche à base de %xxx%).

Bonne optimisation…