三種 SQL 分頁法效率分析

前提條件

資料表主鍵必須為識別欄位，[ID] int IDENTITY (1,1)。

方案一：利用 NOT IN 和 SELECT TOP 分頁

SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
    (SELECT TOP 20 id
     FROM TestTable
     ORDER BY id))
ORDER BY ID

SELECT TOP 頁大小 *
FROM TestTable
WHERE (ID NOT IN
    (SELECT TOP 頁大小*頁數 id
     FROM 表
     ORDER BY id))
ORDER BY ID

方案二：利用 ID 大於多少和 SELECT TOP 分頁

SELECT TOP 10 *
FROM TestTable
WHERE (ID >
    (SELECT MAX(id)
     FROM (SELECT TOP 20 id
           FROM TestTable
           ORDER BY id) AS T))
ORDER BY ID

SELECT TOP 頁大小 *
FROM TestTable
WHERE (ID >
    (SELECT MAX(id)
     FROM (SELECT TOP 頁大小*頁數 id
           FROM 表
           ORDER BY id) AS T))
ORDER BY ID

方案三：利用 SQL 游標預存程序分頁

CREATE PROCEDURE SqlPager
    @sqlstr NVARCHAR(4000), -- 查詢字串
    @currentpage INT,       -- 第 N 頁
    @pagesize INT           -- 每頁行數
AS
    SET NOCOUNT ON
    DECLARE @P1 INT,        -- P1 是游標 ID
            @rowcount INT
    EXEC sp_cursoropen @P1 OUTPUT, @sqlstr, @scrollopt=1, @ccopt=1, @rowcount=@rowcount OUTPUT
    SELECT CEILING(1.0*@rowcount/@pagesize) AS 總頁數 -- ,@rowcount AS 總行數, @currentpage AS 當前頁
    SET @currentpage = (@currentpage-1)*@pagesize + 1
    EXEC sp_cursorfetch @P1, 16, @currentpage, @pagesize
    EXEC sp_cursorclose @P1
    SET NOCOUNT OFF

其他方案

若無主鍵，可用暫存表或方案三，但效率較低。建議優化時加上主鍵與索引，查詢效率會提高。

效率比較結論

透過 SQL 查詢分析器比較，結論如下：

1. 方案二（利用 ID 大於多少和 SELECT TOP 分頁）效率最高，需拼接 SQL 語句。
2. 方案一（利用 NOT IN 和 SELECT TOP 分頁）效率次之，需拼接 SQL 語句。
3. 方案三（利用 SQL 游標預存程序分頁）效率最差，但最為通用。