SQL Server存儲過程的相關(guān)內(nèi)容
此文章主要向大家講述的是正確實現(xiàn)小數(shù)據(jù)量與你海量數(shù)據(jù)之通用分頁SQL Server存儲過程的顯示，在實際操作中建立一個 Web 應(yīng)用，分頁瀏覽的實際操作功能是必不可少。這個問題是數(shù)據(jù)庫處理中十分常見的問題。
經(jīng)典的數(shù)據(jù)分頁方法是:ADO 紀錄集分頁法，也就是利用ADO自帶的分頁功能（利用游標）來實現(xiàn)分頁。但這種分頁方法僅適用于較小數(shù)據(jù)量的情形，因為游標本身有缺點：游標是存放在內(nèi)存中，很費內(nèi)存。
游標一建立，就將相關(guān)的記錄鎖住，直到取消游標。游標提供了對特定集合中逐行掃描的手段，一般使用游標來逐行遍歷數(shù)據(jù)，根據(jù)取出數(shù)據(jù)條件的不同進行不同的操作。而對于多表和大表中定義的游標（大的數(shù)據(jù)集合）循環(huán)很容易使程序進入一個漫長的等待甚至死機。
更重要的是，對于非常大的數(shù)據(jù)模型而言，分頁檢索時，如果按照傳統(tǒng)的每次都加載整個數(shù)據(jù)源的方法是非常浪費資源的。現(xiàn)在流行的分頁方法一般是檢索頁面大小的塊區(qū)的數(shù)據(jù)，而非檢索所有的數(shù)據(jù)，然后單步執(zhí)行當前行。
最早較好地實現(xiàn)這種根據(jù)頁面大小和頁碼來提取數(shù)據(jù)的方法大概就是“俄羅斯SQL Server存儲過程”。這個存儲過程用了游標，由于游標的局限性，所以這個方法并沒有得到大家的普遍認可。
后來，網(wǎng)上有人改造了此存儲過程，下面的存儲過程就是結(jié)合我們的辦公自動化實例寫的分頁SQL Server存儲過程：
CREATE procedure pagination1 (@pagesize int, 頁面大小，如每頁存儲20條記錄 @pageindex int 當前頁碼 ) as set nocount cellSpacing=0 cellPadding=0 width="70%" align=center border=1> 頁碼 方案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
從上表中，我們可以看出，三種存儲過程在執(zhí)行100頁以下的分頁命令時，都是可以信任的，速度都很好。但***種方案在執(zhí)行分頁1000頁以上后，速度就降了下來。第二種方案大約是在執(zhí)行分頁1萬頁以上后速度開始降了下來。而第三種方案卻始終沒有大的降勢，后勁仍然很足。
在確定了第三種分頁方案后，我們可以據(jù)此寫一個存儲過程。大家知道SQL Server存儲過程是事先編譯好的SQL語句，它的執(zhí)行效率要比通過WEB頁面?zhèn)鱽淼腟QL語句的執(zhí)行效率要高。下面的存儲過程不僅含有分頁方案，還會根據(jù)頁面?zhèn)鱽淼膮?shù)來確定是否進行數(shù)據(jù)總數(shù)統(tǒng)計。
獲取指定頁的數(shù)據(jù)：
- CREATE PROCEDURE pagination3 @tblName varchar(255),
表名 @strGetFields varchar(1000) = ''*'',需要返回的列
@fldName varchar(255)='''', 排序的字段名 @PageSize int = 10, 頁尺寸 @PageIndex int = 1, 頁碼
@doCount bit = 0, 返回記錄總數(shù), 非 0 值則返回 @OrderType bit = 0, 設(shè)置排序類型, 非 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，就執(zhí)行總數(shù)統(tǒng)計。以下的所有代碼都是@doCount為0的情況：
- else begin if @OrderType != 0 begin set @strTmp = "<(select min" set @strOrder = " order by [" + @fldName +"] desc"
如果@OrderType不是0，就執(zhí)行降序，這句很重要！
- 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
如果是***頁就執(zhí)行以上代碼，這樣會加快執(zhí)行速度
- end else begin
以下代碼賦予了@strSQL以真正執(zhí)行的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
上面的這個存儲過程是一個通用的SQL Server存儲過程，其注釋已寫在其中了。 在大數(shù)據(jù)量的情況下，特別是在查詢***幾頁的時候，查詢時間一般不會超過9秒；而用其他存儲過程，在實踐中就會導(dǎo)致超時，所以這個存儲過程非常適用于大容量數(shù)據(jù)庫的查詢。
筆者希望能夠通過對以上SQL Server存儲過程的解析，能給大家?guī)硪欢ǖ膯⑹荆⒔o工作帶來一定的效率提升，同時希望同行提出更優(yōu)秀的實時數(shù)據(jù)分頁算法。
【編輯推薦】
- SQL Server索引選擇的引用與建議
- SQL Server索引的正確使用標準是啥樣？
- 三種SQL Server查找數(shù)據(jù)方法的比較
- SQL Server 索引底層實現(xiàn)的注意事項
- SQL Server 索引的底層實現(xiàn)概述
