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 > xx concepe. Something like
select id, col_1, col_2, col_3
from table_name
where deleted_at is null
and id > 50000000 and id <= 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 > 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 >= 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
Best Answer
There is simply no cheap way to get the 12753rd page out of a 50M row result set.
Which is normally fine, as you rarely page deep into a large result set.
Yep. But it's so much more efficient that you simply avoid skipping pages.