Pagination d’un jeu de données sous SQL Server

La consulting et la formation ont comme avantage d’obliger le formateur (ou le consultant SQL Server) a toujours se remettre en question. On est confronté à nombre de questions auxquelles on n’a pas encore réfléchi (ou il y a des années et depuis la techno a changé et l’on n’est pas revenu sur le sujet …).

C’est ainsi que l’on m’a demandé quel était le moyen le plus simple (efficace / performant) de paginer le jeu résultat d’une requête SQL retournant plusieurs centaines, voire milliers d’enregistrements.

Bien sûr on peut très rapidement penser à des solutions utilisant des curseurs, de stockage de valeurs côté client ou dans la tempsDB sur le serveur SQL, etc …

Et puis, en présentant la clause OVER dans SQL Server 2008, on se dit qu’il y a une solution très simple …

La table Sales.SalesOrderDetail de la base AdventureWorks contient plus de 120 000 lignes. Parfait pour une petite démo.

Considérons une requête simple, mais dont nous voulons paginer le résultat.

SELECT *
FROM Sales.SalesOrderDetail;

Pour afficher une tranche d’enregistrements, du 70 000 au 70 100 par exemple, voici une solution :

Commençons par afficher le numéro de ligne dans le jeu de résultat grâce à la clause OVER :

SELECT ROW_NUMBER() OVER (ORDER BY ModifiedDate) as NumLigne,*
FROM Sales.SalesOrderDetail;

image

Resterait donc a prendre une tranche du résultat en mettant une clause WHERE sur la colonne ROW_NUMBER()

image

Et zut, ce n’est pas possible …. Même si l’on peut utiliser un alias de colonne dans une clause ORDER BY, il n’est pas possible de l’utiliser dans la clause WHERE.

Solution de repli, utilisons la commande ROW_NUMBER() OVER (…) dans la condition WHERE :

image

Et re-zut. Dommage. Il serait intéressant que cette fonctionnalité soit inclue dans les prochaines versions de SQL Server…

Bon, ben reste donc à faire une CTE ou bien une requête du style :

SELECT [SalesOrderID]
      ,[SalesOrderDetailID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,[LineTotal]
      ,[rowguid]
      ,[ModifiedDate]
FROM
    (
    SELECT ROW_NUMBER() OVER (ORDER BY ModifiedDate) as NumLigne,*
    FROM   Sales.SalesOrderDetail
    ) as sod
WHERE sod.NumLigne BETWEEN 70000 and 70100

 image

 

Cette fois ci, tout est OK, cela fonctionne parfaitement et je récupère bien une tranche de 100 enregistrements.

Les temps de compilation et d’exécution restent tout à fait convenables (le DBCC FREEPROCCACHE a été exécuté juste avant):

image

Le plan d’exécution pourrait bien sur être optimisé :

image

Mais comme nous n’avons introduit aucune restriction sur les données sources, le Cluster Index Scan est justifié.

Finalement, le point le plus important de cette requête est la colonne que l’on va mettre au niveau de la clause ORDER BY du OVER… C’est ce champs qui va déterminer l’ordre des enregistrements et donc ceux qui seront présents dans la tranche de données.

image

Bon codage …

A propos Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM SQL Server 2008
Cet article a été publié dans SQL Server. Ajoutez ce permalien à vos favoris.

4 commentaires pour Pagination d’un jeu de données sous SQL Server

  1. mikado dit :

    Un bon article complémentaire qui fait une comparaison des performances selon la solution choisi (curseur,over, order inversé…)
    http://sgbd.arbinada.com/node/20

    La solution row_number ne semble pas être la plus performante😦

    • Christophe dit :

      Bonjour

      Intéressant. Je vais mener quelques tests pour valider ces chiffres car un peu étonné.
      Il faudrait également parler verrouillage (curseur) et pagelatch (PFS sur al TempDB) pour être complet car le test est réalisé de manière unitaire et non soumis à concurrence d’accès.

      Par contre, je trouve dommage de n’avoir pas utilisé SQL Server 2008R2 lors du test en 2010 … Avec SQL 2005 on partait déjà avec quelques longueurs de retard.

      • mikado dit :

        C’est en effet intéressant d’avoir non seulement une idée des solutions disponibles avec leurs limites afin de choisir la meilleur solution

        Pour les tests avec 2008, il n’avait peut être pas acheté cette licence😉

  2. Nico dit :

    Requête très intéressante. Je cherchais justement une solution à adopter pour trouver un équivalent au LIMIT utilisable via mysql.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s