Une autre technique de pagination

index pour sélectionner les premiers
enregistrements d’un ensemble
de résultats.
Vous savez que la requête suivante
sélectionne les 10 premiers enregistrements
dans la table Orders de la
base de données Northwind :

SELECT TOP 10 * FROM Orders

Cette syntaxe sert à  sélectionner
un bloc de 10 enregistrements dans
une liste plus grande. Pour sélectionner
une page d’enregistrements spécifique,
il suffit de connaître le
nombre d’enregistrements par page
et la page à  laquelle vous devez accéder
pour renvoyer les informations
dont les utilisateurs ont besoin. Par
conséquent, si une page contient 10
enregistrements et si vous souhaitez
récupérer la page 3 de l’ensemble de
résultats, vous pouvez recourir à  la
requête suivante :

SELECT TOP 10 * FROM Orders WHERE
OrderID NOT IN (SELECT TOP 20
OrderID FROM Orders)

Cette requête renvoie le bloc de 10 enregistrements venant
après les 20 premiers enregistrements de la liste (autrement
dit, les enregistrements 21 à  30). Vous pouvez généraliser
cette requête comme illustré par le pseudo code
suivant :

SELECT TOP page_size * FROM
Orders WHERE OrderID NOT IN
(SELECT TOP
(page_size *
(current_page - 1))
OrderID FROM Orders)

Cette technique fonctionne parfaitement avec des
nombres élevés d’enregistrements, mais ses performances diminuent à  mesure que le nombre de
pages consultées augmente. Ce phénomène
est dû à  la relative inefficacité de
l’opérateur IN. Si, par exemple, vous recherchez
la page 500 d’un recordset
contenant des pages de 10 enregistrements,
la sous-requête suivante est
écrite derrière l’opérateur IN :

(SELECT TOP 4990 OrderID FROM Orders)

Le serveur doit alors comparer
chaque OrderID qu’il essaie de récupérer
par rapport aux 4990 autres
OrderID. Cette tâche n’est pas mince.
Néanmoins, SQL Server peut traiter de
telles requêtes rapidement car ses index
organisent les données d’une manière
qui rend efficace et accélère les
recherches. (Dans la mesure où
OrderID est une clé primaire, SQL
Server l’indexe par défaut.) Bien que
les performances décroissent lorsque
le nombre de pages augmente, le ralentissement
devient perceptible uniquement
pour des nombres de pages
élevés. Cette diminution progressive
des performances n’est pas problématique
puisque les utilisateurs ne consulteront
probablement que les premières
pages et que les performances de la requête
sont, dans ce cas, excellentes. Si
l’application trie et filtre les données
correctement, les utilisateurs trouveront
très vraisemblablement ce qu’ils
cherchent sur la première page ou la
deuxième. S’ils ne trouvent pas rapidement
les enregistrements dont ils ont
besoin, la plupart des utilisateurs lanceront
une nouvelle recherche avec des
paramètres de tri et de filtrage différents,
plutôt que d’essayer de trouver
les informations, par exemple à  la page
2479.
Comme vous l’avez peut-être remarqué,
le fait d’avoir une clé primaire
sur la table sélectionnée est crucial
dans ce type de situation. La clé primaire
fournit pour chaque enregistrement
un identificateur unique qui permet à  la requête de différencier les enregistrements qu’elle
est censée trouver des autres. De même, des éléments tels
que les clauses WHERE et ORDER BY sont absents de cet
exemple de requête de pagination. Le listing 2 illustre une
requête généralisée avec ces deux éléments.
Le tri est en général inefficace et ralentit considérablement
les requêtes. Comme l’efficacité des opérations de tri
et filtrage dans SQL Server est maximale sur des colonnes indexées
et comme SQL Server indexe par défaut la colonne
définie en tant que clé primaire, vous pouvez optimiser encore
la requête. Au lieu de sélectionner toutes les colonnes
de la table, vous pouvez limiter la recherche à  la clé primaire,
jusqu’à  ce que vous parveniez à  circonscrire la plage d’enregistrements
à  la page que vous recherchez, puis récupérer
seulement à  ce stade toutes les colonnes de la table, comme
l’illustre le code du listing 3.
Si vous avez un index qui inclut la clé primaire, les
champs faisant l’objet du tri et ceux
faisant l’objet du filtrage, SQL Server
peut utiliser ses tables d’index pour
trouver les enregistrements que vous
essayez de récupérer. Ce type d’index
est appelé index de couverture car il
inclut tous les champs dont la requête
a besoin. Une requête exécutée sur un
index de couverture est plus rapide
qu’une requête exécutée sur une
table. Lorsque vous extrayez enfin tous
les enregistrements recherchés (à  savoir,
ceux spécifiés par la partie SELECT
* de la requête), l’index de clé
primaire permet à  SQL Server d’accéder
à  ceux-ci de manière extrêmement
efficace.
La requête présentée dans le listing
3 fournit une méthode à  la fois
simple et efficace pour récupérer un
bloc particulier d’enregistrements.
Vous pouvez étoffer la requête par des
clauses GROUP BY et HAVING. Pour
simplifier l’utilisation de la requête, j’ai
encapsulé ses fonctionnalités dans une
procédure stockée SELECT_WITH_PAGING,
présentée dans le listing 4.
Cette procédure stockée accepte les
paramètres (et leurs types) suivants :
fields_to_return (string), primary_
key (string), table_name (string),
page_number (integer, par défaut 1),
page_size (integer), get_record_count
(true/false), filter_conditions (string),
sort_columns (string) et group_by
(string).
Ainsi, par exemple, si la taille des
pages est de 10 enregistrements et si
vous souhaitez récupérer la page 3
d’une liste qui inclut les ID utilisateur
et les noms des destinataires à  partir
de la table Orders de la base de données Northwind, en
effectuant un tri par date, l’instruction prendra la forme
suivante :

EXEC SELECT_WITH_PAGING
'CustomerID, ShipName',
'OrderID',
'Northwind.dbo.Orders',
'3, 10, 1, '' , 'OrderDate'

Lorsque vous exécutez cette requête,
notez qu’elle renvoie un deuxième
recordset contenant le nombre
total d’enregistrements que la requête
filtrée récupérerait normalement. Ce
deuxième recordset est utile si vous
souhaitez afficher le nombre total de
pages pour un utilisateur, ce qui est le
cas de figure le plus fréquent ; il ressort
de mon expérience que les utilisateurs
aiment voir le nombre total de pages,
plutôt que de simples liens « page précédente
» et « page suivante ». Pour récupérer
le nombre total d’enregistrements,
j’affecte la valeur 1 au sixième
paramètre. Si vous ne réinitialisez pas
ce paramètre, vous ne pouvez pas
connaître le nombre d’enregistrements
car la requête extrait uniquement
10 enregistrements (taille de la
page) à  partir de la base de données.

Notez que le paramètre table_
name peut également être une jointure
entre deux ou plusieurs tables,
voire une sous-requête. Par exemple,
les deux chaînes suivantes sont des paramètres
valides de la procédure stockée:

'Northwind.dbo.Orders A JOIN
Northwind.dbo.Customers B
ON A.CustomerID = B.CustomerID'
'(SELECT * FROM
Northwind.dbo.Orders
WHERE OrderDate > ''8/1/1996'')
AS tbl'

La technique présentée dans cet article
permet une pagination côté serveur
simple et efficace portant sur des
nombres élevés d’enregistrements et
je l’ai employée avec succès sur des
tables contenant des millions de lignes.
Dans un cas, l’extraction des premières
pages d’une table de 25 millions d’enregistrements
a pris plus de 40 secondes
avec l’objet ADO Recordset.
Avec la procédure stockée présentée
ici, l’exécution de la même requête a
duré moins d’une seconde. Cette
approche est particulièrement utile
lorsqu’un nombre excessif de données
et le critère performances rendent inappropriées
les fonctionnalités de
pagination de l’objet ADO Recordset.