Nous donnons d'abord plusieurs méthodes de pagination principales et instructions de base, puis donnons directement la conclusion. Les lecteurs intéressés peuvent consulter les données suivantes pour plusieurs méthodes de pagination de procédures stockées couramment utilisées.
Méthode TopN
sélectionnez Top (@PageSize) dans TableName où ID Not IN
(Sélectionnez l'ID Top ( (@PageIndex-1)*@PageSize) dans le nom de la table où .... trier par ... )
où .... commander par ...
table temporaire
Copiez le code comme suit :
déclarer @indextable table(id int identité(1,1),nid int,PostUserName nvarchar(50))
déclarer @PageLowerBound int
déclarer @PageUpperBound int
définir @PageLowerBound=(@pageindex-1)*@pagesize--limite inférieure
définir @PageUpperBound=@PageLowerBound+@pagesize--limite supérieure
définir le nombre de lignes @PageUpperBound
insérer dans @indextable(nid,PostUserName) sélectionner ReplyID,PostUserName dans l'ordre TableName par ......
sélectionnez * dans TableName p,@indextable t où p.ID=t.nid
et t.id>@PageLowerBound et t.id<=@PageUpperBound commandés par t.id
CTE--2005 nouvelle syntaxe, similaire aux tables temporaires, mais le cycle de vie est légèrement différent. En voici juste une application.
avec cte_temp - Définissez la table de temps zéro. PageIndex est un champ calculé qui stocke le numéro de page des résultats de la recherche.
Comme (ceiling((Row_Number() over(order by .... )-1)/@pagesize as int) as PageIndex,* from TableName où.....)
sélectionnez * depuis cte_temp où pageindex=@pageindex-1 ;
en conclusion:
TopN est le plus rapide lorsque le nombre de pages est petit. S'il est inférieur à 10 pages, vous pouvez envisager de l'utiliser. Les temps de CTE et de table temporaire sont très stables et consomment plus de temps que la table temporaire. tempdb monte en flèche et IO augmente.
Comparaison des performances
Environnement de test : win2003server, Sqlserver2005, taille de la bibliothèque 2 567 245 lignes, clause nowhere, taille de page 50 pendant le test, numéro de page en tant que variable
Prenez 0, 3, 10, 31, 100, 316, 1000, 3162... pages, ce qui correspond à l'index de 10. Les résultats du test sont les suivants
Nombre de pagesTopN CTE Table temporaire (avec cache) Table temporaire (sans cache)
Améliorations CTE de la procédure stockée utilisée par l'entreprise
1 3 12 10 101 457 7302
3 15 7 79 5524 464 7191
10 127 5504 88 3801 464 6116
32 588 9672 122 3601 976 7602
100 4680 9738 166 4235 486 7151
316 45271 9764 323 3867 522 7255
1000 Nul 9806 869 2578 635 8948
3162 Nul 9822 2485 4110 12460 8210
10000 Nul 9754 7812 11926 14250 7359
31623 Nul 9775 18729 33218 15249 7511
100000 Nul Nul 31538 55569 17139 6124
Interprétation et analyse des données
Les tables temporaires sont divisées en deux types de temps : cache ou non. L'amélioration CTE réduit uniquement le nombre de colonnes sélectionnées dans la table temporaire CTE. Seuls le numéro de page et la clé primaire sont sélectionnés. le temps ne peut pas être calculé (le temps est trop long), l'unité de données est la milliseconde.
D'après les données ci-dessus, nous pouvons voir que TopN présente des avantages dans les 32 premières pages, mais à mesure que le nombre de pages augmente, les performances diminuent rapidement. L'amélioration du CTE est meilleure que celle du CTE, avec une amélioration moyenne d'environ deux secondes, mais. c'est toujours mieux que la table temporaire Lent, mais étant donné que la table temporaire augmentera la taille du fichier journal et provoquera beaucoup d'E/S, CTE a aussi ses propres avantages. La procédure stockée actuellement utilisée par l'entreprise est très efficace, mais les performances diminueront lorsque le numéro de page sera plus tardif.