SQL Server 分頁查詢預存程序

標籤：經驗   不同   bit   尺寸   運算式   參照物   高效   size   符號   

1.低效的分頁

SELECT TOP m-n+1 * FROM publish WHERE (id NOT IN 　　　　(SELECT TOP n-1 id 　　　　 FROM publish)) 

　　

但這個預存程序有一個致命的缺點，就是它含有NOT IN字樣。雖然我可以把它改造為：

SELECT TOP 頁大小 * FROM Table1 WHERE not exists (select * from (select top (頁大小*頁數) * from table1 order by id) b where b.id=a.id ) order by id 

　　

即，用not exists來代替not in，但我們前面已經談過了，二者的執行效率實際上是沒有區別的。

 

2.高效的分頁

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

　　

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

3.高效的分頁預存程序

-- 擷取指定頁的資料 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)        -- 排序類型 --如果@doCount傳遞過來的不是0，就執行總數統計if @doCount != 0   begin     --拼接where 子句    if @strWhere !=‘‘     set @strSQL = "select count(*) as Total from [" + @tblName + "] where "[email protected]     else     set @strSQL = "select count(*) as Total from [" + @tblName + "]" end  else begin --排序的類型, OrderType!=0 降序if @OrderType != 0 begin     --<(select min 和下文的>(select max 是這個預存程序的關鍵。    --利用不重複的欄位的max或min作為分水嶺，使其成為分頁演算法中分開每頁的參照物。    --在這裡，可以用操作符“>”或“<”號來完成這個使命，使查詢語句符合SARG形式    --若用使用不滿足SARG形式的語句not in 或not exist 就無法限制搜尋的範圍了,SQL SERVER必須對每一行都判斷它是否滿足WHERE子句中的所有條件。    --一個索引對於不滿足SARG形式的運算式來說是無用的.    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) +" "[email protected]+ "  from [" + @tblName + "] where " + @strWhere + " " + @strOrder      else      set @strSQL = "select top " + str(@PageSize) +" "[email protected]+ "  from ["+ @tblName + "] "+ @strOrder --如果是第一頁就執行以上代碼，這樣會加快執行速度 end else begin --以下代碼賦予了@strSQL以真正執行的SQL代碼 set @strSQL = "select top " + str(@PageSize) +" "[email protected]+ "  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) +" "[email protected]+ "  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 

　　

4.SARG

SARG的定義：用於限制搜尋的一個操作，因為它通常是指一個特定的匹配，一個值得範圍內的匹配或者兩個以上條件的AND串連。

形式如下：  

列名 操作符 <常數 或 變數>或<常數 或 變數> 操作符列名 

列名可以出現在操作符的一邊，而常數或變數出現在操作符的另一邊。如：  

Name=’張三’  

價格>5000 

5000<價格 

Name=’張三’ and 價格>5000  

        如果一個運算式不能滿足SARG的形式，那它就無法限制搜尋的範圍了，也就是SQL SERVER必須對每一行都判斷它是否滿足WHERE子句中的所有條件。所以一個索引對於不滿足SARG形式的運算式來說是無用的。  

介紹完SARG後，我們來總結一下使用SARG以及在實踐中遇到的和某些資料上結論不同的經驗：  

  1) Like語句是否屬於SARG取決於所使用的萬用字元的類型  

如：name like ‘張%’ ，這就屬於SARG  

而：name like ‘%張’ ,就不屬於SARG。  

原因是萬用字元%在字串的開通使得索引無法使用。  

  2) or 會引起全表掃描  

Name=’張三’ and 價格>5000 符號SARG，而：Name=’張三’ or 價格>5000 則不符合SARG。使用or會引起全表掃描。  

  3)非操作符、函數引起的不滿足SARG形式的語句  

       不滿足SARG形式的語句最典型的情況就是包括非操作符的語句，如：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等，另外還有函數。下面就是幾個不滿足SARG形式的例子：  

ABS(價格)<5000  

Name like ‘%三’  

有些運算式，如：  

WHERE 價格*2>5000  

SQL SERVER也會認為是SARG，SQL SERVER會將此式轉化為：  

WHERE 價格>2500/2  

但我們不推薦這樣使用，因為有時SQL SERVER不能保證這種轉化與原始運算式是完全等價的。

SQL Server 分頁查詢預存程序