La prochaine version de SQL Server propose l’implémentation de la pagination à l’aide de la clause ORDER BY. Il sera donc possible dorénavant de demander une rangée de lignes de données d’une requête comme on pourrait le faire avec MySQL. Cependant il est important de préciser qu’il existe plusieurs méthodes pour arriver au même résultat. Nous les survolerons dans la suite du billet. Il existe déjà une multitude de posts sur le sujet sur la toile. Le but ici n’est pas de reprendre forcément ce qui est déjà dit. Je voulais simplement ici aborder la notion de performance avec cette nouvelle clause ORDER BY.

Avant de parler de cette nouvelle implémentation passons en revue les différents moyens qu’il existe pour réaliser de la pagination sur SQL Server.

 Méthode 1 : Utilisation de la clause TOP

SELECT * 
FROM
(
  SELECT TOP ([nb_rows]) * 
  FROM (
           SELECT TOP ([offset]) *
           FROM dbo.T
           ORDER BY id) AS t1 
  ORDER BY id DESC
) AS t2
ORDER BY col1;

Méthode 2 : Utilisation de la fonction ROW_NUMBER() à partir de SQL Server 2005

WITH CTE
AS
(
     SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS RowNumber
     FROM dbo.T
)
SELECT 
id,
col1
FROM CTE
WHERE RowNumber BETWEEN ([offset]) AND ([nb_rows])
ORDER BY id;

Avec Denali une nouvelle implémentation est possible avec l’utilisation de la clause ORDER BY et les arguments OFFSET et FETCH NEXT ROW ONLY.

Méthode 3 : Utilisation de la clause ORDER BY

SELECT * 
FROM dbo.T
ORDER BY id 
OFFSET [offset] ROWS 
FETCH NEXT [nb_rows] ROWS ONLY;

On constate que la syntaxe pour réaliser de la pagination est vraiment simplifiée et plutôt intuitive à utiliser. Cependant on pourrait se poser la question de la performance avec cette nouvelle méthode … Pour cela utilisons la table et le jeu de données  suivant :

IF OBJECT_ID(‘T’) IS NOT NULL
DROP TABLE T;

CREATE TABLE dbo.T
(
id INT IDENTITY(1, 1) PRIMARY KEY,
col1 VARCHAR(100)
);

DECLARE @i INT = 1;

WHILE @i < 100000
BEGIN
INSERT dbo.T (col1) VALUES (REPLICATE(‘T’, 50) + CAST(@i AS VARCHAR(50)));
SET @i += 1;
END;

Le but maintenant est de comparer les différentes implémentations. Pour cela nous regarderons les plans d’exécutions générés et nous réaliserons une série de tests pour récupérer les consommations IO et CPU de chaque implémentation. Le script des différentes méthodes qui suit prend comme référence un offset de 5000 avec un nombre de lignes retournées allant de 1 à 150.

DECLARE @nb_rows INT = 1;
DECLARE @offset INT = 5000;

WHILE @nb_rows <= 150
BEGIN
    SELECT *
    FROM
    (
     SELECT TOP (@nb_rows) *
     FROM (
           SELECT TOP (@offset + @nb_rows) *
           FROM dbo.T
           ORDER BY id) AS t1
     ORDER BY id DESC
    ) AS t2
    ORDER BY col1;

    WITH CTE
    AS
    (
     SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS RowNumber
     FROM dbo.T
    )
    SELECT
     id,
     col1
    FROM CTE
    WHERE RowNumber BETWEEN (@offset + 1) AND (@offset + @nb_rows)
    ORDER BY id;

    SELECT *
    FROM dbo.T
    ORDER BY id
    OFFSET @offset ROWS
    FETCH NEXT @nb_rows ROWS ONLY;

    SET @nb_rows += 10;

    WAITFOR DELAY ’00:00:01′;
END

Les plans d?exécutions réels pour chaque implémentation sont les suivants :

On constate que le plan d’exécution liée à notre nouvelle clause ORDER BY est beaucoup plus simple et apparemment moins coûteux en terme de coût. Cela peut paraître logique puisque beaucoup moins d’opérateurs sont concernés dans ce dernier cas pour le même résultat. En activant les statistiques de profil voici ce que l’on peut voir entre les dernières requêtes :

ROW_NUMBER :

ORDERY BY + OFFSET + FETCH NEXT ROW ONLY :

Un nouvelle fonction OFFSET EXPRESSION() semble avoir fait son apparition.

Le script précédent nous donne les résultats suivants :

L’emploi de la fonction ROW_NUMBER() et de la nouvelle clause ORDER BY semble plus efficace que le double emploi de l’opérateur TOP. Cependant il n’est pas évident de voir une différence nette entre ROW_NUMBER et ORDER BY. Pour en avoir le coeur net j’ai décidé de voir ce que cela donnerait sur une base de données réelle en production. Le test concerne une table d’intervention technicien sur laquelle on aimerait implémenter de la pagination. Voici les caractéristiques de cette table :

 

La table a une taille  de 631 Mo. Je vous passe la définition de la table volontairement car le but est ici de voir si « brute de fonderie » notre nouvelle clause est plus performante que les deux autres méthodes.

Le script utilisé est le suivant : (on récupère un offset de 1000 lignes et on augmente la plage de lignes ramenées jusqu’à 1000 par incrément de 100)

DECLARE @nb_rows INT = 2;
DECLARE @offset INT = 1000;

WHILE @nb_rows <= 1000
BEGIN
    WITH CTE
    AS
    (
        SELECT
         inquiry_id,
         [state],
         [priority],
         [type],
         CASE close_in_time
          WHEN ‘O’ THEN ‘clos dans les temps’
          ELSE ‘Hors délai’
         END  close_in_time,
         open_dt,
         Date_MiseSurSite_Effectué,
         RemiseEnService_dt,
         closed_dt,
         closed_by
        FROM dbo.sv_inquiry
    )
    SELECT *
    FROM
    (
        SELECT TOP (@nb_rows) *
        FROM (
            SELECT TOP (@offset + @nb_rows) *
            FROM CTE
            ORDER BY inquiry_id) AS t1
        ORDER BY inquiry_id DESC
    ) AS t2
    ORDER BY inquiry_id;

    WITH CTE
    AS
    (
        SELECT
         inquiry_id,
         [state],
         [priority],
         [type],
         CASE close_in_time
          WHEN ‘O’ THEN ‘clos dans les temps’
          ELSE ‘Hors délai’
         END  close_in_time,
         open_dt,
         Date_MiseSurSite_Effectué,
         RemiseEnService_dt,
         closed_dt,
         closed_by
        FROM dbo.sv_inquiry
    )
    ,CTE2
    AS
    (
        SELECT *, ROW_NUMBER() OVER (ORDER BY inquiry_id) AS RowNumber
        FROM CTE
    )
    SELECT
        *
    FROM CTE2
    WHERE RowNumber BETWEEN (@offset + 1) AND (@offset + @nb_rows)
    ORDER BY inquiry_id;

    SELECT
     inquiry_id,
     [state],
     [priority],
     [type],
     CASE close_in_time
      WHEN ‘O’ THEN ‘clos dans les temps’
      ELSE ‘Hors délai’
     END  close_in_time,
     open_dt,
     Date_MiseSurSite_Effectué,
     RemiseEnService_dt,
     closed_dt,
     closed_by
    FROM dbo.sv_inquiry
    ORDER BY inquiry_id
    OFFSET @offset ROWS
    FETCH NEXT @nb_rows ROWS ONLY;

    WAITFOR DELAY ’00:00:01′;

    SET @nb_rows += 100;
END

Les résultats obtenus :

Dans mon cas le gain de performance n’est pas si évident. Bien sûr il faudrait tester sur un ensemble de cas beaucoup plus large mais personnellement j’attendrai de voir sortir une version RTM ou supérieure … mais je vous invite à compléter ce billet si le coeur vous en dit. En tout cas nous pouvons déjà affirmer qu’il est beaucoup plus simple d’implémenter la pagination via notre clause ORDER BY et les arguments OFFSET et FETCH NEXT.

David BARBARIN (Mikedavem)
MVP SQL Server