程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL Server 大量數據的分頁存儲過程代碼

SQL Server 大量數據的分頁存儲過程代碼

編輯:關於SqlServer

       OK,我們首先創建一數據庫:data_Test,並在此數據庫中創建一表:tb_TestTable

      create database data_Test --創建數據庫data_Test

      GO

      use data_Test

      GO

      create table tb_TestTable --創建表

      (

      id int identity(1,1) primary key,

      userName nvarchar(20) not null,

      userPWD nvarchar(20) not null,

      userEmail nvarchar(40) null

      )

      GO

      然後我們在數據表中插入2000000條數據:

      --插入數據

      set identity_insert tb_TestTable on

      declare @count int

      set @count=1

      while @count<=2000000

      begin

      insert into tb_TestTable(id,userName,userPWD,userEmail) values(@count,'admin','admin888','[email protected]')

      set @count=@count+1

      end

      set identity_insert tb_TestTable off

      我首先寫了五個常用存儲過程:

      1,利用select top 和select not in進行分頁,具體代碼如下:

      create procedure proc_paged_with_notin --利用select top and select not in

      (

      @pageIndex int, --頁索引

      @pageSize int --每頁記錄數

      )

      as

      begin

      set nocount on;

      declare @timediff datetime --耗時

      declare @sql nvarchar(500)

      select @timediff=Getdate()

      set @sql='select top '+str(@pageSize)+' * from tb_TestTable where(ID not in(select top '+str(@pageSize*@pageIndex)+' id from tb_TestTable order by ID ASC)) order by ID'

      execute(@sql) --因select top後不支技直接接參數,所以寫成了字符串@sql

      select datediff(ms,@timediff,GetDate()) as 耗時

      set nocount off;

      end

      2,利用select top 和 select max(列鍵)

      create procedure proc_paged_with_selectMax --利用select top and select max(列)

      (

      @pageIndex int, --頁索引

      @pageSize int --頁記錄數

      )

      as

      begin

      set nocount on;

      declare @timediff datetime

      declare @sql nvarchar(500)

      select @timediff=Getdate()

      set @sql='select top '+str(@pageSize)+' * From tb_TestTable where(ID>(select max(id) From (select top '+str(@pageSize*@pageIndex)+' id From tb_TestTable order by ID) as TempTable)) order by ID'

      execute(@sql)

      select datediff(ms,@timediff,GetDate()) as 耗時

      set nocount off;

      end

      3,利用select top和中間變量--此方法因網上有人說效果最佳,所以貼出來一同測試

      create procedure proc_paged_with_Midvar --利用ID>最大ID值和中間變量

      (

      @pageIndex int,

      @pageSize int

      )

      as

      declare @count int

      declare @ID int

      declare @timediff datetime

      declare @sql nvarchar(500)

      begin

      set nocount on;

      select @count=0,@ID=0,@timediff=getdate()

      select @count=@count+1,@ID=case when @count<=@pageSize*@pageIndex then ID else @ID end from tb_testTable order by id

      set @sql='select top '+str(@pageSize)+' * from tb_testTable where ID>'+str(@ID)

      execute(@sql)

      select datediff(ms,@timediff,getdate()) as 耗時

      set nocount off;

      end

      4,利用Row_number() 此方法為SQL server 2005中新的方法,利用Row_number()給數據行加上索引

      create procedure proc_paged_with_Rownumber --利用SQL 2005中的Row_number()

      (

      @pageIndex int,

      @pageSize int

      )

      as

      declare @timediff datetime

      begin

      set nocount on;

      select @timediff=getdate()

      select * from (select *,Row_number() over(order by ID asc) as IDRank from tb_testTable) as IDWithRowNumber where IDRank>@pageSize*@pageIndex and IDRank<@pageSize*(@pageIndex+1)

      select datediff(ms,@timediff,getdate()) as 耗時

      set nocount off;

      end

      5,利用臨時表及Row_number

      create procedure proc_CTE --利用臨時表及Row_number

      (

      @pageIndex int, --頁索引

      @pageSize int --頁記錄數

      )

      as

      set nocount on;

      declare @ctestr nvarchar(400)

      declare @strSql nvarchar(400)

      declare @datediff datetime

      begin

      select @datediff=GetDate()

      set @ctestr='with Table_CTE as

      (select ceiling((Row_number() over(order by ID ASC))/'+str(@pageSize)+') as page_num,* from tb_TestTable)';

      set @strSql=@ctestr+' select * From Table_CTE where page_num='+str(@pageIndex)

      end

      begin

      execute sp_executesql @strSql

      select datediff(ms,@datediff,GetDate())

      set nocount off;

      end

      OK,至此,存儲過程創建完畢,我們分別在每頁10條數據的情況下在第2頁,第1000頁,第10000頁,第100000頁,第199999頁進行測試,耗時單位:ms 每頁測試5次取其平均值

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved