[原创,分享]百万级数据分页只用53秒的存储过程(只适用于SQL Server2005) - yms123的博客

作者在 2011-05-31 17:30:52 发布以下内容

--SQL2005分页存储过程
--要求:分页表必须具备主键
--缺点:只能单表查询分页
--作者:yms123
--所在论坛:http://bbs.bccn.net
--ASP/ASP.NET技术版主
--MSN:yms126@hotmail.com
CREATE Procedure SQL2005Pagin(
  @PageSize int, --每页记录数
  @PageCurrent int, --当前页
  @PageCount int OUTPUT,--输出参数总页数
  @SqlStr varchar(max)='',--SQL语句
  @TableName varchar(max)--表名
)
AS
--声明变量
Declare @@DataCount int--总记录数
Declare @tempSqlStr nvarchar(max) --临时Sql语句
Declare @tempNumber int --临时变量
Declare @@PKName varchar(max) --主键名称
Declare @LPDNum int --上一页记录数
Declare @CPDnum int --本页记录数
--获得总记录数
Set @tempSqlStr='select @DataCount=count(*) from '+@TableName+' Where exists ('+@SqlStr+')'
exec sp_executesql @tempSqlStr,N'@DataCount int out',@@DataCount out --统计记录数
--print @@DataCount
--获得指定表的主键字段名称
select TOP 1 @@PKName=[name] from syscolumns a where exists
(
    select * from sysindexkeys b
    where b.id=object_id(N'tbl_User')
    and a.id=b.id
    and a.colid=b.colid
)

--计算总页数
if (@@DataCount%@PageSize)=0
    Set @PageCount=@@DataCount/@PageSize
Else
    Begin
       Set @tempNumber=@@DataCount/@PageSize
       Set @PageCount=@tempNumber+1
    End

--计算当前页记录数
Set @LPDNum=(@PageCurrent-1)*@PageSize
Set @CPDnum=@PageSize+@LPDNum

--构造查询语句筛选出记录
Set @tempSqlStr='WITH ['+@TableName+' ORDERED BY RowNum] AS'+CHAR(13)
Set @tempSqlStr=@tempSqlStr+'('+CHAR(13)
Set @tempSqlStr=@tempSqlStr+'select ROW_NUMBER() OVER (ORDER BY '+@@PKName+' ASC) as RowNum,* '
Set @tempSqlStr=@tempSqlStr+'from '+@TableName+' Where exists '+CHAR(13)
Set @tempSqlStr=@tempSqlStr+'('+CHAR(13)
Set @tempSqlStr=@tempSqlStr+@SqlStr+CHAR(13)
Set @tempSqlStr=@tempSqlStr+')'+CHAR(13)
Set @tempSqlStr=@tempSqlStr+')'+CHAR(13)
Set @tempSqlStr=@tempSqlStr+'select * from ['+@TableName+' ORDERED BY RowNum] as a inner join ('
Set @tempSqlStr=@tempSqlStr+'select RowNum,'+@@PKName+' from (select RowNum,'+@@PKName+' from ['+@TableName+' ORDERED BY RowNum])'
Set @tempSqlStr=@tempSqlStr+'as t where rowNum between '+cast(@LPDNum as varchar)+' and '+cast(@CPDnum as varchar)+') as b on a.'+@@PKName+'=b.'+@@PKName+' order by b.rownum'

--执行查询语句返回结果
exec(@tempSqlStr)

使用SQL2005独有的ROW_NUMBER函数实现，
已做压力测试
总数据量:1329382
条数据的表
一百万条数据1页测试执行10次最少时间用了53秒
欢迎提出改进意见
测试代码
declare @begin_date datetime
declare @end_date datetime
select @begin_date = getdate() declare @@PCount int
exec SQL2005Pagin 1000000,1,@@PCount,'select * from tbl_User','tbl_User'
PRINT @@PCount select datediff(ms,@begin_date,@end_date) as '用时/毫秒'
tbl_User为临时测试的表名已插入1329382条数据
测试电脑的配置（单位的电脑)[QQ电脑管家获得的配置信息]
操作系统  Microsoft Windows XP Professional (32位/Service Pack 3)
CPU  (英特尔)Pentium(R) Dual-Core  CPU      E5800  @ 3.20GHz(3192 MHz)
主板  soyo SY-I5G41-L V5.0
内存  2.00 GB (金士顿 PC3-10600 DDR3 SDRAM 1333MHz)
主硬盘  500 GB (希捷 ST3500413AS 已使用时间: 294小时)
显卡  Intel(R) G41 Express Chipset (256 MB)
显示器  戴尔 DELf021 32位真彩色 60Hz
声卡  Realtek 5.1 High Definition Audio
网卡  Realtek PCIe FE Family Controller