簡(jiǎn)單談基于SQL SERVER 分頁(yè)存儲(chǔ)過程的演進(jìn)

時(shí)間：2018-11-30 15:34:01

關(guān)鍵字： server sql 存儲(chǔ)

手機(jī)看文章

掃描二維碼
隨時(shí)隨地手機(jī)看文章

[導(dǎo)讀]針對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)在UI界面上的分頁(yè)是老生常談的問題了，網(wǎng)上很容易找到各種“通用存儲(chǔ)過程”代碼，而且有些還定制查詢條件，看上去使用很方便。筆者打算通過本文也來簡(jiǎn)單談一下基于SQL SERVER 2000的

針對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)在UI界面上的分頁(yè)是老生常談的問題了，網(wǎng)上很容易找到各種“通用存儲(chǔ)過程”代碼，而且有些還定制查詢條件，看上去使用很方便。筆者打算通過本文也來簡(jiǎn)單談一下基于SQL SERVER 2000的分頁(yè)存儲(chǔ)過程，同時(shí)談?wù)凷QL SERVER 2005下分頁(yè)存儲(chǔ)過程的演進(jìn)。

在進(jìn)行基于UI顯示的數(shù)據(jù)分頁(yè)時(shí)，常見的數(shù)據(jù)提取方式主要有兩種。第一種是從數(shù)據(jù)庫(kù)提取所有數(shù)據(jù)然后在系統(tǒng)應(yīng)用程序?qū)舆M(jìn)行數(shù)據(jù)分頁(yè)，顯示當(dāng)前頁(yè)數(shù)據(jù)。第二種分頁(yè)方式為從數(shù)據(jù)庫(kù)取出需要顯示的一頁(yè)數(shù)據(jù)顯示在UI界面上。

以下是筆者對(duì)兩種實(shí)現(xiàn)方式所做的優(yōu)缺點(diǎn)比較，針對(duì)應(yīng)用程序編寫，筆者以.NET技術(shù)平臺(tái)為例。

類別
?SQL語(yǔ)句
?代碼編寫
?設(shè)計(jì)時(shí)
?性能
?
第一種
?語(yǔ)句簡(jiǎn)單，兼容性好
?很少
?完全支持
?數(shù)據(jù)越大性能越差
?
第二種
?看具體情況
?較多
?部分支持
?良好，跟SQL語(yǔ)句有關(guān)
?

?
對(duì)于第一種情況本文不打算舉例，第二種實(shí)現(xiàn)方式筆者只以兩次TOP方式來進(jìn)行討論。
在編寫具體SQL語(yǔ)句之前，定義以下數(shù)據(jù)表。
數(shù)據(jù)表名稱為：Production.Product。Production為SQL SERVER 2005中改進(jìn)后的數(shù)據(jù)表架構(gòu)，對(duì)舉例不造成影響。
包含的字段為：

列名
?數(shù)據(jù)類型
?允許空
?說明
?
ProductID
?Int
?
?產(chǎn)品ID，PK。
?
Name
?Nvarchar(50)
?
?產(chǎn)品名稱。
?

不難發(fā)現(xiàn)以上表結(jié)構(gòu)來自SQL SERVER 2005 樣例數(shù)據(jù)庫(kù)AdventureWorks的Production.Product表，并且只取其中兩個(gè)字段。?
分頁(yè)相關(guān)元素：
PageIndex – 頁(yè)面索引計(jì)數(shù)，計(jì)數(shù)0為第一頁(yè)。
PageSize – 每個(gè)頁(yè)面顯示大小。
RecordCount – 總記錄數(shù)。
PageCount – 頁(yè)數(shù)。

對(duì)于后兩個(gè)參數(shù)，筆者在存儲(chǔ)過程中以輸出參數(shù)提供。
?

1．SQL SERVER 2000中的TOP分頁(yè)
CREATE PROCEDURE [Zhzuo_GetItemsPage]
??? @PageIndex INT, /*@PageIndex從計(jì)數(shù),0為第一頁(yè)*/
??? @PageSize? INT, /*頁(yè)面大小*/
??? @RecordCount INT OUT, /*總記錄數(shù)*/
??? @PageCount INT OUT /*頁(yè)數(shù)*/

/*獲取記錄數(shù)*/
SELECT @RecordCount = COUNT(*) FROM Production.Product
/*計(jì)算頁(yè)面數(shù)據(jù)*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/*TOP記錄數(shù)*/
DECLARE @TOPCOUNT INT
SET @TOPCOUNT = @RecordCount - @PageSize * @PageIndex

DECLARE @SQLSTR NVARCHAR(1000)

IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
??? SET @SQLSTR =N'SELECT TOP '+STR(@PageSize)+
??? 'ProductID,Name FROM Production.Product ORDER BY ProductID DESC'
END
ELSE
BEGIN
??? IF @PageIndex = @PageCount - 1
??? BEGIN
?????? SET @SQLSTR =N'SELECT * FROM ( SELECT TOP ' + STR(@TOPCOUNT) +
?????? 'ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T ORDER BY ProductID DESC'
??? END
??? ELSE
??? BEGIN
?????? SET @SQLSTR =N' SELECT TOP '+STR(@PageSize)+'* FROM (SELECT TOP ' + STR(@TOPCOUNT) +
?????? 'ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T ORDER BY ProductID DESC'
??? END
END

/*執(zhí)行*/
EXEC (@SQLSTR)

以上存儲(chǔ)過程對(duì)頁(yè)數(shù)進(jìn)行判斷，如果是第一頁(yè)或最后一頁(yè)，進(jìn)行特殊處理。其他情況使用2次TOP翻轉(zhuǎn)。其中排序條件為ProductID倒序。最后通過EXECUTE執(zhí)行SQL字符串拼串。

2．SQL SERVER 2005中的TOP分頁(yè)
CREATE PROCEDURE [dbo].[Zhzuo_GetItemsPage2005TOP]
??? @PageIndex INT,
??? @PageSize? INT,
??? @RecordCount INT OUT,
??? @PageCount INT OUT
AS

/*基于SQL SERVER 2005 */
IF @PageIndex = 0 OR @PageCount <= 1
BEGIN
??? SELECT TOP(@PageSize) ProductID,Name FROM Production.Product ORDER BY ProductID DESC
END
ELSE
BEGIN
??? IF @PageIndex = @PageCount - 1
??? BEGIN
?????? SELECT * FROM ( SELECT TOP(@TOPCOUNT) ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T
?????? ORDER BY ProductID DESC
??? END
??? ELSE
??? BEGIN
?????? SELECT TOP(@PageSize) * FROM (SELECT TOP(@TOPCOUNT) ProductID,Name FROM Production.Product ORDER BY ProductID ASC) T
?????? ORDER BY ProductID DESC
??? END
END

以上存儲(chǔ)過程是使用2005的TOP (表達(dá)式) 新功能，避免了字符串拼串，使結(jié)構(gòu)化查詢語(yǔ)言變得簡(jiǎn)潔。實(shí)現(xiàn)的為同樣的功能。

3．SQL SERVER 2005中的新分頁(yè)
CREATE PROCEDURE [dbo].[Zhzuo_GetItemsPage2005]
??? @PageIndex INT,
??? @PageSize? INT,
??? @RecordCount INT OUT,
??? @PageCount INT OUT
AS

/*獲取記錄數(shù)*/
SELECT @RecordCount = COUNT(*) FROM Production.Product
/*計(jì)算頁(yè)面數(shù)據(jù)*/
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
/* 基于SQL SERVER 2005 */
SELECT SerialNumber,ProductID,Name FROM
(SELECT ProductID,Name,ROW_NUMBER() OVER (ORDER BY ProductID DESC) AS SerialNumber FROM Production.Product ) AS T
WHERE T.SerialNumber > (@PageIndex * @PageSize)? and T.SerialNumber <= ((@PageIndex+1) * @PageSize)
?

第三個(gè)存儲(chǔ)過程使用2005下新的功能，實(shí)現(xiàn)的分頁(yè)存儲(chǔ)過程功能更加簡(jiǎn)單明了，而且更加容易理解。注意這里的ProductID為主鍵,根據(jù)ProductID進(jìn)行排序生成ROW_NUMBER，通過ROW_NUMBER來確定具體的頁(yè)數(shù)。

通過對(duì)三個(gè)分頁(yè)存儲(chǔ)過程的比較，可見SQL SERVER 的TSQL 語(yǔ)言對(duì)分頁(yè)功能的支持進(jìn)步不少。使分頁(yè)實(shí)現(xiàn)趨向于簡(jiǎn)單化。

本文來自CSDN博客，轉(zhuǎn)載請(qǐng)標(biāo)明出處：http://blog.csdn.net/zhzuo/archive/2006/09/30/1313274.aspx

SQL分頁(yè)語(yǔ)句有關(guān)分頁(yè) SQL 的資料很多，有的使用存儲(chǔ)過程，有的使用游標(biāo)。本人不喜歡使用游標(biāo)，我覺得它耗資、效率低；使用存儲(chǔ)過程是個(gè)不錯(cuò)的選擇，因?yàn)榇鎯?chǔ)過程是經(jīng)過預(yù)編譯的，執(zhí)行效率高，也更靈活。先看看單條 SQL 語(yǔ)句的分頁(yè) SQL 吧。

方法1：
適用于 SQL Server 2000/2005
SELECT?TOP?頁(yè)大小?*
FROM?table1
WHERE?id?NOT?IN
??????????(
??????????SELECT?TOP?頁(yè)大小*(頁(yè)數(shù)-1) id?FROM?table1?ORDER?BY?id
??????????)
ORDER?BY?id
方法2：
適用于 SQL Server 2000/2005
SELECT?TOP?頁(yè)大小?*
FROM?table1
WHERE?id?>
??????????(
??????????SELECT?ISNULL(MAX(id),0)?
????????? FROM?
??????????????? (
??????????????? SELECT?TOP?頁(yè)大小*(頁(yè)數(shù)-1) id?FROM?table1?ORDER?BY?id
??????????????? )?A
??????????)
ORDER?BY?id
方法3：
適用于 SQL Server 2005
SELECT?TOP?頁(yè)大小?*?
FROM?
????????(
????????SELECT?ROW_NUMBER()?OVER?(ORDER?BY?id)?AS?RowNumber,*?FROM?table1
????????)?A
WHERE?RowNumber?>?頁(yè)大小*(頁(yè)數(shù)-1)

說明，頁(yè)大小：每頁(yè)的行數(shù)；頁(yè)數(shù)：第幾頁(yè)。使用時(shí)，請(qǐng)把“頁(yè)大小”和“頁(yè)大小*(頁(yè)數(shù)-1)”替換成數(shù)字。

其它的方案：如果沒有主鍵，可以用臨時(shí)表，也可以用方案三做，但是效率會(huì)低。
建議優(yōu)化的時(shí)候，加上主鍵和索引，查詢效率會(huì)提高。

通過SQL 查詢分析器，顯示比較：我的結(jié)論是:
分頁(yè)方案二：(利用ID大于多少和SELECT TOP分頁(yè)）效率最高，需要拼接SQL語(yǔ)句
分頁(yè)方案一：(利用Not In和SELECT TOP分頁(yè))?? 效率次之，需要拼接SQL語(yǔ)句
分頁(yè)方案三：(利用SQL的游標(biāo)存儲(chǔ)過程分頁(yè))??? 效率最差，但是最為通用

分類: 數(shù)據(jù)庫(kù)