Sql Server多種分頁效能的比較

標籤：png   資料庫   select   預設   6.2   ...   begin   inf   相對   

一.前言

　　因為工作關係，遇到了非常大的資料量的分頁問題，資料總共有8000萬吧，這個顯然不是簡單的分頁能夠解決的，需要從多多方面考慮，從分表、分庫等等。但是這個也讓我考慮到了分頁效能的問題，在不同資料量的情況下，不同的分頁方法效率是否會有不同。我在這裡用比較常見的幾種分頁方法在不同的資料量、不同頁碼下進行對比，分別是：Top、Row_Number()和Offset Fetch。這裡只用它們分別最簡單的語句，如下。

　　Top：

create proc Tops @pageindex int,@pagesize intASBEGINselect top (@pagesize) * from Customers where CustomerID not in (select top ((@pageindex - 1)* @pagesize) CustomerID from Customers order by CustomerID DESC) order by CustomerID DESCEND

　　Row_Number():

create proc RowNumber @pageindex int,@pagesize intASBEGINselect * from (select ROW_NUMBER() OVER(order by CustomerID desc) as px,* from Customers) as awhere a.px between ((@pageindex - 1)* @pagesize + 1) and (@pageindex*@pagesize)END

　　Offset Fetch:

create proc Offset_Fetch @pageindex int,@pagesize intASBEGINselect * from Customers order by CustomerID descoffset ((@pageindex - 1) * @pagesize) rowsfetch next @pagesize rows only  END

　　三個預存程序都對資料進行了排序，設定的語句相對公平，這裡預設每頁10條資料。Top的分頁是基本上所有的版本的sql server都可以使用的，row_number()是sql 2005以上，offset fetch需要sql 2012才支援了。

 二.20W資料量

　　1.Top

　　　　第1頁十次執行平均時間29.1毫秒。

　　　　

　　　　第1萬頁十次執行平均時間109.2毫秒。

　　　　

　　　　第2萬頁十次執行平均時間126.8毫秒。

　　　　

　　2.Row_Number()

　　　第1頁十次執行平均時間20.2毫秒。

　　　　

　　　　第1萬頁十次執行平均時間96.5毫秒。

　　　　

　　　　第2萬頁十次執行平均時間153.8毫秒。

　　　　

　　3.Offset Fecth

　　　第1頁十次執行平均時間19.3毫秒。

　　　　

　　　　第1萬頁十次執行平均時間70毫秒。

　　　　

　　　　第2萬頁十次執行平均時間75.1毫秒。

　　　　

三.200W資料量

　　1.Top

　　　　第1頁十次執行平均時間55.9毫秒。

 　　　　

　　　　第10萬頁十次執行平均時間.....毫秒。

　　　　執行了好幾十秒。。。。直接淘汰

　　2.Row_Number()

　　　第1頁十次執行平均時間25.5毫秒

　　　　

　　　　第10萬頁十次執行平均時間642.3毫秒

　　　　

　　　　第20萬頁十次執行平均時間1257毫秒

　　　　

　　3.Offset Fecth

　　　　第1頁十次執行平均時間24.7毫秒

　　　　

　　　　第10萬頁十次執行平均時間220.5毫秒

　　　　

　　　　第20萬頁十次執行平均時間396毫秒

　　　　

三.2000W資料量

　　1.Top

　　　因為在上一輪被淘汰了，所以這一輪就算了。。

　　2.Row_Number()

　　　　第1頁十次執行平均時間57毫秒

 　　　　

　　　　第100W頁十次執行平均時間6401.5毫秒

　　　　

　　　　第200W頁十次執行平均時間14606.2毫秒

　　　　

　　3.Offset Fecth

　　　第1頁十次執行平均時間27.5毫秒

　　　　

　　　　第100W頁十次執行平均時間1778.9毫秒

　　　　

　　　　第200W頁十次執行平均時間3523.2毫秒

　　　　

四。總結

　　可以看出來資料量越大、分頁頁碼越大對分頁效率影響就越大。top的分頁方法早早出局，很明顯是因為not in 的資料量太龐大了，所以要是有好的top分頁方法可以下面留言，我也會進行測試。row_number的分頁方法算是比較好的了，而且sql2005及以上的資料庫都可以用，受眾範圍比較大，而offset fecth的效能更加優越，但是只有sql2012及以上的才支援。

　　有什麼問題歡迎討論！

 

 

 

 

　　　　

 

Sql Server多種分頁效能的比較