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 :

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.