SQL Server Denali – Pagination d’un jeu de données

Il y a quelques temps, j’avais publié un billet traitant de la pagination d’un jeu de données sur SQL Server 2008.

Que nous propose SQL 11 sur ce point. Existe t’il, parmi les nouveautés “développement” de Denali, une nouvelle méthode permettant de traiter de manière différente la problématique de la pagination de données sur SQL Server.

Et bien oui. l’apparition d’une clause OFFSET permet en effet de gérer la pagination d’un jeu de données.

Dans mon post précédent, je tirais parti de la clause OVER. Comparons les performances et les plans d’exécution entre la clause OVER et la nouvelle clause OFFSET.

ORDER BY order_by_expression
    [ COLLATE collation_name ] 
    [ ASC | DESC ] 
    [ ,...n ] 
[ <offset_fetch> ]


<offset_fetch> ::=
{ 
    OFFSET { integer_constant | offset_row_count_expression } { ROW | ROWS }
    [
      FETCH { FIRST | NEXT } {integer_constant | fetch_row_count_expression } { ROW | ROWS } ONLY
    ]
}

 

Première chose, on constate que la clause OFFSET parait liée au ORDER BY. En effet, si on ne précise pas de condition de tri sur la requête, une erreur est renvoyée. Pour une description complète de la clause, je vous suggère cette page sur MSDN.

Vidons les caches de SQL Server :

DBCC DROPCLEANBUFFERS
DBCC FREESYSTEMCACHE('ALL')
DBCC FREESESSIONCACHE
DBCC FREEPROCCACHE

Reprenons notre exemple basé sur la clause OVER :

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 70001 and 70100

 

image

image

Faisons maintenant une requête équivalente (en jeu de donnée retourné) avec la clause OFFSET.

DECLARE    @PageSize      TINYINT = 100,    
                       @CurrentPage INT         = 701;

SELECT [SalesOrderID]
      ,[SalesOrderDetailID]
      ,[CarrierTrackingNumber]
      ,[OrderQty]
      ,[ProductID]
      ,[SpecialOfferID]
      ,[UnitPrice]
      ,[UnitPriceDiscount]
      ,[LineTotal]
      ,[rowguid]
      ,[ModifiedDate] 
FROM Sales.SalesOrderDetail     
ORDER BY ModifiedDate    
OFFSET (@PageSize * (@CurrentPage - 1)) ROWS    
FETCH NEXT @PageSize 
ROWS ONLY;

image

image

Les temps de compilation et d’exécution sont relativement proches. La version avec OFFSET semble plus rapide en exécution mais plus longue à compiler. La version a base de clause OVER présente un plan d’exécution plus complexe (opérateur SEGMENT afin e créer une partition pour les données), ce qui doit justifier le temps d’exécution plus élevé, même si dans les deux cas, le tri des données représente 90% du cout total.

Cette fonctionnalité proposée dans d’autres SGBD était réellement attendue. Pour un premier jet, cela a l’air satisfaisant en terme de performances.

Néanmoins, cela mérite un test plus approfondi avec surtout un jeu de donnée bien plus conséquent (plusieurs dizaines de millions d’enregistrements …). J’ai testé sur une table ayant 1 million d’enregistrements, les résultats sont similaires. Temps de compilation très proche, temps d’exécution aussi, plans d’exécution toujours différents.

Bon windowing …

A propos Christophe

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

Un commentaire pour SQL Server Denali – Pagination d’un jeu de données

  1. Ping : SQL Server Denali CTP3 – Nouveautés T-SQL | Christophe LAPORTE – Consultant SQL Server

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