Pagination

Régulièrement, je rencontre des projets, essentiellement web, dans lesquels il y a des besoins de pagination de liste. Par exemple, un site e-commerce a besoin d’afficher une liste de produits d’une catégorie. Bien souvent, les développeurs râlent après SQL Server car il n’y aurait pas d’options comme le LIMIT de MySQL. Je vais donc vous montrer comment réaliser une pagination avec SQL Server.

Le besoin

Dans cet article, je vais partir du principe que je travaille pour un site e-commerce qui me demande d’afficher la liste des produits en fonction de la catégorie choisie. Il faudra que cette liste n’affiche que 20 articles par page et bien sur que les pages soient affichées très rapidement.

Mise en place

Pour réaliser ces tests, j’ai créé la table produits :

CREATE TABLE [dbo].[produits](
    [id] [int] NOT NULL,
    [libelle] [varchar](250) NULL,
    [prix] [decimal](9, 2) NULL,
    [categoryId] [int] NULL,
    [ordreAffichage] [int] NULL,
    [description] [varchar](500) NULL,
CONSTRAINT [PK_produits] PRIMARY KEY CLUSTERED([id] ASC))

J’ai ensuite chargé 110 000 lignes de données dans cette table répartie en 3 catégories. J’ai généré les données dans Excel en incrémentant des id et des libellés.

Données de la pagination

ALL in the DOM

Une des techniques de pagination rencontrée fréquemment sur de petits sites est la technique que j’appelle « ALL in the DOM ». Elle consiste à charger tous les produits de la catégorie dans la page et de gérer la pagination avec JavaScript. La rapidité d’intégration de cette solution est sans doute un avantage mais les performances peuvent très vite se montrer désastreuses ! En effet, si on doit surcharger la page de plusieurs centaines d’articles qui ne seront sans doute jamais consultés, l’affichage pourrait être extrêmement long. Et c’est sans compter le requêtage de la table qui devra lire beaucoup de pages (et d’IO) ainsi que la bande passante du server SQL qui devra transférer les données à l’application. Bref, cette solution est à réserver exclusivement pour des projets amateurs à faible volumétrie.

La requête exécutée sur le serveur ressemblerait à ceci :

DECLARE @CATEGORY int = 3

select id
, libelle
, prix
, categoryId
, description
from produits
where categoryId = @CATEGORY
order by ordreAffichage

Table temporaire

Une autre solution que j’ai rencontré, mais que je ne recommande pas, est d’utiliser des TOP avec une table temporaire. Une première étape consiste à créer un table temporaire qui contient toutes les lignes jusqu’à la fin de la pagination : par exemple, avec 20 produits par page pour afficher la 5ème page, la table temporaire contiendra les 100 premiers produits. Dans la 2ème étape, il suffit ensuite de sélectionner avec un top 20 les produits de la page.

DECLARE @DEBUT int = 500
DECLARE @LONGUEUR int = 20
DECLARE @CATEGORY int = 3

select top (@DEBUT + @LONGUEUR) id
, libelle
, prix
, categoryId
, description
, ordreAffichage
into #t
from produits
where categoryId = @CATEGORY
order by ordreAffichage

select top (@LONGUEUR) *
from #t
order by ordreAffichage desc

drop table #t

Avec ce système, plus l’utilisateur paginera et plus les performances se dégraderont. Avec de nombreux utilisateurs, la TEMPDB pourrait grossir très vite.

CTE et Fenêtrage

Depuis la version 2005, on peut utiliser les CTE pour les requêtes récursives et avec la fonction de fenêtrage ROWNUMBER qui permet d’attribuer un rang à une ligne, nous sommes capable de ne récupérer que les lignes qui nous intéresse.

DECLARE @DEBUT int = 500
DECLARE @LONGUEUR int = 20
DECLARE @CATEGORY int = 3

;WITH cte AS
(
select id
, libelle
, prix
, categoryId
, description
, ROW_NUMBER() OVER (ORDER BY ordreAffichage) as rang
from produits
where categoryId = @CATEGORY
)
select id
, libelle
, prix
, categoryId
, description
FROM cte
WHERE rang > @DEBUT AND rang <= @DEBUT + @LONGUEUR

Je pense que cette solution (ou une adaptation de celle-ci) est bonne pour les SQL Server < 2012.

OFFSET et FETCH

Avec SQL Server 2012 et 2014, OFFSET et FETCH ont fait leur apparition. Cette méthode se rapproche le plus du LIMIT connu sur MySQL.

DECLARE @DEBUT int = 80000
DECLARE @LONGUEUR int = 20
DECLARE @CATEGORY int = 3

select id
, libelle
, prix
, categoryId
, description
from produits
where categoryId = @CATEGORY
order by ordreAffichage
offset @DEBUT rows
fetch next @LONGUEUR rows only

Au niveau performance, elle est quasiment identique aux CTE + ROWNUMBER mais elle est bien plus lisible.

TVP et Cache Client

Cette méthode est plus complexe à mettre en oeuvre. En effet, il est nécessaire d’utiliser les TVP dans SQL Server et gérer un cache au niveau de l’application.
Pour utiliser une TVP, il est nécessaire de créer un type TABLE au niveau de la base de données comme ceci :

CREATE TYPE dbo.TypePagination AS TABLE
(
id int
)

Lors du premier affichage d’une catégorie, il convient de le récupérer les ID de tous les produits de la catégorie et de les stocker dans le cache de l’application.

DECLARE @CATEGORY int = 3

select id
from produits
where categoryId = @CATEGORY
order by ordreAffichage

Lors de la pagination par l’utilisateur, il convient ensuite de récupérer les 20 ID suivant :

select id
, libelle
, prix
, categoryId
, description
from produits p
where exists (select * from @TypePagination t where t.id = p.id)
order by ordreAffichage

Cette méthode est de loin la plus performante si l’indexation de la table produits est réalisée correctement. L’inconvénient est la difficulté (relative) à sa mise en place, la consommation de ressources supérieure au niveau du serveur applicatif et la lenteur du premier affichage de la liste de produit (mais on peut compenser ceci par une procédure de chargement de cache lors du reboot du serveur applicatif).

Comparatif

Afin de tester les performances, j’ai exécuté 10 fois chacune des requêtes et j’ai calculé des moyennes sur le CPU Time, l’Elapsed Time et le Logical Reads. J’ai fait ce test pour la 1ère et la 50ème page de la liste de produit.

Comparatif Performance Pagination

La méthode avec la table temporaire est de moins en moins efficace en avançant dans la pagination. De plus elle a beaucoup d’inconvénients à cause de la création de tables temporaires.

La méthode All in the DOM est catastrophique ! Surtout que dans cet exercice, je ne tiens compte que de la consommation au niveau de la base de données. Il reste ensuite à la page Web de gérer la pagination.

La technique Offset + Fetch semble être un bon compromis entre performance et facilité de développement et reste stable au fur et à mesure de l’avancée dans la pagination. Cependant la méthode utilisant CTE + fonction de fenêtrage est très proche.

La dernière méthode utilisant la TVP est la plus performante au niveau base de données. Mais comme la performance doit se mesurer dans son ensemble avec la chaine complète et qu’une partie du travail est réalisé coté applicatif, il faut tester en fonction de vos besoins.

6 thoughts on “Pagination

  1. Bonjour,

    Pour les approches CTE et OFFSET notamment, il faut considérer une première exécution de la requête, sans fenêtrage, afin d’obtenir le « COUNT » total d’items, comptage nécessaire pour proposer dans l’IHM des n° de pages, et pas juste une navigation page précédente/page suivante…
    Peut-on optimiser cela d’une façon ou d’une autre ?

    Merci,
    Laurent

  2. Bonjour Laurent,
    Effectivement, pour connaitre le nombre total, il faut d’abord réaliser un comptage des lignes avec un count(*) et le conserver en mémoire
    Tu peux aussi transformer la requête OFFSET comme ceci mais je pense qu’en terme de performance, c’est catastrophique 🙂
    select id
    , libelle
    , prix
    , categoryId
    , description
    , count(*) over() as nbtot
    from produits
    where categoryId = @CATEGORY
    order by ordreAffichage
    offset @DEBUT rows
    fetch next @LONGUEUR rows only

  3. et pour la cte, tu peux faire à peu prêt pareil (mais je pense sans avoir testé que c’est une solution trop gourmande) :

    DECLARE @DEBUT int = 500
    DECLARE @LONGUEUR int = 20
    DECLARE @CATEGORY int = 3

    ;WITH cte AS
    (
    select id
    , libelle
    , prix
    , categoryId
    , description
    , ROW_NUMBER() OVER (ORDER BY ordreAffichage) as rang
    from produits
    where categoryId = @CATEGORY
    ), ctetot as
    (
    select count(*) as tot
    from produits
    where categoryId = @CATEGORY
    )
    select id
    , libelle
    , prix
    , categoryId
    , description
    , ctetot.tot
    FROM cte
    cross join ctetot
    WHERE rang > @DEBUT AND rang <= @DEBUT + @LONGUEUR

  4. Salut Greg, et bonne année.

    Bon article mais pour travailler dans l’ebusiness c’est un peu simpliste: comment gère tu les différents tri possible.

    Par exemple quand l’internaute clic sur « trier par prix » ou autre?

    • Bonjour Bruno,
      Meilleurs voeux également.

      Je dirais que ca dépend 😀
      Ca va dépendre du modèle de données et de ce que tu es prêt à mettre en cache coté applicatif.
      Je suis d’accord avec toi, dans cet article je suis resté trop loin d’un vrai cas pratique…

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Time limit is exhausted. Please reload CAPTCHA.