Éviter les requêtes SQL dans des boucles

Il existe plusieurs façons d’optimiser les performances d’une application web en optimisant la gestion avec la base de données. Parmi les bonnes pratiques recommandées, il est souvent suggéré de ne pas placer de requêtes SQL dans des boucles. Cet article rappel pourquoi et comment procéder pour ne pas les placer dans des boucles for, foreach ou while.

Performance d’une application utilisant le SQL

Mauvaise utilisation des jointures

Mauvais exemple

Les jointures sont vraiment à utiliser dès que possible pour limiter le nombre de requêtes et exploiter au mieux les index. Un mauvais exemple pourrais être une application en PHP qui liste des articles et qui récupère ensuite le nom de l’auteur pour chaque article en utilisant une requête à l’intérieur d’une boucle while.

$result = $mysqli->query("SELECT id, titre, id_auteur FROM article");
if ($result) {
  // Lister les résultats
  while ($row = $result->fetch_object()) {
    $sql = "SELECT nom FROM utilisateur WHERE id = " . $row->id_auteur;
    $result_commentaire = $mysqli->query($sql);
    // Reste du code ...
  }
}

Cette technique est relativement longue car chaque requête à la base de données prend un temps non négligeable. Or dans cet exemple il va y avoir autant de requêtes que le nombre de fois où l’ont rentre dans la boucle while.

Bon exemple

Une meilleure stratégie consiste à utiliser une jointure, ainsi il n’y aura qu’une seule requête qui sera effectuée pour récupérer toutes les données.

$sql = "SELECT id, titre, nom 
  FROM article 
  INNER JOIN utilisateur ON utilisateur.id = article.id_auteur";
$result = $mysqli->query($sql);
if ($result) {
  // Lister les résultats
  while ($row = $result->fetch_object()) {
    // Reste du code ...
  }
}

Astuce : il est possible d’améliorer les performances de cette requête en utilisant judicieusement des index.

Multiple UPDATE

Lors de la mise à jour de plusieurs données d’un même tableau en même temps, beaucoup de développeur font une boucle pour effectuer plusieurs fois la requête UPDATE. Cette technique est simple mais n’est pas vraiment optimisée en terme de performance.

Mauvais exemple

L’exemple ci-dessous représente une application PHP qui enregistre plusieurs options décidés par l’utilisateur. Chaque option est mise à jour dans une table “options” à l’aide d’autant de requête UPDATE qu’il y a de champs à modifier.

$options = array(
    'couleur_fond' => 'bleu',
    'commentaire_par_page' => '60',
    'reponse' => 'auto',
  );
foreach ($options as $id => $nouvelle_valeur) {
  $sql = "UPDATE options 
    SET valeur = '" . $nouvelle_valeur . "' 
    WHERE id = '" . $id . "'";
  $mysqli->query($sql);
}

Comme cela à été dit, cette méthode est longue en terme de performance. Non seulement l’application doit faire plusieurs requêtes, ce qui prend du temps, mais en plus dans certains cas la base de données doit mettre à jour l’index à chaque mise à jour du cette table.

Bon exemple

Il s’avère que le code précédent peut être effectué dans une seule requête SQL qui utilise l’opérateur CASE. A l’aide de cet opérateur, il est possible de spécifier que quand le champ “id” correspond à l’option qu’on souhaite modifier, alors il faut le remplacer la nouvelle valeur. Voici le même exemple que tout à l’heure en une seule requête.

UPDATE options
  SET valeur = CASE id
    WHEN 'couleur_fond' THEN 'bleu'
    WHEN 'commentaire_par_page' THEN '60'
    WHEN 'reponse' THEN 'auto'
  END
WHERE id IN ('couleur_fond', 'commentaire_par_page', 'reponse')

Il est très important de remarquer qu’il y a la condition WHERE qui permet d’améliorer encore plus les performances. Cette condition n’est pas obligatoire, c’est juste qu’elle permet de limiter les tests à ces 3 enregistrements uniquement et non pas à toutes les lignes de la table.

Cette petite astuce ne semble pas forcément indispensable à première vue car elle permet d’économiser 2 requêtes (par exemple). Mais imaginez que sur la même application un utilisateur décide de modifier une centaine de paramètres d’un coup. Grâce à cette astuce, il n’y aura pas 100 requête UPDATE à la suite mais plus qu’une seule, ce qui améliore très significativement les performances.