0

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

3

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.

  • Ok, I got it david what you are trying to say. Could you suggest some better approach to represent data in such scenario. – Aayush Dahal Dec 4 '18 at 15:42
  • Both of the approaches you suggest are fine. What actual problem are you trying to solve? – David Browne - Microsoft Dec 4 '18 at 15:52
0

You have ask same question directly indirectly so many times and even mark as answer in few places.

So why don't you throw your complete requirement or complete query.

See if you are looking for paging then your first query is only correct and rest query are wrong.

If this is your complete query then,

select id, col_1, col_2, col_3
from dbo.table_name with(nolock)
where deleted_at is null
order by id 
offset @pagesize * (@pagenumber - 1) rows
fetch next @pagesize rows only 

Create index IX_table_id_deleted_at  on table_name(id asc) include(col_1, col_3, col_3) where deleted_at is null

note : deleted_at is remove from bracket.

If this table is very important and such query is very frequently use then Creating History Table will tremendously help you.

Like keep all deleted_at is not null type of records in History table hereafter.

Offset will perform better than `Row_Number`.

What data type is id ? Each id contain how many rows (including both i.e. deleted is null and not null) ?

If History table is not acceptable then another good news !

Try ColumnStore Index

  • Still not any improvement – Aayush Dahal Dec 4 '18 at 15:39
  • @nep-user , read my edited and answer few question about history table and data type and how many rows each id contain. ? – KumarHarsh Dec 5 '18 at 4:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.