Pagination SQL : problèmes et solutions

Une pagination en SQL est un processus consistant à récupérer les éléments d’une base de données, puis à les scinder afin de permettre de les parcourir page par page. Cette technique est mise en œuvre aujourd’hui, par les développeurs sql, pour de nombreuses applications Web. Les pages Google sont un exemple.

Toutefois, il est important de noter que lorsque la base de données comporte un nombre trop important d’éléments, la pagination peut devenir plus complexe. Si une meilleure stratégie n’est pas adoptée, des problèmes de performance peuvent commencer à se présenter.

Comment savoir que vous faites mal la pagination en SQL et quelle solution mettre en place pour éviter de dégrader la qualité de vos services ?

La méthode d’implémentation naïve

La méthode d’implémentation naïve est la solution la plus simple et la plus courante qui est utilisée par les développeurs SQL pour les applications nécessitant une pagination en SQL. Elle consiste généralement en l’utilisation des clauses LIMIT et OFFSET.

Même si la plupart du temps, une pagination avec ces deux clauses fonctionne plutôt bien, il faut savoir qu’à partir d’une quantité de données importantes, cela peut s’avérer être une mauvaise idée.

En effet, les requêtes avec OFFSET par exemple peuvent prendre, en cas de volume élevé de données, beaucoup plus de temps à répondre. Cela crée un réel problème de performance et peut affecter les applications dans leur fonctionnement.

Pour mieux comprendre, prenez comme exemple une table possédant 1 000 000 de produits dans une base SQL.

Lorsque vous lancez la requête de pagination avec OFFSET, vous pourrez constater qu’au fur et à mesure que la valeur de l’OFFSET augmente, les performances se détériorent de plus en plus. Le temps de réponse devient, en effet, plus long.

Si vous inspectez plus attentivement les résultats de l’optimiseur de base de données pour la requête de pagination formulée, vous remarquerez un détail particulier. En effet, la base de données utilise un système de tri pour ordonner la colonne non indexée et un index pour la partie tenant et id.

La pagination naïve avec un index entraîne un temps de réponse beaucoup plus élevé que la pagination avec l’identifiant unique de la primary key correspondant à la colonne non indexée. Cela permet de tirer 3 principales conclusions :

1. Il faut savoir que l’utilisation de la clause OFFSET sur une colonne non indexée triée se révèle comme une tâche lourde pour la base de données. Si vous procédez ainsi, sachez que vous faites mal la pagination en SQL ;
2. Il convient de noter également que, même sur la primary key, l’utilisation de l’OFFSET entraîne une lenteur au fur et à mesure que le volume de la base de données augmente. Cela est dû à la croissance du nombre de lignes qui doivent être parcourues puis supprimées ;
3. Utiliser OFFSET sur un index avec un grand key_length a plus d’impact sur les temps de réponse qu’avec un petit key_length.

Ces différentes conclusions permettent d’affirmer que vous faites mal la pagination en SQL si vous utilisez OFFSET. Cette manière de procéder entraîne plusieurs inconvénients:

• En effet, pour un grand volume de données, les dernières pages sont toujours plus difficiles à récupérer. La raison est que le nombre de lignes à charger et à supprimer est élevé.
• En plus de cela, pour une base de données dont la quantité d’éléments est constamment en croissance, atteindre les premières lignes devient moins facile avec le temps.
• Le temps de réponse s’allonge et le site ou l’application devient presque inutilisable lorsqu’il y a des milliers d’utilisateurs.

Par ailleurs, un autre inconvénient de la pagination en SQL avec OFFSET, c’est qu’en défilant page par page, si les résultats sont insérés selon le tri de colonnes, certains d’entre eux peuvent être omis pendant que d’autres vont se dupliquer.

Si vous ne pouvez pas réduire la quantité d’informations à parcourir ainsi que le nombre de pages à afficher, la pagination en SQL avec OFFSET reste une très mauvaise idée.

La méthode Seek pour la pagination en SQL 

La meilleure alternative à l’inefficacité des clauses OFFSET et LIMIT en matière de pagination en SQL, est la méthode Seek, également appelée méthode de recherche.

L’utilisation de cette méthode permet d’accélérer le processus de pagination. En effet, elle rend le temps de réponse plus court et moins dépendant de la taille de la base de données.

La méthode est très appréciée des développeurs SQL, des développeurs full-stack et des intégrateurs d’applications.

L’application de la méthode Seek permet par ailleurs de relever plusieurs changements essentiels :

• Une valeur unique est désormais utilisée comme signet pour identifier chaque ligne. C’est cette valeur qui permet d’obtenir la page suivante ;
• Il est important à chaque fois de déterminer la colonne exacte qui sera utilisée dans le cadre de la pagination afin de relever l’index correspondant ;
• Pour ordonner les lignes dans des colonnes non-uniques, il est nécessaire de créer des index multi-colonnes en utilisant à cet effet la primary key comme l’une des colonnes. Ensuite, il va falloir adapter la requête afin de naviguer entre les pages en suivant son index.

Même si elle rend plus complexe le développement, la méthode de pagination Seek est vraiment très efficace. Elle peut même paginer une table de 100 millions de produits et toutes les pages seront rapides. Il suffit d’avoir les bons index.

L’autre intérêt majeur de cette méthode est que, lorsqu’elle est mise en application dès le début, elle assure un développement pour l’avenir. En effet, si elle fonctionne bien aujourd’hui avec une base de 10000 lignes, elle fonctionnera toujours bien dans 10 ans lorsque vous aurez 10 millions de lignes.

Pour conclure

Si la performance vous tient réellement à cœur, il faut savoir que vous faites mal la pagination si vous utilisez pour ce faire la clause OFFSET. Cette dernière n’est vraiment pas appropriée pour paginer de très gros volumes de données. Elle n’est recommandée que dans des cas spécifiques.

En présence d’une quantité importante de données, la méthode Seek demeure la solution de pagination en SQL la plus efficace, permettant de maintenir une performance optimale et d’éviter une dégradation du fonctionnement de son application ou de son site Web.