SQL Server query with pagination and count

Question

I want to make a database query with pagination. So, I used a common-table expression and a ranked function to achieve this. Look at the example below.

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

Here, I can specify the @pagesize and @pagenumber variables to give me just the records that I want. However, this example (that comes from a stored procedure) is used to make a grid pagination in a web application. This web application requires to show the page numbers. For instance, if a have 12 records in the database and the page size is 3, then I'll have to show 4 links, each one representing a page.

But I can't do this without knowing how many records are there, and this example just gives me the subset of records.

Then I changed the stored procedure to return the count(*).

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

So, along with each line, it will show the total number of records. But I didn't like it.

My question is if there's a better way (performance) to do this, maybe setting the @total variable without returning this information in the SELECT. Or is this total column something that won't harm the performance too much?

Thanks

Answer 1

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 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;

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 = 0


SELECT 
    OrderID,
    Count(*) Over() AS TotalRows
FROM Location.Orders
ORDER BY OrderID
OFFSET @skipRows ROWS
FETCH 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.

Answer 2

DECLARE @pageNumber INT = 1  , 
        @RowsPerPage INT = 20

SELECT  *
FROM    TableName
ORDER BY Id
        OFFSET ( ( @pageNumber - 1 ) * @RowsPerPage ) ROWS
             FETCH NEXT @RowsPerPage ROWS ONLY;

Answer 3

What if you calculate the count beforehand?

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

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

Answer 4

@pagenumber=5
@pagesize=5

Create a common table expression and write logic like this

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

Answer 5

There are many way we can achieve pagination: I hope this information is useful to you and others.

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

Example 2: using row_number() function and between

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 )

I hope these will be helpful to all

Answer 6

I don't like other solutions for being too complex, so here is my version.

Execute three select queries in one go and use output parameters for getting the count values. This query returns the total count, the filter count, and the page rows. It supports sorting, searching, and filtering the source data. It's easy to read and modify.

Let's say you have two tables with one-to-many relationship, items and their prices changed over time so the example query is not too trivial.

create table shop.Items
(
    Id uniqueidentifier not null primary key,
    Name nvarchar(100) not null,
);

create table shop.Prices
(
    ItemId uniqueidentifier not null,
    Updated datetime not null,
    Price money not null,
    constraint PK_Prices primary key (ItemId, Updated),
    constraint FK_Prices_Items foreign key (ItemId) references shop.Items(Id)
);

Here is the query:

select @TotalCount = count(*) over()
from shop.Items i;

select @FilterCount = count(*) over()
from shop.Items i
outer apply (select top 1 p.Price, p.Updated from shop.Prices p where p.ItemId = i.Id order by p.Updated desc) as p
where (@Search is null or i.Name like '%' + @Search + '%')/**where**/;

select i.Id as ItemId, i.Name, p.Price, p.Updated
from shop.Items i
outer apply (select top 1 p.Price, p.Updated from shop.Prices p where p.ItemId = i.Id order by p.Updated desc) as p
where (@Search is null or i.Name like '%' + @Search + '%')/**where**/
order by /**orderby**/i.Id
offset @SkipCount rows fetch next @TakeCount rows only;

You need to provide the following parameters to the query:

• @SkipCount - how many records to skip, calculated from the page number.
• @TakeCount - how many records to return, calculated from or equal to the page size.
• @Search - a text to search for in some columns, provided by the grid search box.
• @TotalCount - the total number of records in the data source, the output parameter.
• @FilterCount - the number of records after the search and filtering operations, the output parameter.

You can replace /**orderby**/ comment with the list of columns and their ordering directions if the grid must support sorting the rows by columns. you get this info from the grid and translate it to an SQL expression. We still need to order the records by some column initially, I usually use ID column for that.

If the grid must support filtering data by each column individually, you can replace /**where**/ comment with an SQL expression for that.

If the user is not searching and filtering the data, but only clicks through the grid pages, this query doesn't change at all and the database server executes it very quickly.