CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqlsql-server-2012
Voucik
Voucik

Reputation: 103

Sql best way to pagination

I have #temp table and I need paginate all data in this table. How best way to table pagination?

create table #temp (
    Id int
    ,SomeName nvarchar(100)
)

create table #tempPage (
    Id int
    ,SomeName nvarchar(100)
    ,PageIndex int
)
--Test data
insert #temp (Id, SomeName) values
(1,'A'),
(2,'B'),
(3,'C'),
(4,'D'),
(5,'F'),
(6,'G'),
(7,'H'),
(8,'A1'),
(9,'B1'),
(10,'C1'),
(11,'D1'),
(12,'F1'),
(13,'G1'),
(14,'H1');

--Page size
declare @PageSize int = 5

--Max page count
declare @MaxPages float = (
    select
        case when count(Id)%@PageSize>0 then count(Id)/@PageSize+1 else count(Id)/@PageSize end
    from #temp
)

declare @PageFrom int = 0

declare @CurrentPage int = 1

while @CurrentPage <= @MaxPages
begin
    insert #tempPage (Id, SomeName, PageIndex)
    SELECT
        Id, SomeName, @CurrentPage
    FROM #temp
    ORDER BY id OFFSET @PageFrom ROWS
    FETCH NEXT @PageSize ROWS ONLY;
    set @PageFrom = @PageFrom + @PageSize
    set @CurrentPage = @CurrentPage + 1

end

select * from #tempPage

drop table #temp
drop table #tempPage

Result:

enter image description here

It's work very slow on big data. I use Ms Sql 2012.

Upvotes: 0

Views: 12446

Answers (3)

Avi Siboni
Avi Siboni

Reputation: 804

You can use the OFFSET & FETCH NEXT feature, The OFFSET keyword brings only from row_number and FETCH NEXT brings until. For an instance:

 USE AdventureWorks2008R2
    GO
    SELECT 
      BusinessEntityID
      ,PersonType
     ,FirstName + ' ' + MiddleName + ' ' + LastName 
    FROM Person.Person
     ORDER BY BusinessEntityID ASC
      OFFSET 100 ROWS 
      FETCH NEXT 5 ROWS ONLY
    GO

In that way you can make use of parameters for pagination, for instance:

SELECT
  BusinessEntityID
 ,PersonType
 ,FirstName + ' ' + MiddleName + ' ' + LastName 
FROM Person.Person
 ORDER BY BusinessEntityID
  OFFSET (@PageNo - 1) * @RowCountPerPage ROWS
  FETCH NEXT @RowCountPerPage ROWS ONLY
GO

For deeper understanding and performance you can read this - Pagination with OFFSET / FETCH : A better way article

Upvotes: 6

xQbert
xQbert

Reputation: 35343

Set based operations perform better. Avoid row by row processing.

We can use row_number assigned by the database and divide by the number of records we want per page to generate a page index. If we truncate/(round and eliminate decimals) we get the desired page index.

Something Like:

SELECT ID
    , SomeName
    , round(ROW_NUMBER() OVER(ORDER BY SomeName ASC)/5,0,1) AS PageIndex
FROM #temp
ORDER BY PageIndex, SomeName
  • 5 represents number of records per "page"
  • 0 as we don't care about the decimals but we don't want rounding to occur before truncating the decimals.
  • 1 to truncate to 0 decimals w/o rounding.

I assume you know you could wrap this in a CTE and add a where clause to get specific pages desired

Upvotes: 1

Raihan
Raihan

Reputation: 407

As you are looking for the best approach for pagination and you correctly said your solution is slow for large number of data you have to get one page of data at a time based on the user request.

When the user click page 2 you will get records for page 2 only not all.

Here is the sample code for this

declare @PageNumber int = 2; -- just an example, will come from parameter
declare @PageTo int = @PageSize * @PageNumber;
declare @PageFrom int = @PageTo - @PageSize;

    SELECT
        Id, SomeName, @PageNumber
    FROM #temp
    WHERE Id > @PageFrom AND Id <= @PageTo

drop table #temp

Upvotes: 0

Related Questions