OFFSET ROWS FETCH NEXT 工作机制及存储过程查询性能疑问

Great question—this is a common pain point with pagination in SQL Server, especially when dealing with large tables. Let’s break this down clearly:

Core Answer #

Your OFFSET/FETCH query does NOT first fetch all rows from dbo.vehicle and then trim them down to @nb_elements rows. SQL Server’s query optimizer is designed to avoid unnecessary work, so it will try to generate an execution plan that only retrieves the exact rows you need.

That said, your performance issues are likely coming from a different bottleneck: the ORDER BY date clause.

Why the Performance Hit Happens #

Here’s what’s actually happening under the hood:

• If the date column does not have an index, SQL Server has to scan the entire table to collect all date values, sort them, and then jump to the position specified by OFFSET to fetch the next @nb_elements rows. This full-table scan + sort is what’s killing your performance, especially on large datasets.
• If the date column has a properly configured index, the optimizer can leverage the ordered structure of the index to directly jump to the starting row of your page, without scanning or sorting the entire table. It will only read the exact number of rows needed for your page.

Fixes to Boost Performance #

Try these steps to resolve your performance issues:

• Add an index on the date column: A nonclustered index sorted by date will eliminate the expensive sort operation. If you’re using SELECT *, create a covering index that includes all columns you need (or all columns if you must use *, though this is not ideal):

  CREATE NONCLUSTERED INDEX IX_Vehicle_Date ON dbo.vehicle(date)
  INCLUDE (column1, column2, column3); -- List all columns from your SELECT
  

• Avoid SELECT *: Explicitly list only the columns you need. This reduces the size of your covering index and minimizes the data SQL Server has to read.
• Switch to key-set pagination for large page numbers: If you’re frequently paginating to high page numbers (e.g., @num_pages is large), OFFSET/FETCH will still need to skip thousands of rows. Instead, use a range-based query with your date column (and a unique key like id to handle ties):

  SELECT * FROM dbo.vehicle
  WHERE date > @last_page_max_date 
    AND id > @last_page_max_id -- Use unique key to avoid duplicates if dates are same
  ORDER BY date, id
  FETCH NEXT @nb_elements ROWS ONLY;
  

  This way, SQL Server can directly seek to the starting point of your next page without skipping rows.

内容的提问来源于stack exchange，提问作者PierreThb