Pagination dans SQL Server 2012

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.

country_code sales_count
ES 1424420
FR 1434608
GE 1391128
IT 1443384
NL 1414063
RU 1441266
UK 1451131

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.