par Guillaume Lelarge.

Requêtes avec paramètres


Cette section couvre un thème qui est souvent ignoré dans les livres sur le SQL ; les requêtes avec paramètres et les paramètres liés.

Les paramètres liés, aussi appelés paramètres dynamiques ou variables liées (« bind parameters » ou « bind variables » en anglais) sont une autre façon de passer des données à la base de données. Au lieu de placer les valeurs directement dans la requête SQL, vous pouvez utiliser un marqueur comme ?, :nom ou @nom, et fournir les vraies valeurs en utilisant un appel API séparé.

Il n'y a rien de mal à écrire les valeurs directement dans les requêtes ; néanmoins, il y a deux bonnes raisons pour utiliser les paramètres liés dans des programmes :

Sécurité

Les variables liées sont le meilleur moyen pour éviter les injections SQL.

Performance

Les bases de données avec un cache de plan d'exécution comme SQL Server et Oracle peuvent réutiliser un plan d'exécution si la même requête est exécutée plusieurs fois. Cela permet d'éviter la reconstruction du plan d'exécution mais cela ne fonctionne que si la requête SQL est strictement identique. Si vous placez différentes valeurs dans la requête SQL, la base de données la gère comme une nouvelle requête et crée un nouveau plan d'exécution.

Lors de l'utilisation de paramètres liés, vous ne pouvez pas écrire les vraies valeurs. À la place, vous insérez des marqueurs dans la requête SQL. De cette façon, les requêtes ne changent pas quand elles sont exécutées avec des valeurs différentes.

Naturellement, il existe des exceptions. Par exemple, si le volume de données impliquées dépend des valeurs réelles:

99 rows selected.

SELECT prenom, nom
  FROM employes
 WHERE id_supplementaire = 20;

---------------------------------------------------------------
|Id | Operation                   | Name        | Rows | Cost |
---------------------------------------------------------------
| 0 | SELECT STATEMENT            |             |   99 |   70 |
| 1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYES    |   99 |   70 |
|*2 |   INDEX RANGE SCAN          | EMPLOYE_PK  |   99 |    2 |
---------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID_SUPPLEMENTAIRE"=20)

Une recherche par index apporte les meilleures performances pour des petites sociétés mais une opération TABLE ACCESS FULL peut être encore meilleure que l'index pour les grosses sociétés :

1000 rows selected.

SELECT prenom, nom
  FROM employes
 WHERE id_supplementaire = 30;

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

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID_SUPPLEMENTAIRE"=30)

Dans ce cas, l'histogramme sur ID_SUPPLEMENTAIRE remplit son but. L'optimiseur l'utilise pour déterminer la fréquence des identifiants supplémentaires mentionnés dans la requête SQL. En conséquence, il obtient deux estimations différentes du nombre de lignes pour les deux requêtes.

Le calcul de coût suivant va du coup résulter en deux valeurs de coût. Quand l'optimiseur sélectionne enfin un plan d'exécution, il prend le plan ayant le plus petit coût. Pour les plus petites sociétés, il s'agit du plan utilisant l'index.

Le coût d'une opération TABLE ACCESS BY INDEX ROWID est très dépendant de l'estimation du nombre de lignes. Sélectionner dix fois plus de lignes élèvera le coût par ce même facteur. Le coût complet utilisant l'index est alors plus important qu'un parcours de table complet. Du coup, l'optimiseur sélectionnera l'autre plan d'exécution pour une plus grosse société.

Lors de l'utilisation de paramètres liés, l'optimiseur ne dispose pas de valeurs concrètes pour déterminer leur fréquences. Du coup, il suppose une distribution identique et obtient toujours la même estimation du nombre de lignes et du coût. Au final, il sélectionnera toujours le même plan d'exécution.

Astuce

Les histogrammes des colonnes sont principalement utiles si les valeurs ne sont pas uniformément distribuées.

Pour les colonnes ayant une distribution uniforme, il suffit fréquemment de diviser le nombre de valeurs distinctes par le nombre de lignes dans la table. Cette méthode fonctionne aussi lors de l'utilisation de paramètres liés.

Si nous comparons l'optimiseur à un compilateur, les variables liées sont comme des variables de programme mais si vous écrivez les valeurs directement dans la requête, elles sont comme des constantes. La base de données peut utiliser les valeurs à partir de la requête SQL lors de l'optimisation tout comme un compilateur peut évaluer les expressions constantes à la compilation. Pour le dire simplement, les paramètres liés ne sont pas visibles à l'optimiseur tout comme les valeurs des variables ne sont pas connues du compilateur.

À partir de là, il est un peu paradoxal que les paramètres liés puissent améliorer les performances si ne pas les utiliser permet à l'optimiseur de toujours choisir le meilleur plan d'exécution. Mais la question est à quel prix ? Générer et évaluer toutes les variantes des plans d'exécution est un gros travail qui n'a aucun intérêt si, au final, on obtient le même résultat.

Astuce

Ne pas utiliser les paramètres liés est comme recompiler un programme chaque fois qu'on veut l'utiliser.

Décider de la construction d'un plan spécialisé ou générique est un dilemme pour la base de données. Soit un effort est fait pour évaluer tous les plans possibles pour chaque exécution dans le but d'obtenir le meilleur plan d'exécution possible, soit on évite la surcharge occasionnée par l'optimisation en utilisant un plan d'exécution mis en cache quand cela est possible, tout en acceptant le risque d'utiliser un plan d'exécution non optimal. Le dilemme est que la base de données ne sait pas si le cycle d'optimisation complet délivre un plan d'exécution différent sans réaliser réellement l'optimisation complète. Les concepteurs de bases de données essaient de résoudre ce dilemme avec des méthodes heuristiques... dont le succès reste très limité.

En tant que développeur, vous devez utiliser les paramètres liés de façon délibérée pour aider à résoudre ce dilemme. Autrement dit, vous devez toujours utiliser les paramètres liés sauf pour les valeurs qui pourraient influencer le plan d'exécution.

Les codes de statut non équitablement distribués, comme « à faire » et « fait » sont un excellent exemple. Le nombre d'enregistrements marqués « fait » excède généralement très fortement le nombre d'enregistrements « à faire ». Utiliser un index a du sens pour rechercher les enregistrements « à faire » dans ce cas. Le partitionnement est un autre exemple, notam­ment si vous divisez des tables et index sur plusieurs espaces de stockage. Les valeurs réelles peuvent alors influencer les partitions à parcourir. Les performances des requêtes LIKE peuvent aussi souffrir de l'utilisation des paramètres liés comme nous le verrons dans la prochaine section.

Astuce

En fait, il n'existe que quelques cas pour lesquels les valeurs réelles affectent le plan d'exécution. Du coup, vous devez utiliser les paramètres liés en cas de doute, ne serait-ce que pour empêcher les injections SQL.

Les astuces de code suivant montrent comment utiliser les paramètres liés dans différents langages de programmation.

C#

Sans paramètres liés :

int id_supplementaire;
SqlCommand cmd = new SqlCommand(
            "select prenom, nom" 
          + "  from employes"
          + " where id_supplementaire = " + id_supplementaire
          , connection);

Avec un paramètre lié :

int id_supplementaire;
SqlCommand cmd = new SqlCommand(
            "select prenom, nom" 
          + "  from employees"
          + " where id_supplementaire = @id_supplementaire
          , connection);
cmd.Parameters.AddWithValue( "@id_supplementaire"
                           , id_supplementaire);

Voir aussi la documentation de la classe SqlParameterCollection.

Java

Sans paramètre lié :

int id_supplementaire;
Statement cmd = connection.createStatement(
           "select prenom, nom" 
         + "  from employes"
         + " where id_supplementaire = " + id_supplementaire
         );

Avec paramètre lié :

int id_supplementaire;
PreparedStatement cmd = connection.prepareStatement(
           "select prenom, nom" 
         + "  from employes"
         + " where id_supplementaire = ?"
         );
cmd.setInt(1, id_supplementaire);

Voir aussi la documentation de la classe PreparedStatement.

Perl

Sans paramètre lié :

my $id_supplementaire;
my $sth = $dbh->prepare(
             "select prenom, nom" 
           . "  from employes"
           . " where id_supplementaire = $id_supplementaire"
           );
$sth->execute();

Avec paramètre lié :

my $id_supplementaire;
my $sth = $dbh->prepare(
             "select prenom, nom" 
           . "  from employes"
           . " where id_supplementaire = ?"
           );
$sth->execute($id_supplementaire);

Voir : Programming the Perl DBI.

PHP

En utilisant MySQL, sans paramètre lié :

$mysqli->query(
          "select prenom, nom" 
        . "  from employes"
        . " where id_supplementaire = " . $id_supplementaire);

Avec paramètre lié :

if ($stmt = $mysqli->prepare("select prenom, nom" 
                           . "  from employes"
                           . " where id_supplementaire = ?")) 
{
   $stmt->bind_param("i", $id_supplementaire);
   $stmt->execute();
} else {
  /* handle SQL error */
}

Voir aussi la documentation de la classe mysqli_stmt::bind_param et le chapitre « Prepared statements and stored procedures » de la documentation PDO.

Ruby

Sans paramètre lié :

dbh.execute("select prenom, nom" 
          + "  from employes"
          + " where id_supplementaire = #{id_supplementaire}");

Avec paramètre lié :

dbh.prepare("select prenom, nom" 
          + "  from employes"
          + " where id_supplementaire = ?");
dbh.execute(id_supplementaire);

Voir aussi le chapitre « Quoting, Placeholders, and Parameter Binding » dans le tutoriel DBI de Ruby.

Le point d'interrogation (?) est le seul caractère marqueur que le standard SQL définit. Les points d'interrogation sont des paramètres de position. Cela signifie que les points d'interrogation sont comptés de la gauche vers la droite. Pour lier une valeur à un point d'interrogation particulier, vous devez indiquer son numéro. Néanmoins, cela peut se révéler peu pratique à cause des changements de numéros lors de l'ajout et de la suppression des marqueurs. La plupart des bases de données propose une extension propriétaire aux paramètres nommés pour résoudre ce problème, c'est-à-dire en utilisant un symbole (@nom) ou le signe deux-points (:nom).

Remarque

Les paramètres liés ne peuvent pas changer la structure d'une requête SQL.

Cela signifie que vous ne pouvez pas utiliser les paramètres liés pour les noms de tables ou de colonnes. Les paramètres liés suivants ne fonctionnent pas:

String sql = prepare("SELECT * FROM ? WHERE ?");

sql.execute('employes', 'id_employe = 1');

Si vous devez changer la structure d'une requête SQL à l'exécution, utilisez du SQL dynamique.

Partage de curseur et paramétrisation automatique

Plus l'optimiseur et la requête SQL sont complexes, plus la mise en cache des plans d'exécution devient important. Les bases de données SQL Server et Oracle ont des fonctionnalités permettant de remplacer automatiquement les valeurs littérales dans une chaîne SQL avec des paramètres liés. Ces fonctionnalités sont appelées CURSOR_SHARING (Oracle) ou paramétrisation forcée (SQL Server).

Ces deux fonctionnalités sont des contournements pour les appli­cations qui n'utilisent pas du tout les paramètres liés. Activer ces fonctionnalités empêche les développeurs d'utiliser volontairement des valeurs littérales.

Voir également

À 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