大数据量分页存储过程效率测试
我首先写了五个常用存储过程：
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 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 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 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 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项目中分页测试，测试项也比较单一，所以不够全面系统，但从其效率相比上，我们可以在数据库分页算法上进行有效的控制。
声明：该文观点仅代表作者本人，入门客AI创业平台信息发布平台仅提供信息存储空间服务，如有疑问请联系rumenke@qq.com。
- 上一篇： Tomcat 9.0安装配置
- 下一篇：没有了
