SQL Server 三種分頁方法比較實驗

每一個系統都避免不了列表頁，而列表頁一般都要求分頁顯示。雖然所有語言都提供了內部的分頁功能，但資料量一多，效率便非常勁差，尤其在B/S架構中。影響原因可以總結為兩個：
1、程式語言分頁，每次都要讀取資料庫中的所有相關資料，而只需顯示10、20條，大量資料並沒有用到。
2、如果伺服器或者用戶端提供了資料緩衝，可以稍微最佳化一點，但有一個很明顯的現象，沒有一個客戶需要一次看到那麼多資料，他可能只需要一條或者幾條資料。

所以注意到最佳化效能的程式員一般都不會使用程式語言進行分頁。分頁的任務由應用伺服器或者用戶端轉移到惡劣資料庫伺服器，而資料庫伺服器的分頁方式一般都是由SQL語句（可以術預存程序）實現，根據以往SQL Server的學習經驗，總結分頁SQL主要有三種。

1、使用兩個select和兩個order，雙倒序：比如你有尋找相關記錄中10001-10020的20條記錄。你先按照某欄位排序，尋找前10020條記錄，形成資料集，再進行倒序尋找前20條。這種方法比較簡單，但進行多欄位排序時，有時候會出現莫名奇妙的錯誤（可能我不夠經驗）。

2、使用兩個select，併產生臨時序號：一樣比如你有尋找相關記錄中10001-10020的20條記錄。你首先尋找所有相關資料，並增加新的列儲存記錄臨時序號，形成資料集，再根據尋找資料集，而條件增加了臨時序號在10001-10020之間。這種方法必須使用陌生的產生臨時序號函數，而產生臨時序號的函數只有SQL Serrver 2005 才支援。

3、建立暫存資料表，產生臨時ID：一樣比如你有尋找相關記錄中10001-10020的20條記錄。你可以首先建立一個只有自增ID和你需要尋找相關記錄的自己欄位的暫存資料表，並將你尋找記錄的主鍵填入暫存資料表。再查詢暫存資料表，增加條件臨時序號在10001-10020之間。最後刪除暫存資料表。這種方法最複雜，但可擴充的空間比較大，效能的最佳化的潛力也應該比較強。

下面在對這三種辦法進行實際例子說明：

實驗環境：
Intel(R) Core(TM)2 CPU 4300 @ 1.80GHz 1.80GHz 1.96G記憶體
Microsoft Windwos XP Professional SP2
Microsoft SQL Server Management Studio Express
還有一些亂七八糟的軟體和服務。

資料庫表Test，要12個欄位，一個自增ID（主鍵），一個smallint的type欄位和10個無關緊要的nchar(10)欄位。總記錄有700000條。

SQL語句如下：

----------------兩個select 加兩個order，雙倒序（27秒鐘）
select top 20 * from (select top 349980 * from Test where type >5 order by type desc) as temp order by type desc

----------------兩個select 產生臨時序號（1秒鐘）
select * from (
select * ,ROW_NUMBER() OVER(ORDER BY type,ID) AS RowRank from Test where type >5) as temp
where RowRank between 350001 and 350020

----------------暫存資料表，create＋insert＋select＋drop（3秒鐘）
----------------暫存資料表，create＋insert＋select（2秒鐘）
CREATE TABLE #PageIndexForUsers
(
    IndexID int IDENTITY (0, 1) NOT NULL,
    ID int
)
INSERT INTO #PageIndexForUsers (ID)
SELECT ID
FROM Test
WHERE type >5
ORDER BY type,ID

SELECT T.* FROM Test T, #PageIndexForUsers p
WHERE  p.ID = T.ID AND
       p.IndexId between 350000 and 350019

drop table #PageIndexForUsers

運行結果佔用了時間是第一種方式用了27秒，第二種用了1秒，第三種不加drop用了2秒，加drop用了3秒。下面的附件是資料庫檔案，大家有興趣可以下載試試。