TOP N + NOT IN (SELECT TOP M ...):子查詢效能差、大偏移量極慢ROW_NUMBER() OVER (ORDER BY ...):SQL 2005+ 標準寫法,但大資料集排序成本高ALTER PROCEDURE [dbo].[dnt_getappinvitelist]
@uid INT,
@pageIndex INT,
@pageSize INT
AS
BEGIN
DECLARE @startRow INT, @endRow INT
SET @startRow = (@pageIndex - 1) * @pageSize
IF @pageIndex = 1
BEGIN
-- 首頁:直接取最新 N 筆
EXEC('
SELECT TOP ' + CAST(@pageSize AS VARCHAR) + '
[id],[typename],[appid],[type],[fromuid],[touid],[myml],[datetime],[hash]
FROM [dnt_myinvite]
WHERE [touid] = ' + CAST(@uid AS VARCHAR) + '
ORDER BY [id] DESC
')
END
ELSE
BEGIN
-- 非首頁:以上一頁最小 ID 為游標向後取
EXEC('
SELECT TOP ' + CAST(@pageSize AS VARCHAR) + '
[id],[typename],[appid],[type],[fromuid],[touid],[myml],[datetime],[hash]
FROM [dnt_myinvite]
WHERE [touid] = ' + CAST(@uid AS VARCHAR) + '
AND [id] < (
SELECT MIN([id]) FROM (
SELECT TOP ' + CAST(@startRow AS VARCHAR) + ' [id]
FROM [dnt_myinvite]
WHERE [touid] = ' + CAST(@uid AS VARCHAR) + '
ORDER BY [id] DESC
) AS T
)
ORDER BY [id] DESC
')
END
END
TOP @pageSize ORDER BY ID DESC,利用叢集索引掃描極快SELECT TOP @startRow ID ... ORDER BY ID DESC 取得第 @startRow 筆的 ID(即上一頁最後一筆 ID)MIN(ID) 取得該分頁邊界值WHERE ID < @boundary 直接定位到下一頁起始位置,避免掃描前 N 筆SELECT [id],[typename],[appid],[type],[fromuid],[touid],[myml],[datetime],[hash]
FROM [dnt_myinvite]
WHERE [touid] = @uid
ORDER BY [id] DESC
OFFSET (@pageIndex - 1) * @pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY;
OFFSET/FETCH 標準化、可讀性高、優化器支援良好,建議新專案優先採用。