在項目中，我們經常遇到或用到分頁，那么在大數據量（百萬級以上）下，哪種分頁算法效率最優呢？我們不妨用事實說話。
測試環境
硬件：CPU 酷睿雙核T5750 內存：2G
軟件:Windows server 2003 + Sql server 2005
OK,我們首先創建一數據庫：data_Test,并在此數據庫中創建一表：tb_TestTable
按 Ctrl+C 復制代碼按 Ctrl+C 復制代碼然后我們在數據表中插入2000000條數據:
1
--插入數據2setidentity_inserttb_TestTableon3declare@countint4set@count=15while@count<=20000006begin7insertintotb_TestTable(id,userName,userPWD,userEmail)values(@count,'admin','admin888','lli0077@yahoo.com.cn')8set@count=@count+19end10setidentity_inserttb_TestTableoff我首先寫了五個常用存儲過程：
1，利用select top 和select not in進行分頁，具體代碼如下：
1createprocedureproc_paged_with_notin--利用selecttopandselectnotin2(3@pageIndexint,--頁索引4@pageSizeint--每頁記錄數5)6as7begin8setnocounton;9declare@timediffdatetime--耗時10declare@sqlnvarchar(500)11select@timediff=Getdate()12set@sql='selecttop'+str(@pageSize)+'*fromtb_TestTablewhere(IDnotin(selecttop'+str(@pageSize*@pageIndex)+'idfromtb_TestTableorderbyIDASC))orderbyID'13execute(@sql)--因selecttop后不支技直接接參數，所以寫成了字符串@sql14selectdatediff(ms,@timediff,GetDate())as耗時15setnocountoff;16end2,利用select top 和 select max(列鍵)
1createprocedureproc_paged_with_selectMax--利用selecttopandselectmax(列)2(3@pageIndexint,--頁索引4@pageSizeint--頁記錄數5)6as7begin8setnocounton;9declare@timediffdatetime10declare@sqlnvarchar(500)11select@timediff=Getdate()12set@sql='selecttop'+str(@pageSize)+'*Fromtb_TestTablewhere(ID>(selectmax(id)From(selecttop'+str(@pageSize*@pageIndex)+'idFromtb_TestTableorderbyID)asTempTable))orderbyID'13execute(@sql)14selectdatediff(ms,@timediff,GetDate())as耗時15setnocountoff;16end3,利用select top和中間變量--此方法因網上有人說效果最佳，所以貼出來一同測試
1createprocedureproc_paged_with_Midvar--利用ID>最大ID值和中間變量2(3@pageIndexint,4@pageSizeint5)6as7declare@countint8declare@IDint9declare@timediffdatetime10declare@sqlnvarchar(500)11begin12setnocounton;13select@count=0,@ID=0,@timediff=getdate()14select@count=@count+1,@ID=casewhen@count<=@pageSize*@pageIndexthenIDelse@IDendfromtb_testTableorderbyid15set@sql='selecttop'+str(@pageSize)+'*fromtb_testTablewhereID>'+str(@ID)16execute(@sql)17selectdatediff(ms,@timediff,getdate())as耗時18setnocountoff;19新聞熱點
疑難解答
