sql分頁算法

轉自:http://www.knowsky.com/339541.html
分頁算法，是Web開發人員很關心的一個問題，幾乎每個數據庫相關的應用都要涉及到分頁算法，有很多人寫過這方面的文檔，似乎沒有什么好討論的了；但實際上換一種數據的表現形式，就可以采用一種更好的分頁算法，那我們現在就看看還能不能做得更好。

先說說已有的一些方法。
一是通過 DataGrid 分頁，這種方式最簡單，但效率不高，需要讀取所需數據前面的所有數據。
二是通過指定起始記錄和記錄數的 DbDataAdapter.Fill 來填充 DataSet 的分頁方式，這種方式也簡單，但同樣效率不高，也需要讀取所需數據前面的所有數據。
三是通過多個 select top 和多次排序，從表的中間選取需要的記錄；為了使相鄰頁的數據不重復，需要使用 not in，會導致在選擇大數據量的表格的尾部數據時，數據庫的性能會有很大的降低。

假設我們換一種表格的展現形式，就以傳統的 C/S 應用下的帶滾動條的 Grid 展現方式為例；其實這種方式才是數據庫表格最合適的展現方式，而 Web 應用下常用的 1,2,3....的頁碼連接方式或上一頁、下一頁按鈕的頁碼瀏覽條方式，都是不得已而為之，因為不能通過簡單的技術，在 Web 應用下實現帶滾動條的 Grid。

數據庫的表格都是帶有主鍵的，以區分表格中不同的記錄；用戶界面上的 Grid 里的數據從邏輯上也是有主鍵的，否則數據會有歧義，但大多數的應用，沒有設置，也無法知道所讀出數據的主鍵；即使少數應用設置了，也知道所讀數據的主鍵，但并沒有將其應用到分頁中；其實只要知道了所讀數據的主鍵，就可以非常容易的進行分頁。

首頁的算法很簡單
select top 頁大小 * from 表名 order by 主鍵

對于帶滾動條的表格，數據是一頁一頁順序滾動，即使拖動滾動條，也可以一頁頁滾動到所選的位置，當拖動到一個新頁時的算法為
select top 頁大小 * from 表名 where 主鍵 > 上一頁末記錄的主鍵 order by 主鍵

如果采用了緩存的方式，所有的數據都只需要下載一次，只有滾動到尾部時，才下載新的數據。

這種算法要求知道 Grid 中數據的主鍵，并將主鍵的數據應用到分頁；對于多主鍵和排序的表格，算法是一樣的，只是語句復雜了一些。不但可以從首頁開始，也可以從末頁開始，向前翻滾。
這個算法的性能沒有問題，對于不論多大的表，選擇那個位置的記錄都是一樣的，比較適合于用首頁、上一頁、下一頁和末頁的分頁選擇方式，更適合于帶滾動條的 Grid ；不適合于指定頁碼的分頁。

采用該算法的帶滾動條的 Grid 可以參考我們的演示

請您在提出疑問時，先考慮兩點，否則可能認識不到這個算法和我們系統結合的優勢：
第一：傳統的 C/S 應用的表格和Web上的分頁表格，誰更便利。
第一，我們實現的帶滾動條的表格，和傳統的 C/S 應用的表格的差別有多大。

對回復的解釋：
有的回復提出無法實現“跳轉到xx頁”這樣的操作。
可我們想想，在 C/S 的應用環境下，如果誰用這種頁面跳轉的方式，大家肯定會覺得怪異。
而我們實現的帶滾動條的 Grid 在局域網環境下，速度幾乎和以前的 C/S 應用差不多，在廣域網的情況下，速度也很快。
Web 應用的“跳轉到XX頁的操作”實際上是不得已而為之，如果能實現 傳統的 C/S 應用的Grid，為什么還要用這個呢？

SQL Server數據庫三種分頁方案詳盡分析
（轉自： http://www.xker.com/page/e2007/0718/27883.html ）
本文采用三種分頁辦法，最后對三種分頁辦法分析了其優缺點。

建立表

CREATE TABLE [TestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

插入數據：(2萬條，用更多的數據測試會明顯一些)

SET IDENTITY_INSERT TestTable ON

declare @i int
set @i=1
while @i<=20000
begin
??? insert into TestTable([id], FirstName, LastName, Country,Note)
values(@i, 'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX')
??? set @i=@i+1
end

SET IDENTITY_INSERT TestTable OFF

分頁方案一：

(利用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 XiaoZhengGe
@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 查詢分析器，顯示比較：我的結論是:

分頁方案二：(利用ID大于多少和SELECT TOP分頁）效率最高，需要拼接SQL語句。

分頁方案一：(利用Not In和SELECT TOP分頁)效率次之，需要拼接SQL語句。

分頁方案三：(利用SQL的游標存儲過程分頁)效率最差，但是最為通用。

sql分頁算法