Les antipatterns SQL à éviter

 (datamethods.substack.com)
54 points par GN⁺ 2025-10-19 | 5 commentaires | Partager sur WhatsApp
  • Les antipatterns SQL rendent la maintenance des requêtes et des pipelines de données plus difficile, et entraînent des performances plus lentes que prévu
  • Parmi les cas les plus représentatifs figurent l’abus de CASE WHEN, l’application de fonctions sur des colonnes indexées, SELECT *, l’abus de DISTINCT, les vues et sous-requêtes imbriquées, ainsi que des structures de dépendance trop profondes
  • La plupart de ces problèmes proviennent de solutions de contournement temporaires dues à la pression sur les délais et la rapidité d’exécution ; à long terme, ils nuisent à la fois à la fiabilité des données et à la vitesse de développement
  • Comme solutions, il faut notamment des définitions de jointure claires, l’utilisation de tables de dimensions, la suppression des imbrications inutiles et un nettoyage régulier des vues
  • Il faut traiter SQL non pas comme un simple script, mais comme du code de production géré à l’échelle de l’équipe ; une conception initiale pensée pour la lisibilité réduit fortement les retouches

Introduction

  • Aujourd’hui, nous allons nous concentrer sur quelques antipatterns SQL fréquents et particulièrement impactants
  • Ces problèmes entraînent un cercle vicieux : baisse de la fiabilité des données, ralentissement du développement des requêtes, etc.
  • La liste ci-dessous ne couvre pas tous les cas ; pour une compréhension plus approfondie, le livre de Bill Karwin est recommandé

Des instructions CASE WHEN excessivement complexes

  • Dans les systèmes de grande taille, on utilise souvent des instructions CASE WHEN pour convertir des codes d’état (par ex. 1 = rupture de stock) en libellés lisibles par les humains
  • Ajouter cette logique CASE WHEN à une seule vue pour développer rapidement un dashboard ou un rapport constitue, à long terme, un antipattern
  • Cela provoque des duplications de logique par copier-coller, des divergences d’interprétation, et finit par rendre l’ensemble des requêtes confus
  • La solution consiste à créer séparément une table de dimensions (dimension table) ou une vue partagée pour convertir les codes d’état, afin de garantir la réutilisabilité

Utiliser des fonctions sur des colonnes indexées

  • Lorsque l’on applique une fonction à une colonne indexée, comme dans WHERE UPPER(name) = 'ABC', l’efficacité de l’index disparaît
    • Dans SQL Server, entre autres, cela peut entraîner un full table scan inutile
  • La solution consiste soit à indexer séparément la colonne après transformation, soit à transformer la valeur d’entrée pour simplifier la condition de la requête

Utiliser SELECT * dans une vue

  • Lors du développement d’une vue, SELECT * peut sembler pratique, mais si la structure (le schéma) change, la vue peut facilement casser
  • Comme cela inclut aussi des colonnes inutiles, cela crée des dépendances involontaires et des problèmes de performance ; il faut donc sélectionner explicitement les colonnes

Abuser de DISTINCT pour « résoudre » les doublons

  • Quand un mauvais JOIN produit des résultats dupliqués, utiliser SELECT DISTINCT comme solution temporaire masque un problème d’intégrité des données
    • La cause profonde réside dans des conditions de jointure incomplètes ou dans des erreurs de définition des relations (1:1, 1:N, etc.)
    • La bonne solution consiste à renforcer la logique de jointure pour clarifier la définition des relations et à garantir la cohérence relationnelle avant les agrégations ou les rapports

Empilement excessif de vues (Excessive View Layer Stacking)

  • Lorsque plusieurs équipes réutilisent des vues existantes et continuent d’empiler de nouvelles vues par-dessus, la chaîne de dépendances devient complexe et les performances se dégradent rapidement
    • Le débogage devient difficile, et faire évoluer une requête revient presque à un travail d’« archéologie »
  • Il faut périodiquement aplatir (flatten) la logique de transformation et, pour les calculs complexes, matérialiser (materialize) dans une vue ou une table de base clairement définie

Sous-requêtes trop profondément imbriquées

  • Des sous-requêtes profondément imbriquées sur plus de 3 ou 4 niveaux dégradent la lisibilité et compliquent le débogage
    • Il existe même des cas avec des sous-requêtes de plus de 5 000 lignes
  • L’utilisation des CTE (Common Table Expression) facilite la séparation des étapes logiques et améliore la clarté (readability) de la requête

Conclusion

  • En apparence, SQL semble simple, mais plus le système grossit, plus sa complexité tend à augmenter
  • La plupart des antipatterns ne viennent pas d’une mauvaise intention, mais de compromis faits pour obtenir rapidement un résultat (vitesse, délais, solutions temporaires)
  • Gérer SQL comme du code (gestion de versions, revue de code, conception claire) permet d’assurer à long terme à la fois la productivité et la fiabilité
  • Investir quelques minutes dès la conception initiale pour réfléchir à la clarté et à la cohérence réduit fortement les retouches et la confusion futures

À lire aussi

5 commentaires

 
aer0700 2025-10-20

Quand on n’a pas le temps, on se dit qu’on va juste éteindre l’incendie pour l’instant et réécrire ça plus tard, mais à force d’accumuler ce genre de décisions, on finit dans un véritable enfer de requêtes. J’en ai moi-même créé pas mal. Et pourtant, on sait très bien que ce « plus tard » où on les réécrira ne viendra jamais.
 
firefoxsaiko123 2025-10-20

Bof...
 
ilikeall 2025-10-20

« La plupart des problèmes viennent de solutions de fortune dues à la pression sur la vitesse et les délais »
Bouhou..
 
GN⁺ 2025-10-19
Avis Hacker News
  • Quand je vois DISTINCT dans une requête, je soupçonne que l’auteur ne comprend pas vraiment le modèle de données ou la théorie des ensembles, voire ni l’un ni l’autre
    • Parfois, DISTINCT peut aussi être le signe d’un schéma trop normalisé. Par exemple, je ne pense pas qu’il faille créer à tout prix une table addresses_cities juste pour éviter d’enregistrer plusieurs fois le nom d’une ville
    • Mon expérience est presque la même. Mais récemment, même avec tous les JOIN correctement faits, le fait d’ajouter DISTINCT dans un CTE a nettement amélioré les performances. J’ai l’impression que le query planner optimise mieux quand l’unicité des enregistrements est garantie
    • J’ai eu un retour similaire, disant que ce n’était pas une bonne idée, après avoir ajouté LIMIT 1 à une requête parce que je m’attendais à n’obtenir au plus qu’un seul résultat. Pourtant, sur de grandes tables (sqlite, mysql, postgresql), même après avoir trouvé l’enregistrement voulu, la base a tendance à continuer à parcourir toute la table
    • Je me demande comment savoir s’il est sûr d’omettre DISTINCT dans une requête SELECT x FROM t. Même si, dans le schéma de t, on voit que x a une contrainte PRIMARY ou UNIQUE, quelqu’un peut très bien supprimer cette contrainte peu après. Des doublons apparaîtront alors et on se demandera pourquoi. SQL n’est pas un langage d’ensembles (set), mais un langage de multisets (bag). À l’exécution, on cherche simplement la relation t et l’attribut x, puis on les renvoie. Il peut y avoir des doublons, le type peut même changer. Si l’on veut vraiment un Set, il faut explicitement écrire DISTINCT. Le query planner, lui, saura à l’exécution qu’il n’a pas besoin de déduplication si c’est UNIQUE ou PRIMARY
    • En Cypher, c’est plutôt l’inverse. Quand on manipule des données complexes avec neo4j, des nœuds dupliqués se retrouvent très facilement dans les résultats, donc DISTINCT est indispensable. En particulier avec des relations de longueur variable, sans DISTINCT ça devient plus lent et il y a beaucoup de doublons
  • J’ai écrit un tutoriel en deux parties, d’environ 9 000 caractères, sur la manière de concevoir correctement la structure des requêtes sans DISTINCT
    https://kb.databasedesignbook.com/posts/systematic-design-of-join-queries/
    • Très bon article. Ajouté aux favoris. Et je me suis rendu compte que c’était aussi un vrai livre
  • Un point qu’on mentionne rarement, ce sont les requêtes qui cherchent « ce qui n’existe pas ». Par exemple, l’usage de != ou NOT IN (...) est inefficace dans la grande majorité des cas (même si cela peut aller quand d’autres conditions ont déjà suffisamment réduit l’ensemble de résultats). Et il est important de comprendre comment la base traite les null. Est-ce que null et la chaîne vide sont équivalents ? Est-ce que null == null ? Cela peut varier selon la base de données
    • Concernant le traitement des null et l’indexation, les bases que j’ai utilisées n’indexent pas les valeurs null, donc une requête WHERE col IS NULL fonctionne de manière inefficace même si un index existe sur col. Si c’est vraiment nécessaire, je recommande de créer une colonne char(1) ou bit qui indique si col est null, puis d’indexer ce champ
    • Tu dis que != ou NOT IN (...) sont presque toujours inefficaces, mais je me demande pourquoi. Quand la valeur de droite est constante, cela devrait être une recherche dans une table de hachage et donc généralement efficace. Je me demande s’il existe une alternative plus performante
  • Je ne pense pas que tous les « anti-patterns » cités soient de vrais anti-patterns. Le problème des conditions de requête qui ne correspondent pas aux index vient au fond d’une mauvaise compréhension du fonctionnement des index. Et une bonne partie des problèmes mentionnés ici sont plus étroitement liés à la conception du schéma de base de données qu’à SQL lui-même. Si DISTINCT est nécessaire, c’est peut-être que la conception des clés primaires n’est pas correcte. Si l’on finit par empiler les view, c’est au fond que la conception des tables de base est mauvaise. Une bonne modélisation de base de données prévient tous ces problèmes en amont
  • En réalité, ces « anti-patterns » ne sont souvent que de simples solutions de contournement dues aux limites — ou à l’absence de conception — du langage SQL. Je suis en train de créer un nouveau langage qui fonctionne sur des bases SQL, et j’aimerais proposer de meilleures alternatives à chacun de ces problèmes. Ce n’est pas encore terminé et la documentation est encore incomplète, mais si cela vous intéresse, j’aimerais avoir des retours sur https://lutra-lang.org
    • L’expression « bases SQL » est ambiguë. SQL a été implémenté non seulement dans des bases relationnelles mais aussi dans certaines bases non relationnelles. Les spécialistes connaissent les problèmes de SQL depuis longtemps, et il y a déjà eu des alternatives comme Tutorial D de Chris Date et Hugh Darwen. Malgré cela, les alternatives n’ont pas réussi à s’imposer à cause des décennies de code SQL et d’outils accumulés. Grâce à SQL, j’ai eu pendant des décennies une stabilité professionnelle et des revenus réguliers, donc même s’il faut un meilleur langage, je vois aussi la situation d’un œil positif
    • Le projet a l’air intéressant. Je le suivrai volontiers s’il gagne encore en maturité
  • Le plus grand anti-pattern, c’est de ne pas considérer SQL comme un vrai langage de programmation plutôt que comme un simple langage de requête. Je recommande d’indenter le code de manière cohérente et de regrouper les parties logiquement liées. Je conseille de transformer les sous-requêtes en CTE. Il est aussi important de laisser des commentaires efficaces. Voici mon style : https://bentilly.blogspot.com/2011/02/sql-formatting-style.html
    • Je pense que ce genre de débat sur le style de code n’a guère de sens sans un outil de lint adapté
  • Le plus grand secret qui m’a permis d’accélérer mes requêtes et de réduire l’usage des ressources serveur a été de les rendre plus sargable
    https://en.wikipedia.org/wiki/Sargable
    https://www.brentozar.com/blitzcache/non-sargable-predicates/
    • Je me demande dans quelles communautés ce mot sargable est réellement utilisé. Je fais du SQL depuis plus de 20 ans, mais je ne l’ai presque jamais vu dans les manuels, sur Stack Overflow ou sur HN. Je me demande s’il est surtout utilisé autour d’une base particulière, dans certaines entreprises ou dans une communauté open source précise
    • En cherchant l’étymologie de sargable, cette réponse StackOverflow m’a aidé : https://dba.stackexchange.com/a/217983
      Le mot sargable est un mot-valise formé à partir de « Search ARGument ABLE »
  • Beaucoup de cas d’abus de CASE WHEN peuvent être résolus en centralisant la logique dans une UDF (User Defined Function)
    Utiliser une fonction sur une colonne indexée est un signe que la requête n’est pas sargable
    Au lieu d’abuser de DISTINCT, pour dédupliquer selon la granularité de la table au sein d’un fan-out dérivé d’un join, une requête du type 
    ROW_NUMBER() OVER (PARTITION BY <grain> ORDER BY <deterministic sort>) = 1
    peut être utile. Certaines bases prennent aussi en charge QUALIFY, ce qui rend la requête beaucoup plus propre
    explication de sargable
    QUALIFY in Redshift
    • Les problèmes de requêtes non sargable peuvent être résolus facilement avec des expression indexes. Du moins, je pense que c’est le cas avec sqlite
  • Il y a aussi des situations où imbriquer des view est réellement nécessaire. Dans notre logiciel de POS, nous utilisons beaucoup de view imbriquées pour créer une view dorsale qui permette de voir proprement les transactions d’un seul coup. Sinon, il faudrait écrire des clauses where différentes pour chaque table et retraiter à chaque fois toutes sortes de conditions comme les annulations, retours, suppressions, etc. Au moindre changement, il faudrait corriger des dizaines de view ou de procédures. Dans notre cas, l’usage de view imbriquées est bien plus pratique
  • Le problème de l’usage de fonctions sur des colonnes indexées mériterait d’être expliqué plus clairement. Lorsqu’une fonction s’applique à une colonne indexée, l’efficacité de l’index disparaît et on finit en pratique par faire un full scan, donc c’est lent. Je l’ai appris à mes dépens, par l’expérience
    • Il existe une documentation bien connue à ce sujet : https://use-the-index-luke.com/sql/where-clause/obfuscation
    • La solution proposée (par ex. ajouter un index sur une colonne UPPER(name)) n’est pas la meilleure, du moins sous MS SQL Server. Je ne sais pas ce qu’il en est du support dans d’autres bases, mais une meilleure solution consiste à créer directement une colonne calculée insensible à la casse avec COLLATE 
      ALTER TABLE example ADD name_ci AS name COLLATE SQL_Latin1_General_CI_AS;
      (à ajuster selon vos préférences)
    • Il y a une coquille dans le billet de blog lié. La première ligne devrait être en majuscules. Si l’index porte déjà sur les données après application de la fonction, alors, au moment de la requête, on ne fait pas un scan complet. Bien sûr, dans cet exemple, il vaudrait mieux dès le départ utiliser une collation insensible à la casse, mais de manière générale l’idée de fond reste valable
    • « Je l’ai appris par l’expérience » ressemble à la devise des développeurs SQL. Cela dit, SQL a évolué de façon assez stable pendant longtemps, donc connaître ces pièges à l’avance reste utile pendant des années
 
ahwjdekf 2025-10-21

Le plus important manque, on dirait.

  • utiliser un ORM