Requête SQL Server avec pagination et comptage
je veux faire une requête de base de données avec pagination. Donc, j'ai utilisé une expression de table commune et une fonction de classement pour réaliser ceci. Regardez l'exemple ci-dessous.
declare @table table (name varchar(30));
insert into @table values ('Jeanna Hackman');
insert into @table values ('Han Fackler');
insert into @table values ('Tiera Wetherbee');
insert into @table values ('Hilario Mccray');
insert into @table values ('Mariela Edinger');
insert into @table values ('Darla Tremble');
insert into @table values ('Mammie Cicero');
insert into @table values ('Raisa Harbour');
insert into @table values ('Nicholas Blass');
insert into @table values ('Heather Hayashi');
declare @pagenumber int = 2;
declare @pagesize int = 3;
declare @total int;
with query as
(
select name, ROW_NUMBER() OVER(ORDER BY name ASC) as line from @table
)
select top (@pagesize) name from query
where line > (@pagenumber - 1) * @pagesize
ici, je peux spécifier les variables @pagesize et @pagenumber pour me donner juste les enregistrements que je veux. Cependant, cet exemple (qui vient d'une procédure stockée) est utilisé pour faire une grille de pagination dans une application web. Cette application Web nécessite d'afficher les numéros de page. Par exemple, si a avoir 12 enregistrements dans la base de données et la taille de la page est de 3, alors je vais devoir montrer 4 liens, chacun représentant une page.
mais je ne peux pas faire cela sans savoir combien d'enregistrements il y a, et cet exemple me donne juste le sous-ensemble d'enregistrements.
puis j'ai changé la procédure stockée pour retourner le nombre(*).
declare @pagenumber int = 2;
declare @pagesize int = 3;
declare @total int;
with query as
(
select name, ROW_NUMBER() OVER(ORDER BY name ASC) as line, total = count(*) over()from @table
)
select top (@pagesize) name, total from query
where line > (@pagenumber - 1) * @pagesize
Alors, avec chaque ligne, il affiche le nombre total d'enregistrements. Mais je n'aimais pas ça.
ma question Est s'il y a une meilleure façon (performance) de faire cela, peut-être en mettant la variable @total sans retourner cette information dans le SELECT. Ou est-ce que cette colonne est quelque chose qui ne nuira pas trop à la performance?
Merci
5 réponses
en supposant que vous utilisez MSSQL 2012, vous pouvez utiliser Offset and Fetch qui nettoie considérablement le côté serveur paging. Nous avons constaté que la performance est bonne et, dans la plupart des cas, meilleure. En ce qui concerne le nombre total de colonnes, il suffit d'utiliser la fonction window en dessous de inline...elle n'inclura pas les limites imposées par 'offset' et 'fetch'.
pour Row_Number, vous pouvez utiliser les fonctions de fenêtre comme vous l'avez fait, mais je vous recommande de calculer ce côté client comme (pagenumber*pagesize + resultsetRowNumber), donc si vous êtes sur la cinquième page de 10 résultats et sur la troisième ligne, vous sortirez la ligne 53.
appliqué à une table de commandes avec environ 2 millions de commandes, j'ai trouvé ce qui suit:
FAST VERSION
en moins d'une seconde. La bonne chose à ce sujet est que vous pouvez faire votre filtrage dans l'expression de table commune, une fois et il s'applique à la fois à la pagination processus et le comte. Quand vous avez beaucoup de prédicats dans la clause où, cela garde les choses simples.
declare @skipRows int = 25,
@takeRows int = 100,
@count int = 0
;WITH Orders_cte AS (
SELECT OrderID
FROM dbo.Orders
)
SELECT
OrderID,
tCountOrders.CountOrders AS TotalRows
FROM Orders_cte
CROSS JOIN (SELECT Count(*) AS CountOrders FROM Orders_cte) AS tCountOrders
ORDER BY OrderID
OFFSET @skipRows ROWS
FETCH NEXT @takeRows ROWS ONLY;
VERSION LENTE
cela a pris environ 10 secondes, et c'est le compte(*) qui a causé la lenteur. Je suis surpris que ce soit si lent, mais je pense que c'est simplement calculer le total pour chaque rangée. Il est très propre bien.
declare @skipRows int = 25,
@takeRows int = 100,
@count int = 0
SELECT
OrderID,
Count(*) Over() AS TotalRows
FROM Location.Orders
ORDER BY OrderID
OFFSET @skipRows ROWS
FETCH NEXT @takeRows ROWS ONLY;
CONCLUSION
nous avons déjà passé en revue ce processus d'ajustement de la performance et avons découvert qu'il dépendait de la requête, des prédicats utilisés et des index impliqués. Par exemple, à la seconde où nous avons introduit une vue, elle est figée, donc nous interrogeons la table de base, puis nous joignons la vue (qui inclut la table de base) et elle fonctionne en fait très bien.
je suggère d'avoir quelques stratégies simples et de les appliquer à des requêtes de grande valeur qui sont haletant.
DECLARE @pageNumber INT = 1 ,
@RowsPerPage INT = 20
SELECT *
FROM TableName
ORDER BY Id
OFFSET ( ( @pageNumber - 1 ) * @RowsPerPage ) ROWS
FETCH NEXT @RowsPerPage ROWS ONLY;
Que faire si vous calculez le compte à l'avance?
declare @pagenumber int = 2;
declare @pagesize int = 3;
declare @total int;
SELECT @total = count(*)
FROM @table
with query as
(
select name, ROW_NUMBER() OVER(ORDER BY name ASC) as line from @table
)
select top (@pagesize) name, @total total from query
where line > (@pagenumber - 1) * @pagesize
une autre façon, est de calculer max(line) . Voir le lien
retourner le total des enregistrements de SQL Server lorsque vous utilisez ROW_NUMBER
UPD:
pour une seule requête, cochez la réponse de marc_s sur le lien ci-dessus.
with query as
(
select name, ROW_NUMBER() OVER(ORDER BY name ASC) as line from @table
)
select top (@pagesize) name,
(SELECT MAX(line) FROM query) AS total
from query
where line > (@pagenumber - 1) * @pagesize
@pagenumber=5
@pagesize=5
créer une expression de table commune et écrire logique comme ceci
Between ((@pagenumber-1)*(@pagesize))+1 and (@pagenumber *@pagesize)
il y a plusieurs façons de réaliser la pagination: j'espère que cette information vous sera utile, à vous et à d'autres.
exemple 1: utilisation de la clause offset-fetch next. introduire en 2005
declare @table table (name varchar(30));
insert into @table values ('Jeanna Hackman');
insert into @table values ('Han Fackler');
insert into @table values ('Tiera Wetherbee');
insert into @table values ('Hilario Mccray');
insert into @table values ('Mariela Edinger');
insert into @table values ('Darla Tremble');
insert into @table values ('Mammie Cicero');
insert into @table values ('Raisa Harbour');
insert into @table values ('Nicholas Blass');
insert into @table values ('Heather Hayashi');
declare @pagenumber int = 1
declare @pagesize int = 3
--this is a CTE( common table expression and this is introduce in 2005)
with query as
(
select ROW_NUMBER() OVER(ORDER BY name ASC) as line, name from @table
)
--order by clause is required to use offset-fetch
select * from query
order by name
offset ((@pagenumber - 1) * @pagesize) rows
fetch next @pagesize rows only
exemple 2: en utilisant la fonction row_number () et entre
declare @table table (name varchar(30));
insert into @table values ('Jeanna Hackman');
insert into @table values ('Han Fackler');
insert into @table values ('Tiera Wetherbee');
insert into @table values ('Hilario Mccray');
insert into @table values ('Mariela Edinger');
insert into @table values ('Darla Tremble');
insert into @table values ('Mammie Cicero');
insert into @table values ('Raisa Harbour');
insert into @table values ('Nicholas Blass');
insert into @table values ('Heather Hayashi');
declare @pagenumber int = 2
declare @pagesize int = 3
SELECT *
FROM
(select ROW_NUMBER() OVER (ORDER BY PRODUCTNAME) AS RowNum, * from Products)
as Prodcut
where RowNum between (((@pagenumber - 1) * @pageSize )+ 1)
and (@pagenumber * @pageSize )
j'espère que ceux-ci seront utiles à tous