Your application hums along perfectly during development. Page loads are snappy, users navigate smoothly through records, and everything feels responsive. Then you deploy to production, and suddenly customers with large datasets are complaining about 12-second page loads on deeper pages.

Sound familiar? This is the classic pagination performance cliff that catches many developers off guard. The simple OFFSET/FETCH approach that worked beautifully with your test data of 1,000 records starts crawling when faced with real-world datasets of millions of rows.

Let’s explore why OFFSET/FETCH pagination hits a wall at scale and discover better strategies that keep your application fast, even when dealing with massive datasets.

The Classic OFFSET/FETCH Approach

Most developers start with OFFSET/FETCH because it’s intuitive and widely supported across database systems. Here’s a typical implementation:

-- Get page 6 (rows 51-60) from a products table
SELECT ProductId, ProductName, Price
FROM Products
ORDER BY ProductId
OFFSET 50 ROWS 
FETCH NEXT 10 ROWS ONLY;

This approach feels natural because it mirrors how we think about pagination: skip the first N rows, then take the next batch. It’s also dead simple to implement in your application code:

-- Dynamic pagination query
DECLARE @PageSize INT = 10;
DECLARE @PageNumber INT = 6;
DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;

SELECT ProductId, ProductName, Price
FROM Products
ORDER BY ProductId
OFFSET @Offset ROWS 
FETCH NEXT @PageSize ROWS ONLY;

The problem? This approach has two major flaws that become painfully obvious at scale:

Performance degradation on deep pages: When you request page 1000 of a dataset, SQL Server still needs to sort and count through the first 999 pages worth of data before returning your results. The deeper the page, the slower the query becomes.

Data consistency issues: If records are being inserted or deleted while users paginate through results, they might see duplicate items or miss records entirely. Imagine a user on page 5 when a new record gets inserted at the beginning of the dataset, suddenly all subsequent pages shift by one record.

Personal Experience: I once debugged a customer portal where users complained about “seeing the same product twice” while browsing. The culprit? New inventory being added to the system while customers were browsing, causing OFFSET pagination to shift all subsequent pages.

Keyset Pagination: A Better Approach for Large Datasets

Keyset pagination (also called cursor-based pagination) solves both problems by using the actual data values as navigation markers instead of counting rows:

-- Instead of OFFSET, use WHERE with the last seen ID
DECLARE @LastSeenId INT = 1250; -- Last ProductId from previous page
DECLARE @PageSize INT = 10;

SELECT TOP(@PageSize) ProductId, ProductName, Price
FROM Products
WHERE ProductId > @LastSeenId
ORDER BY ProductId;

This approach offers significant advantages:

Efficient index usage: Instead of scanning through thousands of rows to skip them, the database can jump directly to the starting point using an index seek. This means consistent performance regardless of how deep you paginate.

Stable results under concurrent updates: Since you’re filtering based on actual data values rather than row positions, insertions and deletions in other parts of the dataset won’t affect your current pagination sequence.

Here’s a more complete example showing how this might work in practice:

-- First page (no cursor needed)
SELECT TOP(10) ProductId, ProductName, Price, CreatedDate
FROM Products
ORDER BY ProductId;

-- Subsequent pages using the last ProductId as cursor
SELECT TOP(10) ProductId, ProductName, Price, CreatedDate
FROM Products
WHERE ProductId > @LastProductId  -- Cursor from previous page
ORDER BY ProductId;

The trade-off? You lose the ability to jump to arbitrary pages (like “go to page 47”). Instead, you can only navigate sequentially with “next” and “previous” buttons. For most modern applications, this is actually preferable from a UX perspective.

Advanced Strategies for Complex Scenarios

Real applications often need more sophisticated pagination approaches. Here are some advanced techniques:

Composite Key Pagination

When your sort order involves multiple columns, you need a more complex WHERE clause:

-- Pagination by CreatedDate, then ProductId for tie-breaking
SELECT TOP(10) ProductId, ProductName, CreatedDate
FROM Products
WHERE (CreatedDate > @LastCreatedDate) 
   OR (CreatedDate = @LastCreatedDate AND ProductId > @LastProductId)
ORDER BY CreatedDate, ProductId;

Covering Indexes for Pagination Queries

Create indexes that include all columns needed for your pagination queries:

-- Covering index for efficient pagination
CREATE NONCLUSTERED INDEX IX_Products_Pagination
ON Products (CreatedDate, ProductId)
INCLUDE (ProductName, Price);

Window Functions for Analytics

For reporting scenarios where you need both pagination and total counts:

SELECT 
    ProductId, 
    ProductName, 
    Price,
    ROW_NUMBER() OVER (ORDER BY ProductId) as RowNumber,
    COUNT(*) OVER () as TotalRows
FROM Products
ORDER BY ProductId
OFFSET @Offset ROWS 
FETCH NEXT @PageSize ROWS ONLY;

This gives you both the paginated results and the total count in a single query, though it still suffers from the deep pagination performance issues.

When OFFSET/FETCH Is Still the Right Choice

Despite its limitations, OFFSET/FETCH remains the best choice for certain scenarios:

Admin interfaces and dashboards: When you’re dealing with smaller datasets (under 100K rows) and need the flexibility to jump to specific pages, OFFSET/FETCH is often simpler and perfectly adequate.

Reporting applications: Business intelligence tools often require the ability to navigate to arbitrary pages for data exploration, making the random access capability of OFFSET/FETCH essential.

Low-volume applications: If your application serves a small user base with modest data requirements, the complexity of implementing keyset pagination might not be justified.

Expert Insight: I’ve seen development teams spend weeks implementing sophisticated keyset pagination for applications that would never exceed 50,000 total records. Sometimes the simple solution is the right solution. Always measure before optimizing.

Production-Ready Pagination Checklist

Before choosing your pagination strategy, consider these key factors:

Dataset size expectations: If you expect millions of rows, keyset pagination is almost always the better choice. For smaller datasets, OFFSET/FETCH might be sufficient.

Data stability requirements: If you need stable pagination results while data is being modified concurrently, avoid OFFSET/FETCH in favor of keyset pagination.

Navigation patterns: Do users need to jump to specific pages (like search results), or is sequential navigation sufficient? This determines whether you can use keyset pagination or need to stick with OFFSET/FETCH.

Index strategy: Regardless of your pagination approach, ensure you have appropriate indexes. For OFFSET/FETCH, this means indexes on your ORDER BY columns. For keyset pagination, you need indexes on your cursor columns.

-- Essential indexes for pagination performance
CREATE INDEX IX_Products_Id ON Products (ProductId);
CREATE INDEX IX_Products_CreatedDate ON Products (CreatedDate, ProductId);

Query monitoring: Set up performance monitoring to track pagination query execution times. Establish alerts for queries that exceed acceptable thresholds, especially for deeper pages.

Making the Right Choice for Your Application

The key to successful pagination lies in understanding your specific requirements and constraints. OFFSET/FETCH isn’t inherently wrong, but it can become a significant bottleneck when dealing with large datasets or deep pagination scenarios.

Before committing to any pagination strategy, benchmark your queries with realistic data volumes. Test not just the first few pages, but also simulate deep pagination scenarios with hundreds or thousands of pages. Your pagination choice should be based on actual performance measurements, not assumptions.

Remember that pagination isn’t a one-size-fits-all problem. The right solution depends on your data scale, user behavior patterns, and application requirements. Choose the approach that best balances performance, complexity, and functionality for your specific use case.

References

Related Posts