經(jīng)過優(yōu)化測試的sql分頁存儲過程

《經(jīng)過優(yōu)化測試的sql分頁存儲過程》由會員上傳分享，免費在線閱讀，更多相關內容在教育資源-天天文庫。

1、經(jīng)過優(yōu)化測試的SQL分頁存儲過程2009-10-2423:20建立一個Web應用，分頁瀏覽功能必不可少。這個問題是數(shù)據(jù)庫處理中十分常見的問題。經(jīng)典的數(shù)據(jù)分頁方法是:ADO紀錄集分頁法，也就是利用ADO自帶的分頁功能（利用游標）來實現(xiàn)分頁。但這種分頁方法僅適用于較小數(shù)據(jù)量的情形，因為游標本身有缺點：游標是存放在內存中，很費內存。游標一建立，就將相關的記錄鎖住，直到取消游標。游標提供了對特定集合中逐行掃描的手段，一般使用游標來逐行遍歷數(shù)據(jù)，根據(jù)取出數(shù)據(jù)條件的不同進行不同的操作。而對于多表和大表中定義的游標（大的數(shù)據(jù)集合）循環(huán)很容易使程序進入一個漫長的

2、等待甚至死機?！　「匾氖牵瑢τ诜浅４蟮臄?shù)據(jù)模型而言，分頁檢索時，如果按照傳統(tǒng)的每次都加載整個數(shù)據(jù)源的方法是非常浪費資源的。現(xiàn)在流行的分頁方法一般是檢索頁面大小的塊區(qū)的數(shù)據(jù)，而非檢索所有的數(shù)據(jù)，然后單步執(zhí)行當前行。　　最早較好地實現(xiàn)這種根據(jù)頁面大小和頁碼來提取數(shù)據(jù)的方法大概就是“俄羅斯存儲過程”。這個存儲過程用了游標，由于游標的局限性，所以這個方法并沒有得到大家的普遍認可?！　『髞?，網(wǎng)上有人改造了此存儲過程，下面的存儲過程就是結合我們的辦公自動化實例寫的分頁存儲過程：CREATEprocedurepagination1(@pagesizeint

3、,--頁面大小，如每頁存儲20條記錄@pageindexint--當前頁碼)assetnocountonbegindeclare@indextabletable(idintidentity(1,1),nidint)--定義表變量declare@PageLowerBoundint--定義此頁的底碼declare@PageUpperBoundint--定義此頁的頂碼set@PageLowerBound=(@pageindex-1)*@pagesizeset@PageUpperBound=@PageLowerBound+@pagesizesetrowc

4、ount@PageUpperBoundinsertinto@indextable(nid)selectgidfromTGongwen　　　　　　wherefariqi>dateadd(day,-365,getdate())orderbyfariqidescselectO.gid,O.mid,O.title,O.fadanwei,O.fariqifromTGongwenO,@indextablet???????????whereO.gid=t.nidandt.id>@PageLowerBound????????????????????????and

5、t.id<=@PageUpperBoundorderbyt.idendsetnocountoff　　以上存儲過程運用了SQLSERVER的最新技術――表變量。應該說這個存儲過程也是一個非常優(yōu)秀的分頁存儲過程。當然，在這個過程中，您也可以把其中的表變量寫成臨時表：CREATETABLE#Temp。但很明顯，在SQLSERVER中，用臨時表是沒有用表變量快的。所以筆者剛開始使用這個存儲過程時，感覺非常的不錯，速度也比原來的ADO的好。但后來，我又發(fā)現(xiàn)了比此方法更好的方法?！　」P者曾在網(wǎng)上看到了一篇小短文《從數(shù)據(jù)表中取出第n條到第m條的記錄的方法》，全

6、文如下：從publish表中取出第n條到第m條的記錄：SELECTTOPm-n+1*FROMpublishWHERE(idNOTIN　　　　(SELECTTOPn-1id　　　　FROMpublish))id為publish表的關鍵字　　我當時看到這篇文章的時候，真的是精神為之一振，覺得思路非常得好。等到后來，我在作辦公自動化系統(tǒng)（ASP.NET+C#＋SQLSERVER）的時候，忽然想起了這篇文章，我想如果把這個語句改造一下，這就可能是一個非常好的分頁存儲過程。于是我就滿網(wǎng)上找這篇文章，沒想到，文章還沒找到，卻找到了一篇根據(jù)此語句寫的一個分頁存

7、儲過程，這個存儲過程也是目前較為流行的一種分頁存儲過程，我很后悔沒有爭先把這段文字改造成存儲過程：CREATEPROCEDUREpagination2(@SQLnVARCHAR(4000),--不帶排序語句的SQL語句@Pageint,--頁碼@RecsPerPageint,--每頁容納的記錄數(shù)@IDVARCHAR(255),--需要排序的不重復的ID號@SortVARCHAR(255)--排序字段及規(guī)則)ASDECLARE@StrnVARCHAR(4000)SET@Str=''SELECTTOP''+CAST(@RecsPerPageASVAR

8、CHAR(20))+''*FROM(''+@SQL+'')TWHERET.''+@ID+''NOTIN(SELECTTOP''+CAST