<span style="font-size: small">我們以前在開發大資料量的分頁存儲過程時,往往都為了怎麼樣實作高效的性能,而大傷腦筋,似乎總是想寫出最佳的存儲過程分頁方法,我們假如建立一個學生基本資訊表StudentInfo,我們看在Sql Server 2000中我們實作的存儲過程:
<br />
<br />CREATE PROCEDURE p_GetStudentInfo
<br />@strWhere varchar(1500) -- 查詢條件 (注意: 不要加 where)
<br />,@PageSize int = 50 -- 頁尺寸
<br />,@PageIndex int = 1 -- 頁碼
<br />AS
<br />BEGIN
<br />
<br />declare @strSQL varchar(5000) -- 主語句
<br />declare @strTmp varchar(110) -- 臨時變量
<br />declare @strOrder varchar(400) -- 排序類型
<br />
<br />set @strTmp = '<(select min'
<br />set @strOrder = ' order by studentinfoid desc'
<br />
<br />if @PageIndex = 1
<br />begin
<br /> if @strWhere != ''
<br /> set @strSQL = 'select top ' + str(@PageSize) +' * from studentinfo where ' + @strWhere + ' ' + @strOrder
<br /> else
<br /> set @strSQL = 'select top ' + str(@PageSize) +' * from studentinfo '+ @strOrder
<br />--如果是第一頁就執行以上代碼,這樣會加快執行速度
<br />end
<br />else
<br />begin
<br />--以下代碼賦予了@strSQL以真正執行的SQL代碼
<br />set @strSQL = 'select top ' + str(@PageSize) +' * from studentinfo
<br />where studentinfoid' + @strTmp + '(studentinfoid) from (select top ' + str((@PageIndex-1)*@PageSize) + ' studentinfoid from studentinfo' + @strOrder + ') as tblTmp)'+ @strOrder
<br />if @strWhere != ''
<br /> set @strSQL = 'select top ' + str(@PageSize) +' * from studentinfo
<br /> where studentinfoid' + @strTmp + '(studentinfoid) from
<br /> (select top ' + str((@PageIndex-1)*@PageSize) + ' studentinfoid from studentinfo where ' + @strWhere + ' '
<br /> + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
<br />end
<br />--print (@strSQL)
<br />exec (@strSQL)
<br />END
<br />GO
<br /> 我們在上面的存儲過程中可以看到,如果執行的是第一頁的記錄時,我們可以看到隻執行
<br />
<br />'select top ' + str(@PageSize) +' * from studentinfo where ' + @strWhere + ' ' + @strOrder
<br /> 就可以了,而如果翻頁的時候,就會在條件查詢中又嵌套子查詢,勢必性能會有所下降,而這個已經在我們Sql Server2000中算是高效的分頁存儲過程實作方式了,而現在如果我們換作Sql Server 2005的時候,我們是不是還是用這種方式呢?
<br />
<br /> 在Sql Server 2005中,我們可以利用新增函數row_number()來更高效的實作分頁存儲
<br />
<br />CREATE PROCEDURE p_GetStudentInfo
<br /> @PageSize INT,
<br /> @PageIndex INT,
<br /> @strWhere varchar(1500) -- 查詢條件(注意: 不要加where)
<br /> As
<br /> Begin
<br /> select * from (
<br /> select row_number() over (order by StudentInfoId) row,* from StudentInfo ) StudentInfo
<br /> where row between @PageSize*(@PageIndex-1) and @PageSize*@PageIndex
<br /> End
<br /> go
<br /> 依照群組顯示每條記錄在該群組中出現的順序位置,在顯示每條記錄編号時非常有用,并且搭配OVER子句,這樣就可以實作查詢記錄的條數了。
<br />
<br /> 我們現在來比較一下它們執行的性能,StudentInfo表中現在有30多萬條資料,我們分别來執行下面的存儲過程:
<br />
<br /> Exec p_GetStudentInfo
<br /> @PageSize = 10000,
<br /> @PageIndex = 10,
<br /> @strWhere = '1=1'
<br />
<br />
<br /> 在Sql Server 2000中,執行的時間是接近2秒;
<br />
<br /> 在Sql Server 2005中,執行的時間顯示的是接近0秒。
<br />
<br /> 如果資料涉及到千萬級的資料時,比較上面兩種方法,就可以看到一些顯著的差别了</span>