SQLServer分頁存儲過程的一點心得

最近參與一個對原有MS SQLSERVER2000+ASP系統升級到.Net的開發項目。其中，針對某一個數據記錄較多的表的查詢用的比較多，而且該查詢的條件組合比較復雜，包括分頁，按特定字段排序，按特定條件查詢。

　　參考了netkillerbaseSQL Server 存儲過程的分頁，開始決定采用效率最高的“方案二”，即通過ID標識來比較大小，從而快速檢索出所需的記錄。

　　為了方便讀者，我在這裡簡單列出前面列出的參考文章中的3種分頁查詢存儲過程的核心T-SQL語句：

方案一：

SELECT TOP 頁大小 * 
FROM TestTable 
WHERE (ID NOT IN 
(SELECT TOP 頁大小*頁數 id 
FROM 表 
ORDER BY id)) 
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 

上文作者使用查詢分析器比較過3種方案，結論如下：
分頁方案二：(利用ID大於多少和SELECT TOP分頁）效率最高，需要拼接SQL語句
分頁方案一：(利用Not In和SELECT TOP分頁) 效率次之，需要拼接SQL語句
分頁方案三：(利用SQL的游標存儲過程分頁) 效率最差，但是最為通用

　　按照“方案二”寫好查詢存儲過程後，測試中發現按照某些字段排序時，會出現記錄遺漏的情況。經過分析表中的記錄發現，原來是因為該字段內的值有重復，即用來排序的字段不能作為標識來比較大小。

　　找到原因後，對方案二和方案一進行了一個整合。先使用方案二中的排序字段的大小比較，來初步篩選符合條件的記錄，然後再使用方案一中的ID標識來驗證找到記錄是否合法。最後得到的T-SQL語句類似如下：

SELECT TOP 頁大小 * 
FROM TestTable 
WHERE (ordercol >= SELECT MAX(ordercol) 
FROM (SELECT TOP 頁大小*頁數 ordercol 
FROM TestTable 
ORDER BY ordercol ))  and (ID NOT IN
(SELECT TOP 頁大小*頁數 id 
FROM TestTable 
ORDER BY ordercol)) 
ORDER BY ordercol 

　　這種方式繼承了“方案一”的缺點，即在記錄數相當大，而且頁碼靠後時，(SELECT TOP 頁大小*頁數 id
FROM TestTable ORDER BY ordercol)所得到的記錄集會消耗相當大的內存。但是，於此同時，前面先進行的比較判斷(ordercol >= SELECT MAX(ordercol) FROM (SELECT TOP 頁大小*頁數 ordercol FROM TestTable
ORDER BY ordercol ))所得到的記錄數量卻並不大，加上比較查詢的效率遠遠高於not in，所以最終的執行效率還是可以讓人接受的。

　　實際項目所使用的表中有1萬多條記錄，采用該分頁存儲過程查詢倒數前5頁，查詢執行時間低於1秒。而原來的ASP程序使用recordset的move方法，查詢執行時間超過2秒。