Pagination SQL : l’autre solution

Je vais parler dans ce court billet d’un retour d’expérience. Et plus particulièrement d’une autre façon que le classique LIMIT … OFFSET … pour paginer les résultats en SQL. Comme j’utilise MySQL dans ma mission actuelle, les exemples de code SQL suivront le dialecte de ce SGBD, mais je n’utilise aucune fonctionnalité propre à celui-ci.

Une sombre histoire de batch…

Mon client m’a récemment demandé de refactorer un de ses batchs qui avait le mauvais goût d’être lent et grand consommateur de heap. En effet celui-ci remontait en mémoire tous les membres d’une organisation, cela pouvait aller jusqu’à plusieurs centaines de milliers, puis pour chacun des membres était encore effectué une dizaine de requêtes en base de données.

Pour résoudre ces problèmes, trois axes de refactoring ont été engagés : faire une requête spécifique pour ce batch qui permette de remonter toutes les informations nécessaires au traitement du membre et uniquement celle-ci, mettre en cache les données partagées par plusieurs membres et enfin ce qui nous intéresse aujourd’hui paginer le traitement et donc la requête SQL.

Je me lance dans ce refactoring et j’utilise LIMIT … OFFSET … pour paginer mes résultats, ce qui me donne une requête SQL dans ce genre :

1
2
3
4
5
SELECT m.memberId, ....
FROM Member m
WHERE m.organizationId = 42
ORDER BY m.memberId
LIMIT 64 OFFSET 256

Je teste le batch sur l’organisation qui compte le plus grand nombre de membres. Je surveille la heap, elle me dit merci. Cependant le temps d’exécution est long, aux alentours d’une heure. C’est alors qu’en surveillant de plus près l’exécution
de mon batch, je découvre le goulot d’étranglement. La requête qui récupère les informations nécessaires au traitement des membres met une dizaine de millisecondes à récupérer la première page, mais pour récupérer les dernières, le temps d’exécution est de plusieurs dizaines de secondes. Me voilà surpris, je vérifie mes index, ils semblent corrects. Que se passe-t-il donc ?

Mathias Winand à la rescousse !

J’aurais pu rester longtemps à ne pas comprendre les raisons de ce ralentissement progressif, si je n’étais tomber sur un article de Mathias Winand dans mon flux d’actualités. Ce monsieur est l’auteur d’un site et d’un blog : use-the-index-luke.com qui est une mine d’informations à l’usage des développeurs sur les questions de performance en SQL. On parle ici d’index, de requêtes SQL et non de tuning de SGBD.

L’article en question traitait de la pagination avec PostgreSQL. Il commence par expliquer le fonctionnement de la pagination classique, celle que j’avais utilisé, et les performances qui en résulte. Et en effet le temps d’exécution augmente plus la valeur de la page, i.e. l’offset, augmente. Tout s’expliquait. Mais alors comment faire pour avoir une pagination performante lorsqu’on a beaucoup de données et qu’on doit accéder à toutes les pages, même les dernières : “The seek method”.

Cette autre méthode de pagination était justement le sujet de la deuxième partie dudit article. L’idée derrière celle-ci est de filtrer les lignes déjà parcourues dans la clause WHERE et de ne pas utiliser l’OFFSET. Ainsi pour récupérer la pge N la requête ressemble à ceci :

1
2
3
4
5
SELECT m.memberId, ....
FROM Member m
WHERE m.organizationId = 42 AND m.memberId > memberIdOffset -- correspond au max des memberId de la page N-1
ORDER BY m.memberId
LIMIT 64

On obtient alors un temps de réponse constant quelque soit la page demandée. Et mon batch une fois refactoré ne mettait plus que quelques minutes à s’exécuter.

Il n’y a pas de magie

Comme souvent tout est question de compromis et ce qu’on gagne à un endroit on le perd à un autre. Avec cette méthode nous gagnons en performance. Cependant elle a des inconvénients :

  • Pour accéder à la page N, on doit préalablement parcourir de la page 1 à la page N-1 pour obtenir la condition dans la clause WHERE.
  • Réaliser une pagination bidirectionnelle est compliquée

Cette méthode convient donc particulièrement bien aux cas d’utilisation qui demandent intrinsèquement de faire le parcours de la page 1 à la page N comme par exemple :

  • les batchs, car on veut la plupart du temps parcourir toutes les pages.
  • les fils d’actualités ou listes sans fin, car on va généralement parcourir de la page 1 à la page N au fur et à mesure que l’utilisateur fait défiler le flux.
VN:R_U [1.9.22_1171]
Rating: 0 (from 0 votes)
Share
Ce contenu a été publié dans JDBC, Trucs & astuces, avec comme mot(s)-clef(s) , . Vous pouvez le mettre en favoris avec ce permalien.

Une réponse à Pagination SQL : l’autre solution

  1. mgrenonville dit :

    Malin !
    Une façon élégante de paginer dans les SGBD ne supportant pas la notion de OFFSET (ceux qui ont bossé avec Sybase lèvent le doigt)

    VN:R_U [1.9.22_1171]
    Rating: 0 (from 0 votes)

Laisser un commentaire