LLI Wiki — My Digital Knowledge Base

Requêtes de liste paginées : OFFSET/FETCH ou pagination keyset

Réécrire une requête de liste lente sur Azure SQL Database : keyset, index de support, mesure avec SET STATISTICS IO et Query Store.

Un écran de « liste » est souvent la requête la plus exécutée d'une application, et rarement la plus soignée : un SELECT avec quelques jointures, un ORDER BY et un numéro de page. Ça passe avec 10 000 lignes et ça fait mal avec 10 millions. Voici comment je réécris ce type de requête sur Azure SQL Database : mesurer d'abord, choisir entre OFFSET/FETCH et la pagination par clé (keyset, ou seek), puis donner à la requête l'index dont elle a besoin.

Le problème d'OFFSET/FETCH

SELECT o.OrderId, o.OrderDate, o.Total, c.Name
FROM dbo.Orders AS o
JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId
WHERE o.Status = 'Open'
ORDER BY o.OrderDate DESC, o.OrderId DESC
OFFSET 100000 ROWS FETCH NEXT 50 ROWS ONLY;

OFFSET ne « saute » rien : le moteur produit et jette les 100 000 premières lignes, puis en renvoie 50. Le coût croît linéairement avec le numéro de page : la page 1 est rapide, la page 2 000 est lente. Ces lignes sont lues (et verrouillées ou versionnées) pour rien. Autre effet de bord : si des lignes sont insérées entre deux requêtes, l'utilisateur voit des doublons ou rate des lignes.

La pagination par clé (keyset)

Au lieu de « sauter N lignes », le client renvoie la clé de tri de la dernière ligne reçue et la requête fait un seek juste après :

DECLARE @LastDate datetime2 = '2026-03-01T10:15:00', @LastId int = 48213;

SELECT TOP (50) o.OrderId, o.OrderDate, o.Total, c.Name
FROM dbo.Orders AS o
JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId
WHERE o.Status = 'Open'
  AND (o.OrderDate < @LastDate
       OR (o.OrderDate = @LastDate AND o.OrderId < @LastId))
ORDER BY o.OrderDate DESC, o.OrderId DESC;

La clé de tri doit être unique, d'où le départage par OrderId. Le coût est désormais identique pour toutes les pages.

OFFSET/FETCH Keyset
Coût de la page N croît avec N constant
Aller à la page 500 oui non (suivant/précédent)
Stable en cas d'insertions non oui
Nombre total de lignes requête séparée requête séparée
Complexité triviale clé unique, le client garde un curseur

Si les utilisateurs ont vraiment besoin d'« aller à la page 500 », je garde OFFSET mais je limite la profondeur (personne ne lit la page 2 000 : proposez une recherche ou un filtre).

L'index de support

Le keyset n'est rentable que si l'index correspond au filtre et au tri :

CREATE INDEX IX_Orders_Status_Date_Id
ON dbo.Orders (Status, OrderDate DESC, OrderId DESC)
INCLUDE (CustomerId, Total);

La colonne d'égalité d'abord (Status), puis les colonnes de tri dans le même sens que l'ORDER BY, puis en INCLUDE les colonnes utiles seulement à l'affichage. Avec cet index, le plan est un seek suivi de la lecture de 50 lignes, sans opérateur de tri. Si le plan contient encore un Sort ou un Key Lookup sur des milliers de lignes, l'index ne correspond pas.

Mesurer avant et après

Ne jamais se fier au ressenti. Deux outils :

SET STATISTICS IO, TIME ON;
-- exécuter l'ancienne requête, noter « logical reads » et « CPU time »
-- exécuter la nouvelle, comparer

Les lectures logiques sont la métrique à laquelle je me fie le plus : elle ne dépend pas du cache. J'exécute chaque variante deux fois et je regarde la seconde. Pour la charge réelle, Query Store garde l'historique par requête : durée moyenne, lectures logiques et nombre d'exécutions avant et après le déploiement (sys.query_store_runtime_stats). On compare des intervalles, pas des exécutions isolées, car la liste est appelée avec beaucoup de valeurs de paramètres.

flowchart LR
  A["Requête de liste lente"] --> B["SET STATISTICS IO : référence"]
  B --> C["Réécriture keyset"]
  C --> D["Index de support"]
  D --> E["SET STATISTICS IO : comparaison"]
  E --> F["Déploiement"]
  F --> G["Query Store : vérifier les régressions"]

Retour d'expérience

Mastodon LLIMastodon SocialFosstodon