Skip to content

Avoid SQL queries in loops

chronometre performance - Avoid SQL queries in loops

There are several ways to optimize the performance of a web application by optimizing management with the database. One of the best practices recommended is that you do not place SQL queries in loops. This article explains why and how to avoid placing them in for, foreach, or while loops.

Performance of an application using SQL

Misuse of joins

Bad example

Joins are really to be used as soon as possible to limit the number of queries and get the most out of indexes. A bad example could be a PHP application that lists articles and then retrieves the author’s name for each article using a query inside a while loop.

$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 ...
  }
}

This technique is relatively long because each query to the database takes a significant amount of time. However, in this example there will be as many requests as the number of times it has entered the while loop.

Good example

A better strategy is to use a join, so there will only be one query that will be made to retrieve all of the data.

$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 ...
  }
}

Tip: it is possible to improve the performance of this query by judiciously using indexes.

Multiple UPDATE

When updating multiple data in the same table at the same time, many developers loop through to perform the UPDATE query multiple times. This technique is simple but is not really optimized in terms of performance.

Bad example

The example below represents a PHP application which registers several options decided by the user. Each option is updated in an “options” table using as many UPDATE queries as there are fields to modify.

$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);
}

As has been said, this method is long in terms of performance. Not only does the application have to make several queries, which takes time, but in some cases the database must update the index each time this table is updated.

Good example

It turns out that the previous code can be done in a single SQL query that uses the CASE operator. Using this operator, it is possible to specify that when the “id” field corresponds to the option that we want to modify, then it must be replaced with the new value. Here is the same example as earlier in a single request.

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')

It is very important to note that there is the WHERE condition which further improves performance. This condition is not mandatory, it is just that it allows to limit the tests to these 3 records only and not to all the rows of the table.

This little trick does not necessarily seem essential at first glance because it saves 2 requests (for example). But imagine that on the same application a user decides to modify a hundred parameters at once. Thanks to this trick, there will not be 100 UPDATE requests in a row but more than one, which significantly improves performance.

window.___gcfg = {lang: 'fr'}; (function() { var po = document.createElement('script'); po.type="text/javascript"; po.async = true; po.src="https://apis.google.com/js/plusone.js"; var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(po, s); })();

var _gaq = _gaq || []; _gaq.push(['_setAccount', 'UA-1599467-30']); _gaq.push(['_trackPageview']); _gaq.push(['_trackPageLoadTime']); (function() { var ga = document.createElement('script'); ga.type="text/javascript"; ga.async = true; ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js'; var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s); })();