SQL Server存储过程运行极慢求助：百万级数据分页优化

Alright, let's fix that agonizingly slow pagination stored procedure for your 10M+ row Giggerdata table—naive page-number-based pagination is almost always the villain here, so let's break down actionable, practical optimizations step by step:

1. Dump OFFSET-based pagination entirely—use Key Set Pagination instead #

The biggest issue with OFFSET @pageNumber * @pageSize is that for large page numbers, the database has to scan every single row before your target page just to "skip" them. For a 10M-row table, this gets exponentially slower as you go deeper into the dataset.

Instead, use a unique, ordered column (like your primary key, or a timestamp + PK for non-unique timestamps) to track your last position. Here's a revised stored procedure example:

CREATE PROCEDURE GetGiggerdataBatch
    @LastSeenPrimaryKey INT, -- Pass the PK of the last row from the previous batch
    @BatchSize INT
AS
BEGIN
    SET NOCOUNT ON; -- Reduces unnecessary network overhead
    
    -- Only select the columns you actually need (avoid SELECT *)
    SELECT TOP (@BatchSize)
        ID, Col1, Col2, Col3 -- List your required columns here
    FROM Giggerdata
    WHERE ID > @LastSeenPrimaryKey -- Start right after the last row from the prior batch
    ORDER BY ID ASC; -- Keep the order consistent
END

Your external service will need to track the last primary key from each batch instead of passing a page number—this lets the database jump directly to the starting point using indexes, no full scans required.

2. Optimize indexes for your pagination query #

• Ensure your sorting/filter column has a clustered index: If your primary key (like ID) is the clustered index (default for most databases), you're already set. If you're using a different column (e.g., CreatedDate), create a clustered index on it (or a non-clustered covering index if you can't change the clustered index).
• Create covering indexes if needed: If you're selecting columns not in your clustered index, build a non-clustered index that includes all the columns you need to return. For example:

  CREATE NONCLUSTERED INDEX IX_Giggerdata_CreatedDate_Covering
  ON Giggerdata (CreatedDate)
  INCLUDE (ID, Col1, Col2, Col3); -- Include every column you select in the proc
  

  This eliminates "key lookups" where the database has to jump back to the clustered index to fetch missing columns.

3. Tune the stored procedure for efficiency #

• Avoid SELECT *: Only fetch the columns your external service actually needs. This reduces data transfer size (helping stay under that 30MB API limit) and makes your covering indexes smaller/faster.
• Match parameter types to table columns: If your ID column is BIGINT, don't pass an INT parameter—implicit type conversions kill index usage.
• Consider OPTION (RECOMPILE) for variable batch sizes: If your BatchSize varies a lot, adding OPTION (RECOMPILE) at the end of your query tells the database to generate a fresh execution plan each time, which can help with performance. Just note that recompiling has a small overhead, so test before enabling it for every call.

4. Align batch size with your 30MB API limit smartly #

Instead of calculating batches based on page numbers, calculate the optimal BatchSize to hit that 30MB limit as closely as possible:

• First, estimate the average row size of your result set (e.g., if each row is ~3KB, 10,000 rows = ~30MB).
• Test different batch sizes (5k, 10k, 15k) to find the sweet spot—too large and you might hit memory/lock issues, too small and you're making too many API calls.
• If your rows vary significantly in size, you could even adjust the batch size dynamically based on the previous batch's actual size, but that's more complex.

5. Quick wins to rule out other bottlenecks #

• Update database statistics: Outdated stats can lead to terrible execution plans. Run UPDATE STATISTICS Giggerdata to refresh them.
• Check for blocking/locking: Use your database's monitoring tools (e.g., SQL Server Activity Monitor, MySQL SHOW PROCESSLIST) to see if other queries are locking the Giggerdata table while your pagination runs.
• Offload to a read replica: If your use case allows (i.e., you don't need real-time data), run the pagination queries against a read replica to take load off your primary database.

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