Requête SQL Server avec pagination et nombre

En supposant que vous utilisez MSSQL 2012, vous pouvez utiliser Offset and Fetch qui nettoie grandement la pagination côté server. 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 de la window ci-dessous inline … elle n'inclura pas les limites imposées par 'offset' et 'fetch'.

Pour Row_Number, vous pouvez utiliser les fonctions de window comme vous l'avez fait, mais je vous recommand de calculer ce côté client comme (pagenumber * pagesize + resultsetRowNumber), donc si vous êtes sur la 5ème page de 10 résultats et sur la troisième ligne produirait la ligne 53.

Appliqué à une table Orders avec environ 2 millions de commands, j'ai trouvé ce qui suit:

VERSION RAPIDE

Cela a couru en less d'une seconde. La bonne chose à ce sujet est que vous pouvez faire votre filtrage dans l'expression de la table commune une fois et il s'applique à la fois le process de pagination et le nombre. Lorsque vous avez plusieurs prédicats dans la clause where, cela simplifie les choses.

 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 sec, et c'était le comte (*) qui a causé la lenteur. Je suis surpris que ce soit si lent, mais je suppose que c'est simplement calculer le total pour chaque rangée. C'est très propre cependant.

 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à effectué ce process d'optimization des performances et constaté que cela 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, nous avons fait une requête sur la table de base, puis nous avons joint la vue (qui inclut la table de base) et elle fonctionne très bien.

Je suggérerais d'avoir quelques stratégies simples et de les appliquer à des requêtes de haute valeur qui sont en train de bouger.

 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 count à 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) . Vérifiez le lien

Renvoie le nombre total d'loggings de SQL Server lors de l'utilisation de ROW_NUMBER

UPD:

Pour une seule requête, vérifiez 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 une logique comme celle-ci

 Between ((@pagenumber-1)*(@pagesize))+1 and (@pagenumber *@pagesize) 

Il y a plusieurs façons de parvenir à la pagination: j'espère que cette information vous sera utile ainsi qu'aux autres.

Exemple 1: using offset-fetch next clause. 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