Pagination avec des examples sous SQL Server

Voir aussi l'article "Pagination dans SQL Server 2012"

En fait, MS SQL Server n'a pas des contraintes au niveau d'instruction SELECT pour limiter l'ensemble de données retourné par les numéros des lignes. Par exemple, récupérer un bloc de commandes d'un client trié par leur dates à partir de 10 000 et jusqu'au 12 000.

SELECT O.*
  FROM orders O INNER JOIN customers C
    ON O.customer_code = C.customer_code
  ORDER BY O.qty_date ASC
  LIMIT 10000, 12000

Les fonctions de classements introduites dans la version MS SQL 2005 et notamment la fonction row_number() ont fait la vie quotidien du développeur plus facile. Mais cette solution reste palliative plutôt puisque l'instruction LIMIT se traite au niveau du moteur de la base de données ainsi que les fonctions de classement se traitent au niveau utilisateur. Ensuite, la performance de LIMIT est supérieur. La différence devient plus significative si la taille de vos tables et de blocs récupérés est assez grand (centaines milles et millions de lignes).

Dans cette article je vous présente les différentes méthodes de pagination (paging, sélection par bloc). Pour les tests j'ai utilisé MS SQL Server 2005 Service Pack 2 (9.00.3054.00)installé sur l'ordinateur pas trop puissant : Intel double coeur 1,8 GHz, 2 Go de mémoire vive (512 Mo est disponible pour SQL Server), disque dur 250 Go 7200 rpm. La taille de la base de données est 5 Go environ.

Scénario de test

Il faut extraire toutes les commandes de clients italiens (code pays = "IT") par les blocs de 100 000 lignes pour le traitement suivant. Par exemple, le bloc de 400 001 à 500 000 lignes est le quatrième dans la série. La table contient 4 300 000 lignes environ y compris les 800 000 qui sont correspondants à nos critères de sélection. Cette volumétrie n'est pas vraiment grosse mais elle est capable déjà bien charger notre ordinateur.

L'objectif de test est mesurer les temps d'extractions procédés par les différentes méthodes. Chaque méthode est testé en 4 séries :

  • Série 1: les tables ont la clé composite de type nvarchar, on redémarre SQL Server au début de la série uniquement
  • Série 2: les tables ont la clé composite de type nvarchar, on redémarre SQL Server au début de chaque test de la série
  • Série 3: les tables ont la clé simple de type int, on redémarre SQL Server au début de la série uniquement
  • Série 4: les tables ont la clé simple de type int, on redémarre SQL Server au début de chaque test de la série

La redémarrage de SQL Server sert à exclure les cohérence des tests précédents. Également, il est possible d'utiliser les instructions de nottoyage DBCC comme DROPCLEANBUFFERS ou FREEPROCCACHE. Après la redémarrage on exécute la requête suivante pour charger partiellement nos données dans la cache et rapprocher l'environnement réel de production :

SELECT count(*)
  FROM orders O INNER JOIN customers C 
       ON O.customer_code = C.customer_code
  WHERE C.country_code = 'IT' 

Les tables

La table de commandes a la clé composite ainsi que la clé simple de type entier (int) qui a été rajouté pour les tests.

CREATE TABLE dbo.customers (
  customer_code  nvarchar(15)  NOT NULL,
  country_code   nchar(2)      NOT NULL,
  name           nvarchar(255) NOT NULL,
  street_address nvarchar(100) NULL,
  city           nvarchar(40)  NULL,
  postal_code    nvarchar(15)  NULL,
  CONSTRAINT PK_CUSTOMERS 
    PRIMARY KEY NONCLUSTERED (customer_code ASC)
)
GO
CREATE INDEX IX1_COUNTRY_CODE ON dbo.customers (country_code ASC)
GO 
CREATE TABLE dbo.orders (
  product_code  nvarchar(18) NOT NULL,
  customer_code nvarchar(15) NOT NULL,
  order_type    nvarchar(4)  NOT NULL,
  qty_date      datetime     NOT NULL,
  qty           int          NOT NULL,
  order_id      int          NOT NULL,
  CONSTRAINT PK_ORDERS PRIMARY KEY NONCLUSTERED(order_id ASC),
  CONSTRAINT FK1_ORDERS_CUSTOMERS FOREIGN KEY(customer_code)
    REFERENCES dbo.customers (customer_code)
)
GO
CREATE UNIQUE INDEX AK1_ORDERS ON orders(
  product_code ASC,
  customer_code ASC,
  order_type ASC,
  qty_date ASC)
GO 

Solutions

Il nous faut définir 2 paramètres d'entrée pour chaque méthode :

  • @offset - le numéro de ligne initiale dans la table
  • @batch_size - la taille du bloc

Par exemple, avant chaque test :

DECLARE @offset int, @batch_size int;
SELECT @offset = 400001, @batch_size = 100000;

La méthode classique : ANSI SQL

Je pense qu'il n'y a qu'une seul avantage de cette méthode : la compatibilité à chaque SGBD au niveau SQL. La méthode est basée sur la jointure réflexive (self join) ce qu'est très gênant en cas de millions de lignes. Par contre, pour les tables de dizaines milles de lignes vous pouvez l'utiliser.

Puisque je n'avais pas reçu la fin de requête pendant 15 minutes sur les données de test, je l'ai exclu de résultats et ne montre que son code.

SELECT O.*
  FROM orders O
       INNER JOIN customers C ON O.customer_code = C.customer_code
  WHERE C.country_code = 'IT' AND
       (SELECT count(*) 
          FROM orders O1
               INNER JOIN customers C1 ON O1.customer_code = C1.customer_code
          WHERE C1.country_code = 'IT' AND
                O1.product_code <= O.product_code AND
                O1.customer_code <= O.customer_code AND
                O1.order_type <= O.order_type AND
                O1.qty_date <= O.qty_date
       ) BETWEEN @offset AND @offset + @batch_size - 1
  ORDER BY O.product_code ASC, O.customer_code ASC, O.order_type ASC, O.qty_date ASC

Si vous avez besoin renvoyer les numéros de lignes, il vous faut de modifier un peu cette requête et utiliser la clé simple :

SELECT num, O.*
  FROM orders O
       INNER JOIN
       (SELECT count(*) AS num, O2.order_id
          FROM orders O1
               INNER JOIN customers C1 ON O1.customer_code = C1.customer_code
               INNER JOIN orders O2 ON O1.order_id <= O2.order_id
               INNER JOIN customers C2 
                 ON O2.customer_code = C2.customer_code AND
                    C1.country_code = C2.country_code AND
                    C1.country_code = 'IT'
          GROUP BY O2.order_id 
          HAVING count(*) BETWEEN @offset AND @offset + @batch_size - 1
       ) AS OO ON O.order_id = OO.order_id
  ORDER BY OO.num ASC  

La fonction row_number()

Une exemple "standard" est disponible dans l'aide en ligne (MS SQL Server Books online). Notre requête est similaire :

WITH ordered_orders AS (
  SELECT O.*,
         row_number() OVER(
           ORDER BY O.product_code ASC, 
                    O.customer_code ASC, 
                    O.order_type ASC, 
                    O.qty_date ASC
         ) AS row_num
    FROM orders O INNER JOIN customers C ON O.customer_code = C.customer_code
    WHERE C.country_code = 'IT'
)
SELECT * 
  FROM ordered_orders
  WHERE row_num BETWEEN @offset AND @offset + @batch_size - 1 

La table temporaire

On stocke dans la table temporaire le résultat intermédiaire qui ne contient que des lignes numérotées et leur clés. Puis, on sélectionne la plage nécessaire en faisant la jointure à la table principale.

N'oubliez pas augmenter la taille de la base de données temporaire (tempdb). Pour cette exemple la taille requis est 1,5 Go. Ensuite, la défaut principale de cette méthode est l'absence de  limite supérieure. La table temporaire va grossir en fonction du numéro de la ligne initiale, et la rapidité de sélection va dégrader.

CREATE TABLE #orders(
  row_num       int identity(1, 1) NOT NULL,
  product_code  nvarchar(18) NOT NULL,
  customer_code nvarchar(15) NOT NULL,
  order_type    nvarchar(4)  NOT NULL,
  qty_date      datetime     NOT NULL
);
 
INSERT INTO #orders (product_code, customer_code, order_type, qty_date)
SELECT TOP (@offset + @batch_size)
       O.product_code, O.customer_code, O.order_type, O.qty_date
  FROM orders O INNER JOIN customers C ON O.customer_code = C.customer_code 
  WHERE C.country_code = 'IT'
  ORDER BY O.product_code ASC, O.customer_code ASC, O.order_type ASC, O.qty_date ASC;
 
SELECT O.*
  FROM #orders T INNER JOIN orders O
       ON T.product_code = O.product_code AND
          T.customer_code = O.customer_code AND
          T.order_type = O.order_type AND
          T.qty_date = O.qty_date
  WHERE T.row_num BETWEEN @offset and @offset + @batch_size - 1;
 
DROP TABLE #orders; 

L'instruction SELECT TOP

La méthode est basée sur l'intersection de deux résultats trié en contresens. En fait, il n'y a pas de différence par rapporte de la méthode "Table temporaire" sauf que SQL Server crée et manipule les tables temporaire implicitement. Par contre, les comparaisons pour les blocs de petit talle (100 lignes) montre que cette manipulation sous-jacentes sont moins efficaces.

SELECT TOP (@batch_size) *
  FROM
    (SELECT TOP (@offset + @batch_size) O.*
       FROM orders O INNER JOIN customers C ON O.customer_code = C.customer_code 
       WHERE C.country_code = 'IT'
       ORDER BY O.product_code DESC, O.customer_code DESC, O.order_type DESC, O.qty_date DESC
    ) AS T1
  ORDER BY product_code ASC, customer_code ASC, order_type ASC, qty_date ASC 

Le curseur de serveur

Les fonctions de manipulation des curseurs de serveur ne sont pas bien documenté, mais elles sont fondamentales pour toutes les API d'accès de données comme ADO ou ODBC. Les descriptions de ces fonctions vous pouvez trouvez facilement en Internet, par exemple, "System stored procedures".

DECLARE @handle int, @rows int;
 
EXEC sp_cursoropen 
  @handle OUT, 
  'SELECT O.* FROM orders O INNER JOIN customers C ON O.customer_code = C.customer_code
   WHERE C.country_code = ''IT''
   ORDER BY O.product_code ASC, O.customer_code ASC, O.order_type ASC, O.qty_date ASC', 
  1, -- Keyset-driven cursor
  1, -- Read-only
  @rows OUT SELECT @rows; -- Contains total rows count
 
EXEC sp_cursorfetch 
  @handle, 
  16,     -- Absolute row index
  400001, -- Fetch from row
  100000  -- Rows count to fetch
 
EXEC sp_cursorclose @handle; 

L'instruction SET ROWCOUNT

Cette méthode ne fonction qu'en cas de clé simple mais nous avons les meilleurs résultats dans la majorité de tests.

DECLARE @order_id int;
 
SET ROWCOUNT @offset;
SELECT @order_id = O.order_id 
  FROM orders O INNER JOIN customers C ON O.customer_code = C.customer_code 
  WHERE C.country_code = 'IT'
  ORDER BY O.order_id ASC;
 
SET ROWCOUNT @batch_size;
SELECT O.* 
  FROM orders O INNER JOIN customers C ON O.customer_code = C.customer_code 
  WHERE C.country_code = 'IT' AND
        O.order_id >= @order_id
  ORDER BY O.order_id ASC;
SET ROWCOUNT 0; 

Les résultats des tests

Tous les résultats sont mis ensemble dans la table suivante :

N° de la ligne initiale (@offset) Taille de bloc (@batch_size) Temps de requête par méthodes, secondes
Row_number Rowcount Server cursor Temp table TOP
1 2 3 4 3 4 1 2 3 4 1 2 3 4 1 2 3 4
 
1 100 5 5 5 5 7 6 94 88 86 87 2 2 6 5 5 6 5 5
1000 100 24 29 24 30 26 51 36 90 34 87 1 3 24 58 25 32 24 32
10000 100 79 108 78 107 80 81 36 88 33 87 2 3 78 78 79 81 78 80
100000 100 246 358 234 343 240 78 36 88 30 87 13 28 240 79 250 82 236 81
200000 100 48 394 30 368 31 80 36 88 25 86 17 29 34 82 46 83 35 82
300000 100 47 405 20 379 21 78 32 88 24 87 21 13 25 80 49 84 24 82
400000 100 59 426 24 386 25 80 31 88 21 86 27 30 29 81 68 84 29 83
700000 100 88 450 45 399 36 81 27 89 18 88 42 19 46 87 107 88 47 85
 
400001 100000 434 443 395 394 98 94 123 102 102 103 106 125 97 98 96 98 95 95
500001 100000 125 468 40 399 17 94 50 102 45 102 59 125 21 100 47 97 43 96
600001 100000 104 468 44 406 16 94 49 102 45 102 63 116 26 100 45 100 43 97
700001 100000 122 473 67 411 12 91 46 101 39 98 61 127 18 99 41 100 37 97

Les numéros de colonnes sont ceux des séries :
(1) - la clé composite "product_code, customer_code, order_type, qty_date", on ne redémarre SQL Server qu'au début de la série uniquement
(2) - idem (1) mais on redémarre SQL Server au début de chaque test de la série
(3) - la clé simple "order_id", on ne redémarre SQL Server qu'au début de la série uniquement
(4) - idem (3) mais on redémarre SQL Server au début de chaque test de la série

Les résultats graphiques :

Résume

La fonction de classement row_number() a fait les mauvais résultats par rapport des autres méthodes. Par contre, en cas de volumétrie non significative elle reste la méthode recommandée et bien documentée.

La méthode plus rapide est "SET ROWCOUNT". Par contre, il ne fonctionne qu'en cas de clé simple.

La méthode plus polyvalente est "le curseur de serveur" qui font des bons résultats et vous permet également utiliser vos requêtes, vues et fonctions existantes sans les modifier et y insérer les paramètres de pagination.

En espérant que Microsoft ajoutera l'instruction LIMIT dans les prochaines versions SQL Server, il ne vous reste que faire vos propres tests et choisir la méthode appropriée à vos besoins.