Ravindra Devrani

Pagination with offset is not a good approach for the large dataset. I ran a query on the dataset of 1 million records in the sql server 2022 and compared the result. First approach (LEFT) with offset and the second approach (RIGHT) without the offset.
Example: We are accessing the 5 records after 999995.
Problem with offset: Offset scans the 999995 rows the returns the next 5 rows. Offset have the overhead of scanning the rows.

The approach used in the right side, do not uses offset. We are using the seek approach there. We are fetching the top 5 records after the 999995. This approach is much faster, comparison results are shown in the result.
However, if your Ids are UUIDs then this approach won't work.

8 months ago | [YT] | 1