Pagination SQL Server rapide même dans un historique profond

Un client consulte son historique de commandes. La première page arrive immédiatement, mais la page 8 000 demande plusieurs secondes. Le serveur renvoie toujours 25 lignes. Ajouter des serveurs applicatifs ne traite pas forcément le problème: OFFSET demande à SQL Server de parcourir les entrées précédentes avant de restituer la portion recherchée.

Un index ordonné évite parfois de trier toute la table, mais les entrées ignorées représentent encore du travail. Comparez les lectures logiques d'une page initiale et d'une page éloignée avec exactement les mêmes filtres. Si elles augmentent avec le décalage, le contrat de pagination mérite une révision. Une requête lente dès la première page, à cause d'un tri non indexé, demande une analyse différente.

Définir une frontière sans ambiguïté

La pagination par curseur transmet les dernières valeurs de tri de la réponse précédente. Pour des commandes classées de la plus récente à la plus ancienne, la requête suivante cherche des dates antérieures, ainsi que des identifiants inférieurs lorsque les dates sont identiques. Cette seconde condition est indispensable. Un horodatage seul n'est généralement pas unique et peut entraîner l'omission de commandes.

L'exemple utilise uniquement une table temporaire. La première requête renvoie 105 et 104; la suivante doit renvoyer 103 et 102. Cela permet d'examiner la frontière avant d'appliquer la méthode à une grande table.

CREATE TABLE #Orders
(
    OrderId bigint NOT NULL PRIMARY KEY,
    CreatedAt datetime2(3) NOT NULL,
    Amount decimal(12,2) NOT NULL
);
INSERT #Orders VALUES
(105,'2022-01-10T09:00:00',25),
(104,'2022-01-10T09:00:00',40),
(103,'2022-01-09T12:00:00',15),
(102,'2022-01-08T08:00:00',80),
(101,'2022-01-07T08:00:00',30);

CREATE INDEX IX_Orders_Page
ON #Orders(CreatedAt DESC, OrderId DESC)
INCLUDE(Amount);

SELECT TOP (2) OrderId, CreatedAt, Amount
FROM #Orders
ORDER BY CreatedAt DESC, OrderId DESC;

DECLARE @LastTime datetime2(3) = '2022-01-10T09:00:00';
DECLARE @LastId bigint = 104;

SELECT TOP (2) OrderId, CreatedAt, Amount
FROM #Orders
WHERE CreatedAt < @LastTime
   OR (CreatedAt = @LastTime AND OrderId < @LastId)
ORDER BY CreatedAt DESC, OrderId DESC;

DROP TABLE #Orders;

L'index place les colonnes de navigation dans la clé et inclut Amount pour servir la réponse. Dans une application multiclient, une clé TenantId, CreatedAt, OrderId constitue souvent un point de départ pertinent si TenantId est fixé. Un écran qui mélange tous les clients suit un autre modèle d'accès. Il ne faut pas présumer qu'un seul index convient aux deux usages.

Donner un sens précis au curseur

Conservez la précision exacte de datetime2 et l'identifiant dans le curseur. Un objet date du client qui arrondit l'horodatage peut déplacer la frontière. Associez aussi client, filtres, sens de tri et version du format. Un curseur construit pour les commandes impayées ne doit pas être réutilisé silencieusement pour toutes les commandes. Une signature détecte les modifications, mais les autorisations doivent toujours être vérifiées sur la requête.

Pour la première page, préférez une requête sans frontière. Une condition facultative du type «curseur absent OU ...» peut créer un plan réutilisable moins sélectif. Comparez les plans réels. Le OR de la comparaison lexicographique peut lui-même rester partiellement un filtre résiduel. Vérifiez les lignes réellement lues, sans supposer que chaque formulation produit une recherche parfaite dans l'index.

Lisez une ligne supplémentaire pour savoir s'il existe une suite, puis renvoyez seulement le nombre demandé. Le curseur suivant doit provenir de la dernière ligne effectivement remise au client. S'il utilise la ligne supplémentaire avec une comparaison strictement inférieure, celle-ci sera perdue. Un COUNT exact de l'ensemble peut coûter davantage que la page; beaucoup d'historiques n'en ont pas besoin à chaque appel.

Prévoir les modifications concurrentes

Les nouvelles commandes placées avant la frontière ne décalent normalement pas la page suivante. En revanche, modifier une valeur de tri peut déplacer une ligne à travers cette frontière et provoquer doublons ou omissions. Choisissez si possible des colonnes immuables. Pour un export d'audit figé, des lectures indépendantes ne suffisent pas: envisagez une transaction snapshot de durée maîtrisée, un jeu matérialisé ou une frontière d'export immuable.

Testez des horodatages identiques, la suppression de la ligne frontière, une dernière page vide et des clients de volumes très différents. La suppression de la ligne frontière est sans conséquence si le curseur contient ses anciennes valeurs; la requête n'a pas à retrouver cette ligne. Pour revenir en arrière, inversez comparaison et tri, prenez une quantité limitée, puis rétablissez l'ordre d'affichage.

Cette méthode privilégie une navigation séquentielle prévisible. Les sauts arbitraires vers une page numérotée peuvent encore justifier OFFSET ou des repères précalculés. Choisissez selon l'interaction réelle, puis démontrez que les lectures restent contenues sur des pages éloignées. La réussite combine résultats complets, ordre correct et consommation stable, plutôt qu'une simple accélération du premier écran.

Références techniques: Microsoft Learn: ORDER BY · Microsoft Learn: Pagination.