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