Discussion sur l'implémentation d'une pagination de jeux de résultats volumineux dans ASP.NET

Dans les applications Web, la pagination d’un jeu de résultats de base de données volumineux est un problème bien connu. En termes simples, vous ne souhaitez pas que toutes les données de requête soient affichées sur une seule page. L'affichage avec pagination est donc plus approprié. Bien que ce ne soit pas une tâche simple dans ASP traditionnel, dans ASP.NET, le contrôle DataGrid simplifie ce processus à quelques lignes de code seulement. Par conséquent, dans asp.net, la pagination est très simple, mais l'événement de pagination DataGrid par défaut lira tous les enregistrements de la base de données et les placera dans l'application Web asp.net. Lorsque vous avez plus d'un million de données, cela entraînera de sérieux problèmes de performances (si vous n'y croyez pas, vous pouvez exécuter une requête dans votre application et regarder la consommation mémoire de aspnet_wp.exe dans le gestionnaire de tâches Situation) C'est pourquoi il est nécessaire de personnaliser le comportement de la pagination, de manière à garantir que seuls les enregistrements de données requis par la page en cours sont obtenus.

Il existe de nombreux articles et publications sur ce problème sur Internet, ainsi que des solutions éprouvées. Mon but en écrivant cet article n'est pas de vous montrer une procédure stockée qui résoudra tous vos problèmes, mais d'optimiser les méthodes existantes et de vous fournir une application à tester afin que vous puissiez le faire en fonction de vos besoins de développement.

Mais je ne suis pas très satisfait des méthodes actuellement mises en ligne. Tout d'abord, on utilise l'ADO traditionnel, qui est évidemment écrit pour l'ASP "ancien". Les méthodes restantes sont des procédures stockées SQL Server, et certaines d'entre elles sont inutilisables en raison de temps de réponse trop lents, comme vous pouvez le voir dans les résultats de performances à la fin de l'article, mais quelques-unes ont retenu mon attention.

Généralisation

Je souhaite analyser attentivement les trois méthodes actuellement couramment utilisées, à savoir les tables temporaires (TempTable), le SQL dynamique (DynamicSQL) et le nombre de lignes (Rowcount). Dans ce qui suit, je préfère appeler la deuxième méthode la méthode Asc-Desc (ascendante-descendante). Je ne pense pas que SQL dynamique soit un bon nom car vous pouvez également appliquer une logique SQL dynamique dans une autre méthode. Le problème commun avec toutes ces procédures stockées est que vous devez estimer les colonnes sur lesquelles vous allez trier, pas seulement les colonnes de clé primaire (colonnes PK), ce qui peut entraîner une série de problèmes - pour chaque requête, vous devez affichez-le via la pagination, ce qui signifie que pour chaque colonne de tri différente, vous devez avoir de nombreuses requêtes de pagination différentes, ce qui signifie que vous effectuez soit une procédure stockée différente pour chaque colonne de tri (quelle que soit la méthode de pagination utilisée), soit vous devez placez cette fonctionnalité dans une procédure stockée à l’aide de SQL dynamique. Ces deux méthodes ont un faible impact sur les performances, mais elles augmentent la maintenabilité, surtout si vous devez utiliser cette méthode pour afficher différentes requêtes. Par conséquent, dans cet article, je vais essayer d'utiliser le SQL dynamique pour résumer toutes les procédures stockées, mais pour certaines raisons, nous ne pouvons atteindre qu'une universalité partielle, vous devez donc toujours écrire des procédures stockées indépendantes pour les requêtes complexes.

Le deuxième problème lié à l'autorisation de tous les champs de tri, y compris les colonnes de clé primaire, est que si ces colonnes ne sont pas correctement indexées, aucune de ces méthodes ne sera utile. Dans toutes ces méthodes, une source de pagination doit être triée en premier. Pour les grandes tables de données, le coût du tri à l'aide de colonnes non indexées est négligeable. Dans ce cas, toutes les procédures stockées ne peuvent pas être utilisées dans des situations réelles en raison du long temps de réponse. (Le temps correspondant varie de quelques secondes à quelques minutes, selon la taille du tableau et le premier enregistrement à obtenir). Les index sur d'autres colonnes peuvent introduire des problèmes de performances supplémentaires indésirables. Par exemple, ils peuvent devenir très lents si vous importez beaucoup de données chaque jour.

Table temporaire

Tout d'abord, je vais parler de la méthode des tables temporaires. C'est une solution largement recommandée que j'ai rencontrée à plusieurs reprises dans mes projets. Jetons un coup d'œil à l'essence de cette méthode :

CREATE TABLE #Temp(
ID int IDENTITÉ CLÉ PRIMAIRE,
PK /*iciPKtype*/
)

INSERT INTO #Temp SELECT PK FROM Table ORDER BY SortColumn

SELECT FROM Table JOIN # Temp temp ON Table.PK = temp .PK ORDER BY temp .ID WHERE ID > @StartRow AND ID< @EndRow

en copiant toutes les lignes dans le fichier temporaire table, nous pouvons optimiser davantage la requête (SELECT TOP EndRow...), mais la clé est le pire des cas - une table contenant 1 million d'enregistrements générera une table temporaire avec 1 million d'enregistrements.

ascendante-descendante

dans mon test.

Cette méthode utilise le tri par défaut dans la sous-requête et le tri inversé dans la requête principale. Le principe est le suivant :

DÉCLARER @temp TABLE(
PK /* TypePK */
NON NULL PRIMAIRE
)

INSÉRER DANS @temp SELECT TOP @PageSize PK FROM
(
SÉLECTIONNER HAUT (@StartRow + @PageSize)
PK,
SortColumn /* Si la colonne de tri est différente du PK, SortColumn doit
être également récupéré, sinon seul le PK est nécessaire
*/

COMMANDER PAR TriColonne
/*
ordre par défaut – généralement ASC
*/
)

COMMANDER PAR TriColonne
/*
ordre par défaut inversé – généralement DESC
*/

SELECT FROM Table JOIN @Temp temp ON Table .PK= temp .PK
COMMANDER PAR TriColonne
/*
commande par défaut
*/

comptage de lignes

repose sur l'expression SET ROWCOUNT dans SQL, afin que les lignes inutiles puissent être ignorées et que les enregistrements de lignes requis puissent être obtenus :

DECLARE @Sort /* le type de colonne de tri */

SET ROWCOUNT @ StartRow

SELECT @Sort=SortColumn FROM Table ORDER BY SortColumn
SET ROWCOUNT @PageSize

SELECT FROM Table WHERE SortColumn >= @Sort ORDER BY SortColumn

de sous-requête

que j'ai envisagées et leurs sources sont différentes. La première est la méthode bien connue de triple requête ou d'auto-requête. Dans cet article, j'utilise également une logique commune similaire qui englobe toutes les autres procédures stockées. L'idée ici est de se connecter à l'ensemble du processus, j'ai apporté quelques réductions au code original puisque le nombre d'enregistrements n'est pas nécessaire dans mes tests)

SELECT FROM Table WHERE PK IN (
SELECT TOP @PageSize PK FROM Table OÙ PK PAS DANS
(
SELECT TOP @StartRow PK FROM Table ORDER BY SortColumn)
COMMANDER PAR TriColonne)
ORDER BY SortColumn

Cursor

En regardant le groupe de discussion Google, j'ai trouvé la dernière méthode. Cette méthode utilise un curseur dynamique côté serveur. De nombreuses personnes essaient d'éviter d'utiliser des curseurs car ils ne sont pas pertinents et inefficaces en raison de leur ordre. Mais avec le recul, la pagination est en fait une tâche ordonnée, quelle que soit la méthode que vous utilisez, vous devez la renvoyer à la ligne de départ pour l'enregistrer. Dans la méthode précédente, vous sélectionnez d'abord toutes les lignes avant le début de l'enregistrement, ajoutez les lignes requises à enregistrer, puis supprimez toutes les lignes précédentes. Les curseurs dynamiques ont une option FETCH RELATIVE qui effectue des sauts magiques. La logique de base est la suivante :

DECLARE @PK /* PKType */

DECLARE @tblPK

TABLE(
PK /*PKType*/ CLÉ PRIMAIRE NON NULLE
)

DÉCLARE PagingCursor CURSOR DYNAMICREAD_ONLY FOR
SELECT @PK FROM Table ORDER BY SortColumn

OPEN PagingCursor
RÉCUPÉRER LE @StartRow RELATIF DE PagingCursor DANS @PK

WHILE @PageSize>0 AND @@FETCH_STATUS =0
COMMENCER
INSÉRER LES VALEURS @tblPK(PK) (@PK)
RÉCUPÉRER NEXT DE PagingCursor DANS @PK
FIXER @PageSize = @PageSize - 1
FIN

FERMER
Curseur de pagination
DÉSALLOCATION
PagingCursor

SELECT FROM Table JOIN @tblPK temp ON Table .PK= temp .PK

Généralisation de requêtes complexes

dans ORDER BY SortColumn

J'ai déjà souligné que toutes les procédures stockées utilisent du SQL dynamique pour réaliser la généralisation, donc en théorie elles peuvent utiliser n'importe quel type de requêtes complexes. Vous trouverez ci-dessous un exemple de requête complexe basée sur la base de données Northwind.

SELECT Customers.ContactName AS Client, Customers.Address + ' , ' + Customers.City + ', '+ Customers.Country
Adresse AS, SUM([OrderDetails].UnitPrice*[OrderDetails] .Quantity)
AS [Totalmoneyspent]

FROM Customers

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

INNER JOIN [ OrderDetails ] ON Orders.OrderID = [ OrderDetails].OrderID

WHERE Customers.Country <> 'USA' AND Customers.Country <> 'Mexique'

GROUPER PAR Customers.ContactName, Customers.Address, Customers.City, Customers.Country
HAVING(SUM([OrderDetails].UnitPrice * [ OrderDetails ] .Quantity)) > 1000

ORDER BY Customer DESC ,Address DESC

renvoie l'appel de stockage de pagination de la deuxième page comme suit :

EXEC ProcedureName

/*Tables */

'
Clients
INNER JOIN Commandes SUR Customers.CustomerID=Orders.CustomerID
INNER JOIN [OrderDetails] SUR Orders.OrderID=[OrderDetails].OrderID
'
,
/* PK */

'
Clients.IDClient
'
,
/* COMMANDE */

'
Clients.ContactName DESC,Clients.AddressDESC
'
,

/*Numéro de page */

2
,

/*Taille de la page */

10
,

/*Champs */

'
Clients.Nom du contact en tant que client,
Customers.Address+'' , '' +Customers.City+ '' , '' +Customers.Country ASAddress, SUM([OrderDetails].UnitPrice*[OrderDetails].Quantity)AS[Totalmoneyspent]
'
,

/*Filtre */

'
Customers.Country<>'' USA '' ANDCustomers.Country<> '' Mexique ''' ,

/*GroupBy */

'
Clients.IDClient,Clients.NomContact,Clients.Adresse,
Clients.Ville,Clients.Pays
HAVING(SUM([OrderDetails].UnitPrice*[OrderDetails].Quantity))>1000
'

Il convient de noter que vous avez utilisé des alias dans l'instruction ORDER BY de la requête d'origine, mais il est préférable de ne pas le faire dans une procédure stockée paginée, car sauter des lignes avant de commencer l'enregistrement prend beaucoup de temps. En fait, il existe de nombreuses méthodes de mise en œuvre, mais le principe n'est pas d'inclure tous les champs au début, mais d'inclure uniquement la colonne de clé primaire (équivalente à la colonne de tri dans la méthode RowCount), ce qui peut accélérer la réalisation du processus. tâche. Ce n'est que sur la page de demande que tous les champs obligatoires sont obtenus. De plus, il n'y a pas d'alias de champ dans la requête finale et dans les requêtes avec saut de ligne, les colonnes d'index doivent être utilisées à l'avance.

Il existe un autre problème avec la procédure stockée RowCount. Pour réaliser une généralisation, une seule colonne est autorisée dans l'instruction ORDER BY. Il s'agit également d'un problème avec la méthode ascendante-descendante et la méthode du curseur, bien qu'elles puissent trier plusieurs colonnes, mais. il faut s'assurer qu'il n'y a qu'un seul champ dans la clé primaire. Je suppose que cela pourrait être résolu avec du SQL plus dynamique, mais à mon avis, cela n'en vaut pas la peine. Même si de telles situations sont possibles, elles ne se produisent pas très souvent. Habituellement, vous pouvez utiliser les principes ci-dessus pour également paginer indépendamment les procédures stockées.

Tests de performances

Lors des tests, j'ai utilisé quatre méthodes. Si vous avez une meilleure méthode, je serais intéressé de la connaître. Quoi qu'il en soit, je dois comparer ces méthodes et évaluer leurs performances. Tout d'abord, ma première idée est d'écrire une application de test asp.net contenant un DataGrid de pagination, puis de tester les résultats de la page. Bien entendu, cela ne reflète pas le véritable temps de réponse de la procédure stockée, une application console est donc plus adaptée. J'ai également inclus une application Web, mais pas pour les tests de performances, mais comme exemple de pagination personnalisée DataGrid et de procédures stockées fonctionnant ensemble.

Lors du test, j'ai utilisé un grand tableau de données généré automatiquement et inséré environ 500 000 éléments de données. Si vous ne disposez pas d'une telle table à expérimenter, vous pouvez cliquer ici pour télécharger une conception de table et un script de procédure stockée pour générer des données. Au lieu d'utiliser une colonne de clé primaire à incrémentation automatique, j'ai utilisé un identifiant unique pour identifier l'enregistrement. Si j'utilise le script que j'ai mentionné ci-dessus, vous pouvez envisager d'ajouter une colonne d'auto-incrémentation après avoir généré la table. Les données d'auto-incrémentation seront triées numériquement en fonction de la clé primaire. Cela signifie également que vous avez l'intention d'utiliser une procédure stockée paginée. avec tri par clé primaire pour obtenir les données de la page actuelle.

Afin de mettre en œuvre le test de performances, j'ai appelé plusieurs fois une procédure stockée spécifique via une boucle, puis j'ai calculé le temps de réponse moyen. Compte tenu des raisons de mise en cache, afin de modéliser plus précisément la situation réelle, le temps nécessaire à la même page pour obtenir des données pour plusieurs appels à une procédure stockée n'est généralement pas adapté à l'évaluation. Par conséquent, lorsque nous appelons la même procédure stockée, , le numéro de page demandé pour chaque appel doit être aléatoire. Bien sûr, nous devons supposer que le nombre de pages est fixe, 10 à 20 pages, et que des données avec des numéros de page différents peuvent être obtenues plusieurs fois, mais de manière aléatoire.

Une chose que nous pouvons facilement remarquer est que le temps de réponse est déterminé par la distance des données de page à obtenir par rapport à la position de départ de l'ensemble de résultats, plus on s'éloigne de la position de départ de l'ensemble de résultats, plus il y aura d'enregistrements. ignoré. C'est aussi la raison pour laquelle je n'inclue pas le top 20 dans ma séquence aléatoire. Comme alternative, j'utiliserais 2^n pages, et la taille de la boucle est le nombre de pages différentes nécessaires * 1000, donc chaque page est récupérée près de 1000 fois (il y aura certainement un écart dû à des raisons aléatoires)

Résultats

ici sont mes résultats de tests :






Conclusion

Les tests ont été effectués du meilleur au moins performant : nombre de lignes, curseur, ascendant-descendant, sous-requête. Une chose intéressante est que les gens visitent rarement les pages après les cinq premières pages, donc la méthode de sous-requête peut répondre à vos besoins dans ce cas, en fonction de la taille de votre ensemble de résultats et de sa distance pour prédire la fréquence des occurrences de page. , vous utiliserez probablement également une combinaison de ces méthodes. Si c'était moi, je préférerais de toute façon la méthode du nombre de lignes, elle fonctionne plutôt bien, même pour la première page, le "n'importe quel cas" représente ici certains cas où la généralisation est difficile, dans ce cas, j'utiliserais un curseur. (J'utiliserais probablement la méthode de sous-requête pour les deux premiers, et la méthode du curseur après cela)