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
- Regardez d'abord le plan d'exécution réel : un index manquant et un mauvais style de pagination se ressemblent côté application (« c'est lent »).
- Le résultat doit être identique. Je compare ancienne et nouvelle requête avec
EXCEPTdans les deux sens sur un échantillon de pages avant tout déploiement. - Lire de grosses colonnes (
varchar(max)) pour chaque ligne d'un large scan multiplie le coût, et sous le niveau d'isolation par défaut ces lignes restent verrouillées jusqu'à la fin de l'instruction. Sélectionnez d'abord les clés, puis joignez pour ne récupérer les colonnes lourdes que pour les 50 lignes. - Un nouvel index coûte des écritures et du log. Vérifiez qu'il n'est pas quasi-doublon d'un index existant.
- Gardez quand même une limite de profondeur sur
OFFSET: elle protège le pool contre un utilisateur qui clique sur « dernière page ».