Ne cassez pas vos index — SARGabilité et anti-patterns dans les clauses WHERE

Le code qui fait mal

Regardez cette requête :

SELECT Libelle
FROM dbo.Produit
WHERE ( COALESCE( Est_archive, '' ) = '' )
  AND ( COALESCE( Est_visible, '' ) = '' );

Je tombe régulièrement sur ce genre de code en audit. Ça peut être généré par un ORM ou écrit à la main. Le résultat est une requête qui ne peut pas utiliser les index. COALESCE emballe les colonnes du WHERE dans une fonction, et SQL Server n’a plus d’autre choix que de parcourir l’intégralité de la table pour évaluer chaque ligne.

C’est ce qu’on appelle un problème de SARGabilité.

Qu’est-ce que la SARGabilité ?

SARGable vient de Search ARGument-able. Un prédicat SARGable est un prédicat que l’optimiseur de requêtes peut résoudre en utilisant un index.

L’analogie la plus simple : chercher un mot dans un dictionnaire.

• Si vous cherchez le mot « index », vous ouvrez le dictionnaire à la lettre I et vous y allez directement. C’est un Index Seek.
• Si vous cherchez tous les mots qui contiennent « dex » quelque part, vous devez parcourir toutes les pages du dictionnaire. C’est un Index Scan (ou un Table Scan), Le parcours complet de toutes les valeurs stockées, Pour toutes les tester jusqu’à la dernière.

Quand vous appliquez une fonction sur une colonne dans le WHERE, vous transformez votre recherche par la première lettre en recherche dans tout le dictionnaire.

flowchart TD
    A[Requête avec WHERE] --> B{Prédicat SARGable ?}
    B -->|Oui| C[Index Seek]
    B -->|Non| D[Index Scan]
    C --> E["Lecture ciblée : quelques pages"]
    D --> F["Lecture complète : toutes les pages de l'index"]
    E --> G[Performance optimale]
    F --> H[Performance dégradée]

Sur une table de quelques milliers de lignes, la différence peut sembler faible. En production, la requête sera de plus en plus lente au fur et à mesure que le volume de table augmentera.

Les fonctions qui cassent vos index

Voici les anti-patterns les plus fréquents, avec leur réécriture SARGable :

Les cas classiques et comment les corriger

Un des cas les plus classiques d’utilisation de fonctions dans la clause WHERE, c’est l’utilisation des fonctions, TRIM, LTRIM ou RTRIM, ou LOWER ou UPPER, pour effectuer des recherches avec des chaînes qui sont trimées, ou faire des recherches sans tenir compte de la casse.

Le TRIM dans la clause WHERE est souvent inutile :

• le RTRIM ne sert à rien, car SQL Server ignore les espaces de fin dans les comparaisons de chaînes (sinon la comparaison sur des types de données CHAR serait un cauchemar).
• le LTRIM est lui aussi souvent inutile : si vous avez des espaces de début dans vos données, c’est que les données ne sont pas propres à l’insertion. La bonne solution est de nettoyer les données à l’insertion, pas à la lecture.

Les fonction UPPER et LOWER sont en général utilisées à mauvais escient. Ce sont des habitudes héritées de motreurs sensibles à la casse par défaut, comme Oracle ou Postgresql. En SQL Server, la plupart des instances sont installées avec une collation par défaut insensible à la casse (CI), donc il n’est pas nécessaire d’appliquer UPPER ou LOWER pour faire des comparaisons insensibles à la casse.

Exemple du COALESCE

La fonction COALESCE empêche le seek, presque « plus » que les autres fonctions (ça ne veut rien dire, je sais).

COALESCE est en interne traduit par SQL Server en expression CASE WHEN. Quand vous écrivez :

WHERE COALESCE([Est_archive], 0) = 0

SQL Server voit en réalité :

WHERE CASE WHEN [Est_archive] IS NOT NULL THEN [Est_archive] ELSE 0 END = 0

L’optimiseur ne peut pas « défaire » cette expression pour en extraire un prédicat simple sur la colonne. Il doit évaluer le CASE WHEN pour chaque ligne de la table avant de pouvoir filtrer. C’est la définition même d’un Index Scan.

Conversions implicites

Un autre cas fréquent de perte de SARGabilité : les conversions implicites. Quand le type de données du paramètre ne correspond pas au type de la colonne, SQL Server convertit automatiquement un des côtés de la comparaison.

Le problème : SQL Server convertit toujours le côté de moindre précédence dans la hiérarchie des types. Si c’est la colonne qui est convertie, l’index est perdu.

J’ai abordé cet aspect dans cet article sur les conversions implicites.

Quand on ne peut pas modifier le code

C’est le cas classique chez les éditeurs de logiciel : vous n’avez pas accès au code source, et l’éditeur ne corrigera pas le problème dans un futur proche. Il reste deux solutions côté base de données.

1. Colonnes calculées + index

Vous pouvez créer une colonne calculée qui reproduit exactement l’expression utilisée dans le WHERE, puis indexer cette colonne. Théoriquement, SQL Server fait automatiquement le lien entre la fonction dans la requête et la colonne calculée.

Par exemple :

-- Ajout des colonnes calculées
ALTER TABLE dbo.Produit
ADD Est_archive_safe AS ISNULL(Est_archive, 0);

-- Création de l'index sur les colonnes calculées
CREATE INDEX IX_Produit_Computed
ON dbo.Produit (Est_archive_safe)
INCLUDE (Libelle);
GO

Mais, cela pose quelques problèmes pratiques :

1. la correspondance entre la fonction dans le WHERE et la colonne calculée doit être exacte. ISNULL(Col, 0) ne correspondra pas à COALESCE(Col, 0) — ce sont des fonctions différentes pour l’optimiseur. LTRIM(RTRIM(Col)) ne correspondra pas à RTRIM(LTRIM(Col)) ni à TRIM(Col).
2. Les options SET de la connexion QUOTED_IDENTIFIER et ANSI_NULLS doivent être à ON pour que la correspondance fonctionne.
3. La correspondance est fragile et incertaines. Pour plus d’informations, voyez cet article de blog de Paul White : Properly Persisted Computed Columns.

2. Contraintes et modélisation

S’il s’agit d’un test de NULL, vous pouvez rendre les colonnes NOT NULL, et éventuellement ajouter une contrainte DEFAULT. Si Est_archive ne peut pas être NULL, l’optimiseur élimine le ISNULL().

-- Nettoyer les NULL existants
UPDATE dbo.Marque SET Est_archive = 0 WHERE Est_archive IS NULL;

-- Ajouter les contraintes
ALTER TABLE dbo.Marque
ALTER COLUMN Est_archive bit NOT NULL;

ALTER TABLE dbo.Marque
ADD CONSTRAINT DF_Marque_Est_archive DEFAULT (0) FOR Est_archive;
GO

SELECT TRIM(Libelle) AS Libelle
FROM dbo.Produit
WHERE ISNULL(Est_archive, 0) = 0
ORDER BY Libelle;
GO

Le plan d’exécution de cette requête est désormais un Index Seek, alors qu’avant c’était un Index Scan.

Voici un script pour identifier les colonnes NULLable qui ne contiennent en réalité aucun NULL — candidates idéales pour un passage en NOT NULL :

-- Trouver les colonnes NULLable qui ne contiennent jamais de NULL
DECLARE @sql nvarchar(max) = N'';

SELECT @sql = @sql +
    'SELECT ''' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '.' +
    QUOTENAME(c.name) + ''' AS colonne, ' +
    'COUNT(*) AS nb_lignes, ' +
    'SUM(CASE WHEN ' + QUOTENAME(c.name) + ' IS NULL THEN 1 ELSE 0 END) AS nb_null ' +
    'FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) +
    ' UNION ALL '
FROM sys.columns AS c
JOIN sys.tables AS t ON c.object_id = t.object_id
JOIN sys.schemas AS s ON t.schema_id = s.schema_id
WHERE c.is_nullable = 1
  AND t.type = 'U'
ORDER BY s.name, t.name, c.name;

-- Retirer le dernier UNION ALL
SET @sql = LEFT(@sql, LEN(@sql) - 10);

-- Filtrer pour ne garder que les colonnes sans NULL
SET @sql = N'SELECT * FROM (' + @sql + N') AS x WHERE nb_null = 0 ORDER BY colonne;';

EXEC sp_executesql @sql;

Stratégies d’optimisation — Résumé

Voici une checklist pour le développeur T-SQL :

1. Jamais de fonction sur une colonne dans le WHERE — déplacer la logique sur le paramètre ou la valeur.
2. Vérifier les types de données — les paramètres et les colonnes doivent correspondre pour éviter les conversions implicites.
3. Modéliser avec des NOT NULL et des DEFAULT quand c’est possible — c’est la solution la plus propre.
4. Nettoyer les données à l’insertion, pas à la lecture — si vous avez besoin de TRIM() dans vos SELECT, c’est que vos données ne sont pas propres.