Efficient way to paginate in sql server with 50M plus data (sql server 17) I was looking for efficient way of pagination in sql server 2017 so I came across following code:
select id, col_1, col_2, col_3
from table_name
where deleted_at is null
order by id 
offset @pagesize * (@pagenumber - 1) rows
fetch next @pagesize rows only option(recompile)

This works fine for small offset. But, as the offset value increase. It gradually starts to take a lot of time.
So, I decided to fetch data with something like id &gt; xx concepe. Something like
select id, col_1, col_2, col_3 
from table_name
where deleted_at is null
and id &gt; 50000000 and id &lt;= 50000050
order by id 

This code works lighting fast. However, this could not work perfectly if there are deleted data.
So, I tweak little bit and switch to Top method instead.
select Top(50) id, col_1, col_2, col_3
from table_name
where deleted_at is null
and id &gt; 50000000
order by id 

This seems quite close to get next 50 data in lighting speed. However, this too seems failed when you want to move from page 1 to page 3. You could not know how many data are deleted in between row 50 to row 100. So, this too seems failed for numeric pagination.
Now, I started to look pagination with top method and finally found:
SELECT  top(50) id, col_1, col_2, col_3
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY id) AS RowNum, id, col_1, col_2, col_3
          FROM      table_name
          where deleted_at is NULL
        ) AS RowConstrainedResult
WHERE   RowNum &gt;= 50000000
ORDER BY RowNum

But, even for this it took around 20-25 sec when the RowNum is around 50M.
How can I decrease this query time. AnyIdea?
NOTE:
I have already created an index as
create index IX_table_id_deleted_at on table_name(id asc,deleted_at) include(col_1, col_3, col_3) where deleted_at is null 

Also, I have tried with NOCOUNT ON as well as WITH(NOLOCK) but still the query time is around 11 sec
There is simply no cheap way to get the 12753rd page out of a 50M row result set.


  [offset .. fetch] works fine for small offset. But, as the offset value increase. It gradually starts to take a lot of time.


Which is normally fine, as you rarely page deep into a large result set.


  [The "Key Seek Method"] failed when you want to move from page 1 to page 3.


Yep.  But it's so much more efficient that you simply avoid skipping pages.