Doit-on jouer mille fois une requête ou une fois mille requêtes ?

Contexte

Il s’agit d’un cas classique de développement web : nous avons besoin de paginer les résultats d’une requête SQL.

À partir d’une requête de recherche (sur une base MySQL), nous voulons :

• Récupérer n éléments à partir d’un indice i (contenu de la page)
• Avoir le nombre total d’éléments retournés par la requête

Stratégies testées

Utiliser deux requêtes

• Exécuter la requête en select avec offset et limit pour ne récupérer que les n éléments de la page.
• Exécuter la requête en select count pour avoir le nombre total d’éléments retournés.

Utiliser un curseur

• Récupérer un curseur à partir de la requête SQL
• Déplacer le curseur sur le premier élément de la page
• Charger les n éléments de la page
• Déplacer le curseur sur le dernier élément de la requête
• Récupérer l’indice du dernier élément (qui correspondra au nombre total d’éléments)

Comparaison des stratégies

Quelle est alors la stratégie la plus rapide ?

En mesurant le temps d’exécution d’une requête REST de recherche, via des contrôlleurs Spring WebFlux sur une table de plus de cinq cent mille lignes, on obtient les résultats suivants :

Le curseur s’avère plus lent que l’exécution de deux requêtes en suivant.

Cela s’explique, car le curseur charge les données ligne par ligne, alors que les requêtes SQL les chargent par ensemble de lignes.

Conclusion

Nous sommes partis sur l’exécution de deux requêtes (select et count), car il s’agit de la méthode la plus performante.

On préfère exécuter mille fois une requête.

Annexe : Code utilisé pour les tests

La base de données est accédée en Java via la librairie JOOQ.

Utiliser deux requêtes

final int offset = 10;
final int limit = 5;

final List<Record> content = dslContext.selectFrom(Tables.USER)
        .where(Tables.USER.MAIL.like("%@martoni.com"))
        .addOffset(offset)
        .addLimit(limit)
        .fetch();

final int totalElements = dslContext.fetchCount(
        dslContext.selectFrom(Tables.USER)
            .where(Tables.USER.MAIL.like("%@martoni.com"))
);
// (...)

Utiliser un curseur

final int baseIndex = 9;
final int nbElements = 5;

final ResultQuery<Record> query = dslContext.selectFrom(Tables.USER)
        .where(Tables.USER.MAIL.like("%@martoni.com"));

try (Cursor<Record> cursor = selectQuery
        .resultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE)
        .resultSetConcurrency(ResultSet.CONCUR_READ_ONLY)
        .fetchLazy()){

    // Page content
    final List<Record> content = new ArrayList<>();
    final ResultSet resultSet = cursor.resultSet();
    resultSet.absolute(baseIndex);
    int i = 0;
    while (cursor.hasNext() && i < nbElements) {
        content.add(cursor.fetchNext());
        ++i;
    }

    // Total number of elements
    resultSet.last();
    final int totalElements = resultSet.getRow();
    // (...)
}