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 :
- Création BDD
- Remplissage des données de test
- Création des procédures stockées implémentées les méthodes de pagination
- Démarrage des tests et enregistrement des résultats
- 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
Table 2. Les requêtes "chaudes", le temps moyens sur 3 tirs, millisecondes
|
![]() |
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.