我首先写了五个常用存储过程：
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次取其平均值
存过 第2页耗时 第1000页耗时 第10000页耗时 第100000页耗时 第199999页耗时 效率排行
| 序号 | 存过 | 第2页耗时 | 第1000页耗时 | 第10000页耗时 | 第100000页耗时 | 第199999页耗时 | 效率排行 |
|---|---|---|---|---|---|---|---|
| 1 | 用not in | 0ms | 16ms | 47ms | 475ms | 953ms | 3 |
| 2 | 用select max | 5ms | 16ms | 35ms | 325ms | 623ms | 1 |
| 3 | 中间变量 | 966ms | 970ms | 960ms | 945ms | 933ms | 5 |
| 4 | row_number | 0ms | 0ms | 34ms | 365ms | 710ms | 2 |
| 4 | 临时表 | 780ms | 796ms | 798ms | 780ms | 805ms | 4 |
测试结果显示：select max >row_number>not in>临时表>中间变量
于是我对效率最高的select max方法用2分法进行了扩展，代码取自互联网，我修改了ASC排序时取不到值的BUG，测试结果:
2分法 156ms 156ms 180ms 470ms 156ms 1*
从测试结果来看，使用2分法确实可以提高效率并使效率更为稳定，我又增加了第159999页的测试，用时仅296ms，效果相当的不错！
下面是2分法使用select max的代码，已相当完善。
--/*----- 对数据进行了2分处理使查询前半部分数据与查询后半部分数据性能相同 -------*/
ALTER PROCEDURE Proc_paged_2part_selectmax (@tblName NVARCHAR(200),
----要显示的表或多个表的连接
@fldName NVARCHAR(500) = '*',
----要显示的字段列表
@pageSize INT = 10,
----每页显示的记录个数
@page INT = 1,
----要显示那一页的记录
@fldSort NVARCHAR(200) = NULL,
----排序字段列表或条件
@Sort BIT = 0,
----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ')
@strCondition NVARCHAR(1000) = NULL,
----查询条件,不需where
@ID NVARCHAR(150),
----主表的主键
@Dist BIT = 0,
----是否添加查询字段的 DISTINCT 默认0不添加/1添加
@pageCount INT = 1 output,
----查询结果分页后的总页数
@Counts INT = 1 output
----查询到的记录数
)
AS
SET nocount ON
DECLARE @sqlTmp NVARCHAR(1000) ----存放动态生成的SQL语句
DECLARE @strTmp NVARCHAR(1000)
----存放取得查询结果总数的查询语句
DECLARE @strID NVARCHAR(1000)
----存放取得查询开头或结尾ID的查询语句
DECLARE @strSortType NVARCHAR(10) ----数据排序规则A
DECLARE @strFSortType NVARCHAR(10) ----数据排序规则B
DECLARE @SqlSelect NVARCHAR(50)
----对含有DISTINCT的查询进行SQL构造
DECLARE @SqlCounts NVARCHAR(50)
----对含有DISTINCT的总数查询进行SQL构造
DECLARE @timediff DATETIME --耗时测试时间差
SELECT @timediff = Getdate()
IF @Dist = 0
BEGIN
SET @SqlSelect = 'select '
SET @SqlCounts = 'Count(*)'
END
ELSE
BEGIN
SET @SqlSelect = 'select distinct '
SET @SqlCounts = 'Count(DISTINCT ' + @ID + ')'
END
IF @Sort = 0
BEGIN
SET @strFSortType=' ASC '
SET @strSortType=' DESC '
END
ELSE
BEGIN
SET @strFSortType=' DESC '
SET @strSortType=' ASC '
END
--------生成查询语句--------
--此处@strTmp为取得查询结果数量的语句
IF @strCondition IS NULL
OR @strCondition = '' --没有设置显示条件
BEGIN
SET @sqlTmp = @fldName + ' From ' + @tblName
SET @strTmp = @SqlSelect + ' @Counts=' + @SqlCounts + ' FROM '
+ @tblName
SET @strID = ' From ' + @tblName
END
ELSE
BEGIN
SET @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) '
+ @strCondition
SET @strTmp = @SqlSelect + ' @Counts=' + @SqlCounts + ' FROM '
+ @tblName + ' where (1>0) ' + @strCondition
SET @strID = ' From ' + @tblName + ' where (1>0) '
+ @strCondition
END
----取得查询结果总数量-----
EXEC Sp_executesql
@strTmp,
N'@Counts int out ',
@Counts out
DECLARE @tmpCounts INT
IF @Counts = 0
SET @tmpCounts = 1
ELSE
SET @tmpCounts = @Counts
--取得分页总数
SET @pageCount=( @tmpCounts + @pageSize - 1 ) / @pageSize
/**//**//**//**当前页大于总页数 取最后一页**/
IF @page > @pageCount
SET @page=@pageCount
--/*-----数据分页2分处理-------*/
DECLARE @pageIndex INT --总数/页大小
DECLARE @lastcount INT --总数%页大小
SET @pageIndex = @tmpCounts / @pageSize
SET @lastcount = @tmpCounts%@pageSize
IF @lastcount > 0
SET @pageIndex = @pageIndex + 1
ELSE
SET @lastcount = @pagesize
--//***显示分页
IF @strCondition IS NULL
OR @strCondition = '' --没有设置显示条件
BEGIN
IF @pageIndex < 2
OR @page <= @pageIndex / 2 + @pageIndex % 2
--前半部分数据处理
BEGIN
IF @page = 1
SET @strTmp=@SqlSelect + ' top '
+ Cast(@pageSize AS VARCHAR(4)) + ' '
+ @fldName + ' from ' + @tblName + ' order by '
+ @fldSort + ' ' + @strFSortType
ELSE
BEGIN
IF @Sort = 1
BEGIN
SET @strTmp=@SqlSelect + ' top '
+ Cast(@pageSize AS VARCHAR(4)) + ' '
+ @fldName + ' from ' + @tblName +
' where '
+
@ID
+ ' <(select min(' + @ID + ') from (' +
@SqlSelect
+ ' top '
+ Cast(@pageSize*(@page-1) AS VARCHAR(20
))
+ ' ' + @ID + ' from ' + @tblName +
' order by '
+ @fldSort + ' ' + @strFSortType +
') AS TBMinID)'
+ ' order by ' + @fldSort + ' ' +
@strFSortType
END
ELSE
BEGIN
SET @strTmp=@SqlSelect + ' top '
+ Cast(@pageSize AS VARCHAR(4)) + ' '
+ @fldName + ' from ' + @tblName +
' where '
+
@ID
+ ' >(select max(' + @ID + ') from (' +
@SqlSelect
+ ' top '
+ Cast(@pageSize*(@page-1) AS VARCHAR(20
))
+ ' ' + @ID + ' from ' + @tblName +
' order by '
+ @fldSort + ' ' + @strFSortType +
') AS TBMinID)'
+ ' order by ' + @fldSort + ' ' +
@strFSortType
END
END
END
ELSE
BEGIN
SET @page = @pageIndex - @page + 1 --后半部分数据处理
IF @page <= 1 --最后一页数据显示
SET @strTmp=@SqlSelect + ' * from (' + @SqlSelect + ' top '
+ Cast(@lastcount AS VARCHAR(4)) + ' '
+ @fldName + ' from ' + @tblName + ' order by '
+ @fldSort + ' ' + @strSortType + ') AS TempTB'
+ ' order by ' + @fldSort + ' ' + @strFSortType
ELSE IF @Sort = 1
BEGIN
SET @strTmp=@SqlSelect + ' * from (' + @SqlSelect +
' top '
+ Cast(@pageSize AS VARCHAR(4)) + ' '
+ @fldName + ' from ' + @tblName + ' where ' +
@ID
+ ' >(select max(' + @ID + ') from(' +
@SqlSelect
+ ' top '
+ Cast(@pageSize*(@page-2)+@lastcount AS
VARCHAR(
20)
)
+ ' ' + @ID + ' from ' + @tblName +
' order by '
+ @fldSort + ' ' + @strSortType +
') AS TBMaxID)'
+ ' order by ' + @fldSort + ' ' + @strSortType
+ ') AS TempTB' + ' order by ' + @fldSort +
' '
+ @strFSortType
END
ELSE
BEGIN
SET @strTmp=@SqlSelect + ' * from (' + @SqlSelect +
' top '
+ Cast(@pageSize AS VARCHAR(4)) + ' '
+ @fldName + ' from ' + @tblName + ' where ' +
@ID
+ ' <(select min(' + @ID + ') from(' +
@SqlSelect
+ ' top '
+ Cast(@pageSize*(@page-2)+@lastcount AS
VARCHAR(
20)
)
+ ' ' + @ID + ' from ' + @tblName +
' order by '
+ @fldSort + ' ' + @strSortType +
') AS TBMaxID)'
+ ' order by ' + @fldSort + ' ' + @strSortType
+ ') AS TempTB' + ' order by ' + @fldSort +
' '
+ @strFSortType
END
END
END
ELSE --有查询条件
BEGIN
IF @pageIndex < 2
OR @page <= @pageIndex / 2 + @pageIndex % 2
--前半部分数据处理
BEGIN
IF @page = 1
SET @strTmp=@SqlSelect + ' top '
+ Cast(@pageSize AS VARCHAR(4)) + ' '
+ @fldName + ' from ' + @tblName + ' where 1=1 '
+ @strCondition + ' order by ' + @fldSort + ' '
+ @strFSortType
ELSE IF( @Sort = 1 )
BEGIN
SET @strTmp=@SqlSelect + ' top '
+ Cast(@pageSize AS VARCHAR(4)) + ' '
+ @fldName + ' from ' + @tblName + ' where ' +
@ID
+ ' <(select min(' + @ID + ') from (' +
@SqlSelect
+ ' top '
+ Cast(@pageSize*(@page-1) AS VARCHAR(20))
+ ' ' + @ID + ' from ' + @tblName +
' where (1=1) '
+ @strCondition + ' order by ' + @fldSort +
' '
+ @strFSortType + ') AS TBMinID)' + ' '
+ @strCondition + ' order by ' + @fldSort +
' '
+ @strFSortType
END
ELSE
BEGIN
SET @strTmp=@SqlSelect + ' top '
+ Cast(@pageSize AS VARCHAR(4)) + ' '
+ @fldName + ' from ' + @tblName + ' where ' +
@ID
+ ' >(select max(' + @ID + ') from (' +
@SqlSelect
+ ' top '
+ Cast(@pageSize*(@page-1) AS VARCHAR(20))
+ ' ' + @ID + ' from ' + @tblName +
' where (1=1) '
+ @strCondition + ' order by ' + @fldSort +
' '
+ @strFSortType + ') AS TBMinID)' + ' '
+ @strCondition + ' order by ' + @fldSort +
' '
+ @strFSortType
END
END
ELSE
BEGIN
SET @page = @pageIndex - @page + 1 --后半部分数据处理
IF @page <= 1 --最后一页数据显示
SET @strTmp=@SqlSelect + ' * from (' + @SqlSelect + ' top '
+ Cast(@lastcount AS VARCHAR(4)) + ' '
+ @fldName + ' from ' + @tblName + ' where (1=1) '
+ @strCondition + ' order by ' + @fldSort + ' '
+ @strSortType + ') AS TempTB' + ' order by '
+ @fldSort + ' ' + @strFSortType
ELSE IF( @Sort = 1 )
SET @strTmp=@SqlSelect + ' * from (' + @SqlSelect + ' top '
+ Cast(@pageSize AS VARCHAR(4)) + ' '
+ @fldName + ' from ' + @tblName + ' where ' + @ID
+ ' >(select max(' + @ID + ') from(' + @SqlSelect
+ ' top '
+ Cast(@pageSize*(@page-2)+@lastcount AS VARCHAR(
20))
+ ' ' + @ID + ' from ' + @tblName +
' where (1=1) '
+ @strCondition + ' order by ' + @fldSort + ' '
+ @strSortType + ') AS TBMaxID)' + ' '
+ @strCondition + ' order by ' + @fldSort + ' '
+ @strSortType + ') AS TempTB' + ' order by '
+ @fldSort + ' ' + @strFSortType
ELSE
SET @strTmp=@SqlSelect + ' * from (' + @SqlSelect + ' top '
+ Cast(@pageSize AS VARCHAR(4)) + ' '
+ @fldName + ' from ' + @tblName + ' where ' + @ID
+ ' <(select min(' + @ID + ') from(' + @SqlSelect
+ ' top '
+ Cast(@pageSize*(@page-2)+@lastcount AS VARCHAR(
20))
+ ' ' + @ID + ' from ' + @tblName +
' where (1=1) '
+ @strCondition + ' order by ' + @fldSort + ' '
+ @strSortType + ') AS TBMaxID)' + ' '
+ @strCondition + ' order by ' + @fldSort + ' '
+ @strSortType + ') AS TempTB' + ' order by '
+ @fldSort + ' ' + @strFSortType
END
END
------返回查询结果-----
EXEC Sp_executesql
@strTmp
SELECT Datediff(ms, @timediff, Getdate()) AS 耗时
--print @strTmp
SET nocount OFF
go 执行示例：
exec proc_paged_2part_selectMax 'tb_testTable','ID,userName,userPWD,userEmail',10,100000,'ID',0,null,'ID',0这种测试只在单机进行，并且没有在实际开发WEB项目中分页测试，测试项也比较单一，所以不够全面系统，但从其效率相比上，我们可以在数据库分页算法上进行有效的控制。
扫一扫
130
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
