Bonne nouvelle pour les développeurs ! Enfin, SQL Server 2012 introduit l'instruction de pagination ORDER BY OFFSET au niveau de la requête SQL. Est-ce que cela veut dire que les anciennes méthodes ne sont plus valables ? Faisons-nous les tests pour y répondre...

Scénario de test

L'archive des scripts SQL de test comprit un fichier par une étape :

1. Création BDD
2. Remplissage des données de test
3. Création des procédures stockées implémentées les méthodes de pagination
4. Démarrage des tests et enregistrement des résultats
5. Visualisation des résultats

Par rapport des tests sur SQL Server 2005, j'ai réduit légèrement le scénario et automatisé les tirs. Les tables à interroger sont toujours "customers" (rempli par 10 000 lignes) et "sales" (10 000 000 lignes).

Après avoir inséré les données, on voit la répartition égale des ventes par pays.

Dans notre scénario, l'application reçoit les ventes d'un seul pays par les pages de 100 lignes à partir d'une ligne donné. Cette requête est implémenté comme une vue (view) "test_sales_data".

Les méthodes

Les méthodes sont implémentés comme les procédures stockées similaires "test_paging_mN" (dont N est le numéro de méthode) :

CREATE PROCEDURE dbo.test_paging_mN
  @offset int,
  @page_size int
AS
BEGIN
  ...
  implémentation de pagination (voir le code ci-dessous)
  ...
END

Méthode 1 : l'instruction ORDER BY OFFSET (SQL Server 2012 uniquement)

  SELECT * FROM dbo.test_sales_data
  ORDER BY id_product, id_customer, sale_date
  OFFSET @offset - 1 ROW FETCH NEXT @page_size ROWS ONLY

Méthode 2 : la fonction row_number()

  WITH ordered_sales AS (
    SELECT 
      *,
      row_number() OVER( ORDER BY id_product, id_customer, sale_date) AS row_num
    FROM dbo.test_sales_data
  )
  SELECT *
    FROM ordered_sales
    WHERE row_num BETWEEN @offset AND @offset + @page_size - 1;

Méthode 3 : la table temporaire

  SELECT * INTO #s FROM dbo.test_sales_data WHERE 1 = 0;
  ALTER TABLE #s ADD row_num INT NOT NULL IDENTITY(1, 1) PRIMARY KEY;
 
  INSERT INTO #s
  SELECT TOP (@offset + @page_size - 1) * FROM dbo.test_sales_data
  ORDER BY id_product, id_customer, sale_date;
 
  SELECT * FROM #s
  WHERE row_num BETWEEN @offset and @offset + @page_size - 1;

Méthode 4 : l'instruction SELECT TOP

  SELECT *
  FROM
  (
    SELECT TOP (@page_size) *
    FROM
      (SELECT TOP (@offset + @page_size - 1) *
         FROM dbo.test_sales_data
         ORDER BY id_product ASC, id_customer ASC, sale_date ASC
      ) t1
    ORDER BY id_product DESC, id_customer DESC, sale_date DESC
  ) t2
  ORDER BY id_product, id_customer, sale_date

Méthode 5 : le curseur de serveur

La référence des procédures stockées de curseur (MSDN).

  DECLARE @handle int, @rows int;
  EXEC sp_cursoropen
    @handle OUT,
    'SELECT * FROM dbo.test_sales_data ORDER BY id_product, id_customer, sale_date',
    1, -- 0x0001 - Keyset-driven cursor
    1, -- Read-only
    @rows OUT; -- Contains total rows count
 
  EXEC sp_cursorfetch
    @handle,
    16,        -- Absolute row index
    @offset,   -- Fetch from row
    @page_size -- Rows count to fetch
 
  EXEC sp_cursorclose @handle;

Résultats

Pour ne pas charger Management Studio par les dizaines des tables retournées, je vous recommande démarrer les test dans l'invite de commande.

set SQL_BIN_HOME=C:\Program Files\Microsoft SQL Server\110\Tools\Binn
"%SQL_BIN_HOME%\sqlcmd.exe" -S .\SQL2012 -d test_paging -E -i SQLServerPaging2_04_Test.sql -o Test.log

En fonction de la puissance de votre ordinateur, les tests peuvent durer quelques dizaines des minutes. Une fois les tests finissent, lancez le script "SQLServerPaging2_05_Results.sql" qui retourne 2 tables:

• les résultats des requêtes "froides" (on nettoie le cache avant)
• les résultats moyennes sur le 3 tirs des requêtes "chaudes" (la requête et les données sont dans le cache)

Voici les résultats sur mon PC (Intel 2 couers 2,4GHz, RAM 6 Go sous Win 7 Pro 64 bits) relativement faible qui ne possède qu'une seul disque dur pas trop rapide .

Table 1. La requête "froide", milliseconds

offset 1 2 3 4 5 1 6690 6693 6763 6906 35033 100 7260 6790 7190 7083 34736 1000 9230 8996 8570 8663 34653 10000 9330 8916 8570 7200 35433 100000 14946 15126 16156 15180 35103 200000 21120 20216 22863 21126 35736 300000 25223 23210 26063 24970 35473 400000 29923 25690 31336 29846 34933 500000 29326 28240 31526 30300 34750

Table 2. Les requêtes "chaudes", le temps moyens sur 3 tirs, millisecondes

offset 1 2 3 4 5 1 7 9 23 13 6669 100 16 12 24 16 6617 1000 33 34 48 37 7048 10000 140 210 243 131 6662 100000 1037 2084 1711 1182 6632 200000 3291 4206 4477 3218 6678 300000 4754 6340 5997 4923 6640 400000 1634 8407 2981 2006 6612 500000 1642 10641 3232 2196 6717

Conclusions

La performance de l'instruction ORDER BY OFFSET montre le résultat attendu. Tous les développeurs doivent prendre en considération cette solution comme le pattern.

D'autre part, la méthode "curseur de serveur" obtient toujours le temps stable non relative de la taille de données retournées. Cette option est utile lorsqu'on traite des grosses volumes par lots. I.e. faire les calculs sur une table des plusieurs centaines millions et milliards de lignes par les lots des 100 milles.

Notamment pour les développeurs web je recommande ne pas utiliser les requêtes retournées des 1,5M lignes comme dans ce test. Il est nécessaire restreindre la requête puisque la situation normale est renvoyer quelques dizaines ou centaines lignes au plus aux utilisateurs. Limitez le résultat par le TOP NNN et imposez les utilisateurs d'affiner les recherches. Comme cela, dans la situation "moyenne" vous n'aurez pas besoin avoir le serveur super-puissant ainsi que faire la répartition des charges sur plusieurs serveurs SGBD.