亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

關于SQL分頁存儲過程的分析

系統 2003 0

建立一個 Web 應用,分頁瀏覽功能必不可少。這個問題是數據庫處理中十分常見的問題。經典的數據分頁方法是:ADO 紀錄集分頁法,也就是利用ADO自帶的分頁功能(利用游標)來實現分頁。但這種分頁方法僅適用于較小數據量的情形,因為游標本身有缺點:游標是存放在內存中,很費內存。游標一建立,就將相關的記錄鎖住,直到取消游標。游標提供了對特定集合中逐行掃描的手段,一般使用游標來逐行遍歷數據,根據取出數據條件的不同進行不同的操作。而對于多表和大表中定義的游標(大的數據集合)循環很容易使程序進入一個漫長的等待甚至死機。

更重要的是,對于非常大的數據模型而言,分頁檢索時,如果按照傳統的每次都加載整個數據源的方法是非常浪費資源的。現在流行的分頁方法一般是檢索頁面大小的塊區的數據,而非檢索所有的數據,然后單步執行當前行。

?

? 最早較好地實現這種根據頁面大小和頁碼來提取數據的方法大概就是“俄羅斯存儲過程”。這個存儲過程用了游標,由于游標的局限性,所以這個方法并沒有得到大家的普遍認可。

? 后來,網上有人改造了此存儲過程,下面的存儲過程就是結合我們的辦公自動化實例寫的分頁存儲過程:

? /* ***********************************************************

?*?Code?formatted?by?SoftTree?SQL?Assistant??v5.0.97
?*?Time:?2011-10-05?20:32:46
?*********************************************************** */

CREATE ? PROCEDURE ?page_t(
???? @pagesize ??? INT ,???? -- 頁面大小,如每頁存儲20條記錄
???? @pageindex ?? INT ? -- 當前頁碼
)
AS
???? SET ?NOCOUNT? ON
????
???? BEGIN
???????? DECLARE ? @indextable ?????? TABLE (id? INT ? IDENTITY ( 1 ,? 1 ),?nid? INT )? -- 定義表變量
???????? DECLARE ? @PageLowerBound ?? INT ? -- 定義此頁的底碼
???????? DECLARE ? @PageUpperBound ?? INT ? -- 定義此頁的頂碼
???????? SET ? @PageLowerBound ? = ?( @pageindex ? - 1 )? * ? @pagesize
???????? SET ? @PageUpperBound ? = ? @PageLowerBound ? + ? @pagesize
???????? SET ? ROWCOUNT ? @PageUpperBound
???????? INSERT ? INTO ? @indextable
??????????(
????????????nid
??????????)
???????? SELECT ?id
???????? FROM ???tt?
???????? -- where?id?>dateadd(day,-365,getdate())?order?by?id?desc
???????? SELECT ?O.id
???????? FROM ???tt?O,
??????????????? @indextable ?t
???????? WHERE ??O.id? = ?t.nid
??????????????? AND ?t.id? > ? @PageLowerBound
??????????????? AND ?t.id? <= ? @PageUpperBound
???????? ORDER ? BY
???????????????t.id
???? END
????
???? SET ?NOCOUNT? OFF
????

?

-- --exec?page_t?100,2

?

?

以上存儲過程運用了SQL SERVER的最新技術――表變量。應該說這個存儲過程也是一個非常優秀的分頁存儲過程。當然,在這個過程中,您也可以把其中的表變量寫成臨時表:CREATE TABLE #Temp。但很明顯,在SQL SERVER中,用臨時表是沒有用表變量快的。所以筆者剛開始使用這個存儲過程時,感覺非常的不錯,速度也比原來的ADO的好。但后來,我又發現了比此方法更好的方法。

?

? 從tt表中取出第 n 條到第 m 條的記錄:

? DECLARE ? @m ? INT ?

DECLARE ? @n ? INT
SET ? @m = 10000
set ? @n = 2
SELECT ? TOP ?( @m - @n + 1 )? * ?? FROM ?tt?
WHERE ?(id? NOT ? IN ??( SELECT ? TOP ?( @n - 1 )?id??? FROM ?tt))

id 為tt表的關鍵字

我當時看到這篇文章的時候,真的是精神為之一振,覺得思路非常得好。這個存儲過程也是目前較為流行的一種分頁存儲過程

? /* ***********************************************************

?*?Code?formatted?by?SoftTree?SQL?Assistant??v5.0.97
?*?Time:?2011-10-05?21:12:08
?*********************************************************** */

? CREATE ?? PROC ?pagination2(
????????????????????????????? @SQL ? NVARCHAR ( 4000 ),???? -- 不帶排序語句的SQL語句
????????????????????????????? @Page ? INT ,???? -- 頁碼
????????????????????????????? @RecsPerPage ? INT ,???? -- 每頁容納的記錄數
????????????????????????????? @ID ? VARCHAR ( 255 ),???? -- 需要排序的不重復的ID號
????????????????????????????? @Sort ? VARCHAR ( 255 )? -- 排序字段及規則
?????????????????????????)
AS

DECLARE ? @Str ? NVARCHAR ( 4000 )

SET ? @Str ? = ? ' SELECT?TOP? ' ? + ? CAST ( @RecsPerPage ? AS ? VARCHAR ( 20 ))? + ? ' ?*?FROM?
(
' ? + ? @SQL ? + ? ' )?T?WHERE?T. ' ? + ? @ID ? + ? ' ?NOT?IN?(SELECT?TOP? ' ? + ? CAST (( @RecsPerPage ? * ?( @Page ? - 1 ))? AS ? VARCHAR ( 20 ))
???? + ? ' ? ' ? + ? @ID ? + ? ' ?FROM?( ' ? + ? @SQL ? + ? ' )?T9?ORDER?BY? ' ? + ? @Sort ? + ? ' )?ORDER?BY? ' ? + ?
???? @Sort

PRINT ? @Str

EXEC ?sp_ExecuteSql? @Str
GO


-- ---pagination2?'SELECT?id?FROM?tt',2,100,'id','id'?


? ??

?其實,以上語句可以簡化為:

? SELECT ? ? TOP? 100 /* 頁大小 */ *

FROM ?tt? WHERE ?(ID? NOT ? IN ?( SELECT ? TOP ??( 100 /* 頁大小 */ ? * 0 /* 頁數 */ ?)id? FROM ?tt /* */ ? ORDER ? BY ?id))
ORDER ? BY ?ID

?

?

但這個存儲過程有一個致命的缺點,就是它含有NOT IN字樣。雖然我可以把它改造為:

?

SELECT ? TOP ? 100 /* 頁大小? */ ? *
FROM ???TT?a
WHERE ?? NOT ? EXISTS
???????(
??????????? SELECT ? *
??????????? FROM ???(
?????????????????????? SELECT ? TOP ( 100 /* 頁大小? */ ? * ? 0 /* 頁數 */ )? * ?
?????????????????????? FROM ???TT
?????????????????????? ORDER ? BY
?????????????????????????????id
??????????????????)?b
??????????? WHERE ??b.id? = ?a.id
???????)

ORDER ? BY ?id? ?

?

即,用not exists來代替not in,但我們前面已經談過了,二者的執行效率實際上是沒有區別的。既便如此,用TOP 結合NOT IN的這個方法還是比用游標要來得快一些。

?

雖然用not exists并不能挽救上個存儲過程的效率,但使用SQL SERVER中的TOP關鍵字卻是一個非常明智的選擇。因為分頁優化的最終目的就是避免產生過大的記錄集,而我們在前面也已經提到了TOP的優勢,通過TOP 即可實現對數據量的控制。

在分頁算法中,影響我們查詢速度的關鍵因素有兩點:TOP和NOT IN。TOP可以提高我們的查詢速度,而NOT IN會減慢我們的查詢速度,所以要提高我們整個分頁算法的速度,就要徹底改造NOT IN,同其他方法來替代它。

?

我們知道,幾乎任何字段,我們都可以通過max(字段)或min(字段)來提取某個字段中的最大或最小值,所以如果這個字段不重復,那么就可以利用這些不重復的字段的max或min作為分水嶺,使其成為分頁算法中分開每頁的參照物。在這里,我們可以用操作符“>”或“<”號來完成這個使命,使查詢語句符合SARG形式。如:

?

?

?

SELECT ? TOP ? 10 ? * ?
FROM ???tt
WHERE ??id? > ? 200

?

?

? 于是就有了如下分頁方案:


select ? top ?頁大小? *
from ?tt
where ?id >
( select ? max ?(id)? from ?
( select ? top ?((頁碼 - 1 ) * 頁大小)?id? from ?tt? order ? by ?id)? as ?T
)?
order ? by ?id

?

?

? 在選擇即不重復值,又容易分辨大小的列時,我們通常會選擇主鍵。下表列出了筆者用有著1000萬數據的辦公自動化系統中的表,在以id(id是主鍵,但并不是聚集索引。)為排序列、提取id字段,分別以第1、10、100、500、1000、1萬、10萬、25萬、50萬頁為例,測試以上三種分頁方案的執行速度:(單位:毫秒)

頁碼 方案1 方案2 方案3
1 60 30 76
10 46 16 63
100 1076 720 130
500 540 12943 83
1000 17110 470 250
10000 24796 4500 140
100000 38326 42283 1553
250000 28140 128720 2330
500000 121686 127846 7168

從上表中,我們可以看出,三種存儲過程在執行100頁以下的分頁命令時,都是可以信任的,速度都很好。但第一種方案在執行分頁1000頁以上后,速度就降了下來。第二種方案大約是在執行分頁1萬頁以上后速度開始降了下來。而第三種方案卻始終沒有大的降勢,后勁仍然很足。

?

? 在確定了第三種分頁方案后,我們可以據此寫一個存儲過程。大家知道SQL SERVER的存儲過程是事先編譯好的SQL語句,它的執行效率要比通過WEB頁面傳來的SQL語句的執行效率要高。下面的存儲過程不僅含有分頁方案,還會根據頁面傳來的參數來確定是否進行數據總數統計。

獲取指定頁的數據:

? /* ***********************************************************

?*?Code?formatted?by?SoftTree?SQL?Assistant??v5.0.97
?*?Time:?2011-10-05?21:40:08
?*********************************************************** */

CREATE ? PROCEDURE ?PAGE01
???? @tblName ? VARCHAR ( 255 ),? -- ?表名
???? @strGetFields ? VARCHAR ( 1000 )? = ? ' * ' ,? -- ?需要返回的列
???? @fldName ? VARCHAR ( 255 )? = ? '' ,? -- ?排序的字段名
???? @PageSize ? INT ? = ? 10 ,? -- ?頁尺寸
???? @PageIndex ? INT ? = ? 1 ,? -- ?頁碼
???? @doCount ? BIT ? = ? 0 ,? -- ?返回記錄總數,?非?0?值則返回
???? @OrderType ? BIT ? = ? 0 ,? -- ?設置排序類型,?非?0?值則降序
???? @strWhere ? VARCHAR ( 1500 )? = ? '''' ? -- ?查詢條件?(注意:?不要加?where)
AS
???? DECLARE ? @strSQL ???? VARCHAR ( 5000 )? -- ?主語句
???? DECLARE ? @strTmp ???? VARCHAR ( 110 )? -- ?臨時變量
???? DECLARE ? @strOrder ?? VARCHAR ( 400 )? -- ?排序類型
???? IF ? @doCount ? != ? 0
???? BEGIN
???????? IF ? @strWhere ? != ? ''''
???????????? SET ? @strSQL ? = ? ' select?count(*)?as?Total?from ' ? + ? ' ? ' ? + ? @tblName ? + ?
???????????????? ' ?where ' ? + ? @strWhere
???????? ELSE
???????????? SET ? @strSQL ? = ? ' select?count(*)?as?Total?from? ' ? + ? @tblName
???? END -- 以上代碼的意思是如果@doCount傳遞過來的不是0,就執行總數統計。以下的所有代碼都是@doCount為0的情況:
???? ELSE
???? BEGIN
???????? IF ? @OrderType ? != ? 0
???????? BEGIN
???????????? SET ? @strTmp ? = ? ' <(select?min '
???????????? SET ? @strOrder ? = ? ' ?order?by? ' ? + ? @fldName ? + ? ' ?desc '
???????????????? -- 如果@OrderType不是0,就執行降序,這句很重要!
???????? END
???????? ELSE
???????? BEGIN
???????????? SET ? @strTmp ? = ? ' >(select?max '
???????????? SET ? @strOrder ? = ? ' ?order?by? ' ? + ? @fldName ? + ? ' ?asc '
???????? END
???????? IF ? @PageIndex ? = ? 1
???????? BEGIN
???????????? IF ? @strWhere ? != ? ''''
???????????????? SET ? @strSQL ? = ? ' select?top? ' ? + ? STR ( @PageSize )? + ? ' ? ' ? + ? @strGetFields
???????????????????? + ? '
?????????????????from?
' ? + ? @tblName ? + ? ' ?where? ' ? + ? @strWhere ? + ? ' ? ' ? + ? @strOrder
???????????? ELSE
???????????????? SET ? @strSQL ? = ? ' select?top? ' ? + ? STR ( @PageSize )? + ? ' ? ' ? + ? @strGetFields
???????????????????? + ? ' ?
???????????from?
' ? + ? @tblName ? + ? ' ? ' ? + ? @strOrder
???????????????????? -- 如果是第一頁就執行以上代碼,這樣會加快執行速度
???????? END
???????? ELSE
???????? BEGIN
???????????? -- 以下代碼賦予了@strSQL以真正執行的SQL代碼 
???????????? SET ? @strSQL ? = ? ' select?top? ' ? + ? STR ( @PageSize )? + ? ' ? ' ? + ? @strGetFields ? + ?
???????????????? ' ?from? '
???????????????? + ? @tblName ? + ? ' ?where? ' ? + ? @fldName ? + ? '' ? + ? @strTmp ? + ? ' ( ' ? + ? @fldName ?
???????????????? + ? ' )?
??????????from?(select?top?
' ? + ? STR (( @PageIndex ? - 1 )? * ? @PageSize )? + ? ' ? ' ? + ? @fldName ?
???????????????? + ? ' ?
??????????from?
' ? + ? @tblName ? + ? '' ? + ? @strOrder ? + ? ' )?as?tblTmp) ' ? + ? @strOrder
????????????
???????????? IF ? @strWhere ? != ? ''''
???????????????? SET ? @strSQL ? = ? ' select?top? ' ? + ? STR ( @PageSize )? + ? ' ? ' ? + ? @strGetFields
???????????????????? + ? ' ?from?[ '
???????????????????? + ? @tblName ? + ? ' ]?where?[ ' ? + ? @fldName ? + ? ' ] ' ? + ? @strTmp ? + ? ' ([ '
???????????????????? + ? @fldName ? + ? ' ])?from?(select?top? ' ? + ? STR (( @PageIndex ? - 1 )? * ? @PageSize )?
???????????????????? + ? ' ?[ '
???????????????????? + ? @fldName ? + ? ' ]?from?[ ' ? + ? @tblName ? + ? ' ]?where? ' ? + ? @strWhere ?
???????????????????? + ? ' ? '
???????????????????? + ? @strOrder ? + ? ' )?as?tblTmp)?and? ' ? + ? @strWhere ? + ? ' ? ' ? + ? @strOrder
???????? END
???? END ?
???? EXEC ?( @strSQL )
GO


? -- -----PAGE01?'tt','*','id'

?

?

?

/* ***********************************************************
?*?Code?formatted?by?SoftTree?SQL?Assistant??v5.0.97
?*?Time:?2011-10-05?21:35:12
?***********************************************************
*/

CREATE ? PROCEDURE ?PAGE1_tt
???? @tblName ? VARCHAR ( 255 ),? -- ?表名
???? @strGetFields ? VARCHAR ( 1000 )? = ? ' * ' ,? -- ?需要返回的列
???? @fldName ? VARCHAR ( 255 )? = ? '' ,? -- ?排序的字段名
???? @PageSize ? INT ? = ? 10 ,? -- ?頁尺寸
???? @PageIndex ? INT ? = ? 1 ,? -- ?頁碼
???? @doCount ? BIT ? = ? 0 ,? -- ?返回記錄總數,?非?0?值則返回
???? @OrderType ? BIT ? = ? 0 ,? -- ?設置排序類型,?非?0?值則降序
???? @strWhere ? VARCHAR ( 1500 )? = ? '''' ? -- ?查詢條件?(注意:?不要加?where)
AS
???? DECLARE ? @strSQL ???? VARCHAR ( 5000 )? -- ?主語句
???? DECLARE ? @strTmp ???? VARCHAR ( 110 )? -- ?臨時變量
???? DECLARE ? @strOrder ?? VARCHAR ( 400 )? -- ?排序類型
???? IF ? @doCount ? != ? 0
???? BEGIN
???????? IF ? @strWhere ? != ? ''''
???????????? SET ? @strSQL ? = ? ' select?count(*)?as?Total?from ' ? + ? ' ? ' ? + ? @tblName ? + ?
???????????????? ' ?where ' ? + ? @strWhere
???????? ELSE
???????????? SET ? @strSQL ? = ? ' select?count(*)?as?Total?from? ' ? + ? @tblName
???? END -- 以上代碼的意思是如果@doCount傳遞過來的不是0,就執行總數統計。以下的所有代碼都是@doCount為0的情況:
???? ELSE
???? BEGIN
???????? IF ? @OrderType ? != ? 0
???????? BEGIN
???????????? SET ? @strTmp ? = ? ' <(select?min '
???????????? SET ? @strOrder ? = ? ' ?order?by? ' ? + ? @fldName ? + ? ' ?desc '
???????????????? -- 如果@OrderType不是0,就執行降序,這句很重要!
???????? END
???????? ELSE
???????? BEGIN
???????????? SET ? @strTmp ? = ? ' >(select?max '
???????????? SET ? @strOrder ? = ? ' ?order?by? ' ? + ? @fldName ? + ? ' ?asc '
???????? END
???????? IF ? @PageIndex ? = ? 1
???????? BEGIN
???????????? IF ? @strWhere ? != ? ''''
???????????????? SET ? @strSQL ? = ? ' select?top? ' ? + ? STR ( @PageSize )? + ? ' ? ' ? + ? @strGetFields
???????????????????? + ? '
?????????????????from?
' ? + ? @tblName ? + ? ' ?where? ' ? + ? @strWhere ? + ? ' ? ' ? + ? @strOrder
???????????? ELSE
???????????????? SET ? @strSQL ? = ? ' select?top? ' ? + ? STR ( @PageSize )? + ? ' ? ' ? + ? @strGetFields
???????????????????? + ? ' ?
???????????from?
' ? + ? @tblName ? + ? ' ? ' ? + ? @strOrder
???????????????????? -- 如果是第一頁就執行以上代碼,這樣會加快執行速度
???????? END
???????? ELSE
???????? BEGIN
???????????? -- 以下代碼賦予了@strSQL以真正執行的SQL代碼 
???????????? SET ? @strSQL ? = ? ' select?top? ' ? + ? STR ( @PageSize )? + ? ' ? ' ? + ? @strGetFields ? + ?
???????????????? ' ?from? '
???????????????? + ? @tblName ? + ? ' ?where? ' ? + ? @fldName ? + ? '' ? + ? @strTmp ? + ? ' ( ' ? + ? @fldName ?
???????????????? + ? ' )?
??????????from?(select?top?
' ? + ? STR (( @PageIndex ? - 1 )? * ? @PageSize )? + ? ' ? ' ? + ? @fldName ?
???????????????? + ? ' ?
??????????from?
' ? + ? @tblName ? + ? '' ? + ? @strOrder ? + ? ' )?as?tblTmp) ' ? + ? @strOrder
????????????
???????????? IF ? @strWhere ? != ? ''''
???????????????? SET ? @strSQL ? = ? ' select?top? ' ? + ? STR ( @PageSize )? + ? ' ? ' ? + ? @strGetFields
???????????????????? + ? ' ?from?[ '
???????????????????? + ? @tblName ? + ? ' ]?where?[ ' ? + ? @fldName ? + ? ' ] ' ? + ? @strTmp ? + ? ' ([ '
???????????????????? + ? @fldName ? + ? ' ])?from?(select?top? ' ? + ? STR (( @PageIndex ? - 1 )? * ? @PageSize )?
???????????????????? + ? ' ?[ '
???????????????????? + ? @fldName ? + ? ' ]?from?[ ' ? + ? @tblName ? + ? ' ]?where? ' ? + ? @strWhere ?
???????????????????? + ? ' ? '
???????????????????? + ? @strOrder ? + ? ' )?as?tblTmp)?and? ' ? + ? @strWhere ? + ? ' ? ' ? + ? @strOrder
???????? END
???? END ?
???? EXEC ?( @strSQL )

GO

? -- PAGE1_tt ? 'tt','*','id',100,1,0

?

? 上面的這個存儲過程是一個通用的存儲過程,其注釋已寫在其中了。 在大數據量的情況下,特別是在查詢最后幾頁的時候,查詢時間一般不會超過9秒;而用其他存儲過程,在實踐中就會導致超時,所以這個存儲過程非常適用于大容量數據庫的查詢。 筆者希望能夠通過對以上存儲過程的解析,能給大家帶來一定的啟示,并給工作帶來一定的效率提升,同時希望同行提出更優秀的實時數據分頁算法。

以上的這第三種存儲過程在小數據量的情況下,有如下現象:
1、分頁速度一般維持在1秒和3秒之間。
2、在查詢最后一頁時,速度一般為5秒至8秒,哪怕分頁總數只有3頁或30萬頁。

?

?

關于SQL分頁存儲過程的分析


更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索:z360901061

微信掃一掃加我為好友

QQ號聯系: 360901061

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。

【本文對您有幫助就好】

您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描上面二維碼支持博主2元、5元、10元、自定義金額等您想捐的金額吧,站長會非常 感謝您的哦!!!

發表我的評論
最新評論 總共0條評論
主站蜘蛛池模板: 欧洲亚洲视频 | 亚欧免费视频 | 欧美成人特黄级毛片 | 国产手机在线视频放线视频 | 久久久高清日本道免费观看 | 特级无码a级毛片特黄 | 亚洲日本久久一区二区va | 国产精品麻豆一区二区 | 精品外国呦系列在线观看 | 在线中文字幕日韩 | 超碰最新上传 | 日韩a无吗一区二区三区 | 色噜噜狠狠色综合免费视频 | 久久精品国产色蜜蜜麻豆 | 日日碰| 99re热这里只有精品视频 | 亚洲欧美中日韩 | 久久99久久精品国产99热 | 五月婷婷免费视频 | 亚洲免费在线观看 | 99热久久久这里只有精品免费 | 国产伦理一区二区三区 | 成人短视频网站 | 99久久精品免费看国产 | 久久免费视频8 | 国产男女猛视频在线观看网站 | 91精东果冻蜜桃星空麻豆 | 亚洲欧美国产精品 | jizz中国女人| 老潮湿影院免费体验区 | 欧美日韩精品 | 精品视自拍视频在线观看 | 日韩精品一区二区三区在线观看l | 久热这里只有精品在线 | 一区二区国产精品 | 特黄特黄一级高清免费大片 | www四虎影视| 久久久这里只有精品加勒比 | 久久看免费视频 | 亚洲国产精品综合欧美 | 成人一级黄色片 |