浅谈基于SQL Server分页存储过程五种方法及性能比较

2024-03-27 06:21:03 34

在SQLServer数据库操作中，我们常常会用到存储过程对实现对查询的数据的分页处理，以方便浏览者的浏览。

创建数据库data_Test：

createdatabasedata_Test
GO
usedata_Test
GO
createtabletb_TestTable--创建表
(
idintidentity(1,1)primarykey,
userNamenvarchar(20)notnull,
userPWDnvarchar(20)notnull,
userEmailnvarchar(40)null
)
GO

插入数据：

setidentity_inserttb_TestTableon
declare@countint
set@count=1
while@count<=2000000
begin
insertintotb_TestTable(id,userName,userPWD,userEmail)values(@count,'admin','admin888','lli0077@yahoo.com.cn')
set@count=@count+1
end
setidentity_inserttb_TestTableoff

1、利用selecttop和selectnotin进行分页

具体代码如下：

createprocedureproc_paged_with_notin--利用selecttopandselectnotin
(
@pageIndexint,--页索引
@pageSizeint--每页记录数
)
as
begin
setnocounton;
declare@timediffdatetime--耗时
declare@sqlnvarchar(500)
select@timediff=Getdate()
set@sql='selecttop'+str(@pageSize)+'*fromtb_TestTablewhere(IDnotin(selecttop'+str(@pageSize*@pageIndex)+'idfromtb_TestTableorderbyIDASC))orderbyID'
execute(@sql)--因selecttop后不支技直接接参数，所以写成了字符串@sql
selectdatediff(ms,@timediff,GetDate())as耗时
setnocountoff;
end

2、利用selecttop和selectmax(列键)

createprocedureproc_paged_with_selectMax--利用selecttopandselectmax(列)
(
@pageIndexint,--页索引
@pageSizeint--页记录数
)
as
begin
setnocounton;
declare@timediffdatetime
declare@sqlnvarchar(500)
select@timediff=Getdate()
set@sql='selecttop'+str(@pageSize)+'*Fromtb_TestTablewhere(ID>(selectmax(id)From(selecttop'+str(@pageSize*@pageIndex)+'idFromtb_TestTableorderbyID)asTempTable))orderbyID'
execute(@sql)
selectdatediff(ms,@timediff,GetDate())as耗时
setnocountoff;
end

3、利用selecttop和中间变量

createprocedureproc_paged_with_Midvar--利用ID>最大ID值和中间变量
(
@pageIndexint,
@pageSizeint
)
as
declare@countint
declare@IDint
declare@timediffdatetime
declare@sqlnvarchar(500)
begin
setnocounton;
select@count=0,@ID=0,@timediff=getdate()
select@count=@count+1,@ID=casewhen@count<=@pageSize*@pageIndexthenIDelse@IDendfromtb_testTableorderbyid
set@sql='selecttop'+str(@pageSize)+'*fromtb_testTablewhereID>'+str(@ID)
execute(@sql)
selectdatediff(ms,@timediff,getdate())as耗时
setnocountoff;
end

4、利用Row_number()此方法为SQLserver2005中新的方法,利用Row_number()给数据行加上索引

createprocedureproc_paged_with_Rownumber--利用SQL2005中的Row_number()
(
@pageIndexint,
@pageSizeint
)
as
declare@timediffdatetime
begin
setnocounton;
select@timediff=getdate()
select*from(select*,Row_number()over(orderbyIDasc)asIDRankfromtb_testTable)asIDWithRowNumberwhereIDRank>@pageSize*@pageIndexandIDRank<@pageSize*(@pageIndex+1)
selectdatediff(ms,@timediff,getdate())as耗时
setnocountoff;
end

5、利用临时表及Row_number

createprocedureproc_CTE--利用临时表及Row_number
(
@pageIndexint,--页索引
@pageSizeint--页记录数
)
as
setnocounton;
declare@ctestrnvarchar()
declare@strSqlnvarchar()
declare@datediffdatetime
begin
select@datediff=GetDate()
set@ctestr='withTable_CTEas
(selectceiling((Row_number()over(orderbyIDASC))/'+str(@pageSize)+')aspage_num,*fromtb_TestTable)';
set@strSql=@ctestr+'select*FromTable_CTEwherepage_num='+str(@pageIndex)
end
begin
executesp_executesql@strSql
selectdatediff(ms,@datediff,GetDate())
setnocountoff;
end

以上的五种方法中，网上说第三种利用selecttop和中间变量的方法是效率最高的。关于SQLServer分页存储过程五种方法及性能比较的全部内容就到此结束了，希望对大家有所帮助。

浅谈基于SQL Server分页存储过程五种方法及性能比较

热门推荐

随机推荐