par Guillaume Lelarge.

Logique intelligente


Une des fonctionnalités clés des bases de données SQL est leur support des requêtes personnalisées : de nouvelles requêtes peuvent être exécutées à tout moment. Ceci est seulement possible parce que l'optimiseur de requêtes (planificateur de requêtes) fonctionne à l'exécution ; il analyse chaque requête à sa réception et génère un plan d'exécution raisonnable immédiatement. La surcharge introduite par l'optimisation à l'exécution peut être minimisée avec les paramètres liés.

L'intérêt de ceci est que les bases de données sont optimisées pour le SQL dynamique. Utilisez-le si vous en avez besoin.

Néanmoins, une pratique fréquemment répondue est d'éviter le SQL dynamique et de privilégier le SQL statique, principalement à cause du mythe indiquant que « le SQL dynamique est lent ». Cette pratique fait plus de mal que de bien si la base de données utilise un cache partagé de plan d'exécution comme DB2, Oracle ou SQL Server.

Pour cette démonstration, imaginons une application qui exécute des requêtes sur la table EMPLOYES. L'application permet la recherche par identifiant de société, identifiant d'employé et nom (non sensible à la casse), toute combinaison acceptée. Il est possible d'écrire une seule requête qui couvre tous les cas en utilisant la logique « intelligente ».

SELECT prenom, nom, id_supplementaire, id_employe
  FROM employes
 WHERE ( id_supplementaire = :id_supp OR :id_supp IS NULL )
   AND ( id_employe        = :id_emp  OR :id_emp  IS NULL )
   AND ( UPPER(nom)        = :nom     OR :nom     IS NULL )

La requête utilise les variables liées nommées pour faciliter la lecture. Toutes les expressions possibles de filtres sont codées statiquement dans la requête. Quand un filtre n'est plus nécessaire, vous pouvez simplement utiliser NULL à la place du terme de recherche : cela désactive la condition via la logique OR.

Cette requête SQL est parfaitement raisonnable. L'utilisation de NULL est même en ligne avec sa définition suivant la logique à trois valeurs du SQL. Néanmoins, c'est un des pires problèmes de performances.

La base de données ne peut pas optimiser le plan d'exécution pour un filtre particulier parce que tous peuvent être annulés à l'exécution. La base de données a besoin de préparer la requête pour le pire des cas : si tous les filtres sont désactivés :

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
|  0 | SELECT STATEMENT  |           |    2 |  478 |
|* 1 |  TABLE ACCESS FULL| EMPLOYES  |    2 |  478 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((:NOM     IS NULL OR UPPER("NOM")=:NOM) 
       AND (:ID_EMP  IS NULL OR "ID_EMPLOYE"=:ID_EMP) 
       AND (:ID_SUPP IS NULL OR "ID_SUPPLEMENTAIRE"=:ID_SUPP))

En conséquence, la base de données utilise un parcours de table complet même s'il existe un index pour chaque colonne.

Ce n'est pas que la base de données ne peut pas résoudre la logique « intelligente ». Elle crée un plan d'exécution générique à cause de l'utilisation des variables liées pour qu'elle puisse être mise en cache et ré-utilisée avec d'autres valeurs plus tard. Si nous n'utilisons pas de paramètres liés mais qu'à la place, nous écrivons les valeurs réelles dans la requête SQL, l'optimiseur sélectionne le bon index pour le filtre actif.

SELECT prenom, nom, id_supplementaire, id_employe
  FROM employes
 WHERE( id_supplementaire    = NULL     OR NULL IS NULL )
   AND( id_employe           = NULL     OR NULL IS NULL )
   AND( UPPER(nom)           = 'WINAND' OR 'WINAND' IS NULL )
---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |    1 |    2 |
| 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYES    |    1 |    2 |
|*2 |   INDEX RANGE SCAN          | NOM_MAJ_EMP |    1 |    1 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  2 - access(UPPER("NOM")='WINAND')

Néanmoins, ce n'est pas une solution. Cela prouve seulement que la base de données peut résoudre ces conditions.

Attention

Utiliser des valeurs littérales rend votre application vulnérable aux attaques par injection SQL et peut causer des problèmes de performances à cause de la surcharge du à une optimisation accrue.

La solution évidente pour les requêtes dynamiques est le SQL dynamique. Suivant le principe KISS, dites simplement à la base de données ce dont vous avez besoin maintenant et rien d'autres.

SELECT prenom, nom, id_supplementaire, id_employe
  FROM employes
 WHERE UPPER(nom) = :nom

Notez que la requête utilise un paramètre lié.

Astuce

Utilisez le SQL dynamique si vous avez besoin de clauses where dynamiques.

Utilisez toujours des paramètres liés lors de la génération de SQL dynamique. Dans le cas contraire, le mythe du « SQL dynamique lent » devient réalité.

Le problème décrit dans cette section est très répandu. Toutes les bases de données qui utilisent un cache partagé de plans d'exécution ont une fonctionnalité pour essayer de contourner ce problème. Cela introduit souvent de nouveaux problèmes, voire des bugs.

DB2

DB2 utilise un cache partagé de plans d'exécution et il est complètement exposé au problème décrit dans cette section.

DB2 permet de spécifier l'approche de ré-optimisation en utilisant l'astuce REOPT. La valeur par défaut est NONE, qui produit un plan d'exécution générique et souffre du problème décrit ci-dessus. REOPT(ALWAYS) forcera l'optimiseur à toujours tester les variables liées réelles pour produire le meilleur plan pour chaque exécution. Dans les faits, cela désactive la mise en cache du plan d'exécution pour cette requête.

La dernière option est REOPT(ONCE). Elle testera les paramètres liées pour la première exécution seulement. Le problème de cette approche est son comportement non déterministe : les valeurs provenant de la première exécution affectent toutes les exécutions. Le plan d'exécution peut changer à chaque redémarrage de la base de données ou, de façon moins prévisible, le plan en cache expire et l'optimiseur le recrée en utilisant différentes valeurs la prochaine fois que la requête est exécutée.

MySQL

MySQL ne souffre pas de ce problème particulier car il ne dispose pas d'un cache de plans d'exécution. Une demande d'ajout de fonctionnalité datant de 2009 discute de l'impact de la mise en cache du plan d'exécution. Il semble que l'optimiseur de MySQL est suffisamment simple pour que la mise en cache du plan d'exécution ne soit pas d'un grand intérêt.

Oracle

La base de données Oracle utilise un cache partagé de plans d'exécution (« SQL area ») et est complètement exposée au problème décrit dans cette section.

Oracle a introduit une fonctionnalité appelée bind peeking avec la version 9i. Le « Bind peeking » permet à l'optimiseur d'utiliser les vraies valeurs de la première exécution lors de la préparation d'un plan d'exécution. Le problème de cette approche est son comportement non déterministe : les valeurs provenant de la première exécution affectent toutes les exécutions. Le plan d'exécution peut changer à chaque redémarrage de la base de données ou, de façon moins prévisible, le plan en cache expire et l'optimiseur le recrée en utilisant différentes valeurs la prochaine fois que la requête est exécutée.

La version 11g a introduit le partage de curseur adaptatif pour améliorer encore plus la situation. Cette fonctionnalité rend possible la mise en cache par la base de données de plusieurs plans d'exécution pour la même requête SQL. De plus, l'optimiseur récupère les paramètres liés et stockent leur sélectivité estimée avec le plan d'exécution. Quand le cache est accédé par la suite, la sélectivité des valeurs liées doit faire partie de l'échelle de sélectivité d'un plan d'exécution pour cette requête. Si un tel plan d'exécution existe déjà, la base de données le remplace avec le nouveau plan d'exécution qui couvre aussi les estimations de sélectivité des valeurs liées actuelles. Dans le cas contraire, elle met en cache une nouvelle variante du plan d'exécution pour cette requête, avec les estimations de sélectivité.

PostgreSQL

Le cache de plans d'exécutions de PostgreSQL fonctionne seulement pour les requêtes ouvertes, autrement dit aussi longtemps que le PreparedStatement est ouvert. Le problème décrit ci-dessus survient seulement lors de la réutilisation d'un pointeur de requête. Notez que le connecteur JDBC de PostgreSQL active le cache seulement après la cinquième exécution. Voir aussi : Planifier avec les valeurs liées réelles.

SQL Server

SQL Server utilise une méthode appelée écoute de paramètres. L'écoute de paramètre permet à l'optimiseur d'utiliser les valeurs liées réelles de la première exécution lors de l'analyse. Le problème de cette approche est son comportement non déterministe : les valeurs provenant de la première exécution affectent toutes les exécutions. Le plan d'exécution peut changer à chaque redémarrage de la base de données ou, de façon moins prévisible, le plan en cache expire et l'optimiseur le recrée en utilisant différentes valeurs la prochaine fois que la requête est exécutée.

SQL Server 2005 a ajouté de nouvelles options de requêtes pour avoir un contrôle plus fin sur l'écoute de paramètres et sur la recompilation. L'astuce de requête RECOMPILE contourne le cache de plans pour une requête particulière. OPTIMIZE FOR permet la spécification de vraies valeurs pour les paramètres, utilisées seulement pour l'optimisation. Enfin, vous pouvez fournir un plan d'exécution complet avec l'option USE PLAN.

L'implémentation originale de l'option OPTION(RECOMPILE) avait un bug. Du coup, il ne considérait pas toutes les variables liées. La nouvelle implémentation proposée avec SQL Server 2008 a un autre bug, rendant la situation très confuse. Erland Sommarskog a collectionné toutes les informations intéressantes sur les différentes versions de SQL Server.

Bien que les méthodes heuristiques puissent améliorer le problème de la « logique intelligente » jusqu'à un certain point, elles ont été conçues pour gérer les problèmes des paramètres liés en connexion avec les histogrammes de colonnes et les expressions LIKE.

La méthode la plus fiable pour arriver au meilleur plan d'exécution est d'éviter les filtres inutiles dans la requête SQL.

À propos de l'auteur

Photo de Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing—for that he also published the book SQL Performance Explained.

“Use The Index, Luke!” by Markus Winand and translated by Guillaume Lelarge is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Mentions légales | Contact | NO WARRANTY | Marque déposée | Privacy | CC-BY-NC-ND 3.0 license