SQL Server存儲過程的相關內容
此文章主要向大家講述的是正確實現小數據量與你海量數據之通用分頁SQL Server存儲過程的顯示，在實際操作中建立一個 Web 應用，分頁瀏覽的實際操作功能是必不可少。這個問題是數據庫處理中十分常見的問題。
經典的數據分頁方法是:ADO 紀錄集分頁法，也就是利用ADO自帶的分頁功能（利用游標）來實現分頁。但這種分頁方法僅適用于較小數據量的情形，因為游標本身有缺點：游標是存放在內存中，很費內存。
游標一建立，就將相關的記錄鎖住，直到取消游標。游標提供了對特定集合中逐行掃描的手段，一般使用游標來逐行遍歷數據，根據取出數據條件的不同進行不同的操作。而對于多表和大表中定義的游標（大的數據集合）循環很容易使程序進入一個漫長的等待甚至死機。
更重要的是，對于非常大的數據模型而言，分頁檢索時，如果按照傳統的每次都加載整個數據源的方法是非常浪費資源的。現在流行的分頁方法一般是檢索頁面大小的塊區的數據，而非檢索所有的數據，然后單步執行當前行。
最早較好地實現這種根據頁面大小和頁碼來提取數據的方法大概就是“俄羅斯SQL Server存儲過程”。這個存儲過程用了游標，由于游標的局限性，所以這個方法并沒有得到大家的普遍認可。
后來，網上有人改造了此存儲過程，下面的存儲過程就是結合我們的辦公自動化實例寫的分頁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
從上表中，我們可以看出，三種存儲過程在執行100頁以下的分頁命令時，都是可以信任的，速度都很好。但***種方案在執行分頁1000頁以上后，速度就降了下來。第二種方案大約是在執行分頁1萬頁以上后速度開始降了下來。而第三種方案卻始終沒有大的降勢，后勁仍然很足。
在確定了第三種分頁方案后，我們可以據此寫一個存儲過程。大家知道SQL Server存儲過程是事先編譯好的SQL語句，它的執行效率要比通過WEB頁面傳來的SQL語句的執行效率要高。下面的存儲過程不僅含有分頁方案，還會根據頁面傳來的參數來確定是否進行數據總數統計。
獲取指定頁的數據：
- CREATE PROCEDURE pagination3 @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
上面的這個存儲過程是一個通用的SQL Server存儲過程，其注釋已寫在其中了。 在大數據量的情況下，特別是在查詢***幾頁的時候，查詢時間一般不會超過9秒；而用其他存儲過程，在實踐中就會導致超時，所以這個存儲過程非常適用于大容量數據庫的查詢。
筆者希望能夠通過對以上SQL Server存儲過程的解析，能給大家帶來一定的啟示，并給工作帶來一定的效率提升，同時希望同行提出更優秀的實時數據分頁算法。
【編輯推薦】
- SQL Server索引選擇的引用與建議
- SQL Server索引的正確使用標準是啥樣？
- 三種SQL Server查找數據方法的比較
- SQL Server 索引底層實現的注意事項
- SQL Server 索引的底層實現概述
