SQL Server query with pagination and count
Assuming you are using MSSQL 2012, you can use Offset and Fetch
which cleans up server-side paging greatly. We've found performance is fine, and in most cases better. As far as getting the total column count, just use the window function below inline...it will not include the limits imposed by 'offset' and 'fetch'.
For Row_Number, you can use window functions the way you did, but I would recommend that you calculate that client side as (pagenumber*pagesize + resultsetRowNumber), so if you're on the 5th page of 10 results and on the third row you would output row 53.
When applied to an Orders table with about 2 million orders, I found the following:
FAST VERSION
This ran in under a second. The nice thing about it is that you can do your filtering in the common table expression once and it applies both to the paging process and the count. When you have many predicates in the where clause, this keeps things simple.
declare @skipRows int = 25, @takeRows int = 100, @count int = 0;WITH Orders_cte AS ( SELECT OrderID FROM dbo.Orders)SELECT OrderID, tCountOrders.CountOrders AS TotalRowsFROM Orders_cte CROSS JOIN (SELECT Count(*) AS CountOrders FROM Orders_cte) AS tCountOrdersORDER BY OrderIDOFFSET @skipRows ROWSFETCH NEXT @takeRows ROWS ONLY;
SLOW VERSION
This took about 10 sec, and it was the Count(*) that caused the slowness. I'm surprised this is so slow, but I suspect it's simply calculating the total for each row. It's very clean though.
declare @skipRows int = 25,@takeRows int = 100,@count int = 0SELECT OrderID, Count(*) Over() AS TotalRowsFROM Location.OrdersORDER BY OrderIDOFFSET @skipRows ROWSFETCH NEXT @takeRows ROWS ONLY;
CONCLUSION
We've gone through this performance tuning process before and actually found that it depended on the query, predicates used, and indexes involved. For instance, the second we introduced a view it chugged, so we actually query off the base table and then join up the view (which includes the base table) and it actually performs very well.
I would suggest having a couple of straight-forward strategies and applying them to high-value queries that are chugging.
DECLARE @pageNumber INT = 1 , @RowsPerPage INT = 20SELECT *FROM TableNameORDER BY Id OFFSET ( ( @pageNumber - 1 ) * @RowsPerPage ) ROWS FETCH NEXT @RowsPerPage ROWS ONLY;
What if you calculate the count beforehand?
declare @pagenumber int = 2;declare @pagesize int = 3;declare @total int;SELECT @total = count(*)FROM @tablewith query as( select name, ROW_NUMBER() OVER(ORDER BY name ASC) as line from @table)select top (@pagesize) name, @total total from querywhere line > (@pagenumber - 1) * @pagesize
Another way, is to calculate max(line)
. Check the link
Return total records from SQL Server when using ROW_NUMBER
UPD:
For single query, check marc_s's answer on the link above.
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