`
zhouaihuafly
  • 浏览: 16799 次
  • 性别: Icon_minigender_1
  • 来自: 珠海
文章分类
社区版块
存档分类
最新评论

大数据量分页存储过程效率测试附代码

软件测试SQLSQL Server算法互联网 
阅读更多
在项目中，我们经常遇到或用到分页，那么在大数据量（百万级以上）下，哪种分页算法效率最优呢？我们不妨用事实说话。


测试环境

硬件：CPU 酷睿双核T5750  内存：2G
软件:Windows server 2003    +   Sql server 2005

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','lli0077@yahoo.com.cn')
    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次取其平均值
测试结果显示：
存过             第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
4row_number            0ms           0ms          34ms               365ms             710ms      2
5临时表                    780ms         796ms        798ms           780ms             805ms      4

输出窗口复制  保存

select max >row_number>not in>临时表>中间变量

于是我对效率最高的select max方法用2分法进行了扩展，代码取自互联网，我修改了ASC排序时取不到值的BUG，测试结果:
存过    第2页耗时    第1000页    第10000页   第100000页  第199999页效率   排行
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项目中分页测试，测试项也比较单一，所以不够全面系统，但从其效率相比上，我们可以在数据库分页算法上进行有效的控制。
1
顶
0
踩
分享到：
| Spring IoC容器中Bean的作用范围详解
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    高效分页存储过程

    ### 高效分页存储过程解析与应用 在数据库操作中，分页是常见的需求之一，尤其是在处理大量数据时，合理的分页策略不仅能提升用户体验，还能显著提高系统的响应速度和性能。本文将深入分析一个高效的分页存储过程，...

    sql 分页存储过程

    "sql分页存储过程"是指利用存储过程实现数据查询时的分页功能，这在大数据量的查询中尤为重要，因为直接一次性返回所有结果可能会消耗大量系统资源并导致响应时间过长。 分页查询是数据库中常用的一种技术，它允许...

    最牛的asp+mssql存储过程带条件分页代码

    描述中提到，这个存储过程经过50万条数据的测试，证明其在处理大量数据时具有良好的性能。这是因为存储过程在首次被编译后，其执行计划会被缓存，后续的调用只需查找缓存的执行计划，避免了重复解析和编译的过程，...

    sql多表分页存储过程

    ### SQL多表分页存储过程解析 #### 标题与描述概述 该存储过程主要针对SQL Server 2005/2000环境下的多表分页查询进行了实现，并且已在SQL环境下测试通过。其核心功能是为用户提供一种灵活且高效的多表分页查询...

    mysql存储过程通用分页

    在处理大量数据时，特别是进行分页查询，存储过程可以提高效率，减少网络传输，同时提供更好的代码组织和管理。本篇文章将深入探讨MySQL存储过程在实现通用分页中的应用。 首先，让我们理解什么是分页。分页是...

    c#大数据量分页 大型数据分页程序

    在.NET开发环境中，C#是一种常用的编程语言，尤其在企业级应用中，处理大数据量的分页查询是一项常见的任务。本篇文章将详细讲解如何在C#中实现高效的大数据量分页程序，以及如何利用提供的源码进行实践。 首先，...

    实现千万级数据分页的存储过程

    ### 实现千万级数据分页的存储过程 #### 背景与意义 在数据库管理中，处理大量数据时，如何高效地进行分页显示是一个常见的挑战。传统的SQL查询方式在面对海量数据时可能会出现性能瓶颈，导致响应时间过长。因此，...

    sql oracle bootstrap海量存储过程jquery分页

    描述中提到的资源包括异步海量数据分页存储过程，这表明在后端，开发者使用了异步技术来处理大量数据的分页请求，以避免阻塞主线程，提高系统的响应速度。同时，源码可以直接应用于项目中，这对于开发者来说是一个...

    分页存储过程(用存储过程实现数据库的分页代码)

    总结来说，使用存储过程实现分页是一种高效且灵活的方法，它能够有效地处理大量数据，提供良好的用户体验，同时减少网络传输的负担。然而，需要注意的是，虽然存储过程可以提高性能，但过度依赖存储过程也可能导致...

    web三层无存储过程分页Demo

    同时，考虑到大数据量时的性能，可以采用缓存策略（如Redis）或者延迟加载技术。 - 对于可扩展性，设计良好的API接口可以让其他部分更方便地接入或替换分页功能，例如，支持多种数据库或更换分页算法。 综上所述，...

    自定义分页 分层 无存储过程

    效率是任何系统的关键考虑因素，尤其是在大数据量的场景下。尽管没有使用存储过程，但我们可以通过其他方式优化查询性能，例如：合理设计索引、使用JOIN优化、减少不必要的数据传输等。此外，还可以利用缓存策略（如...

    asp.net 存储过程分页【测试过】

    在Web开发中，分页是一项常用的功能，尤其当数据量较大时，合理的分页处理不仅能提升用户体验，还能提高系统的性能。ASP.NET框架提供了丰富的功能来实现这一需求。本文将通过一个具体的示例——“asp.net 存储过程...

    实现千万级数据的分页显示

    ### 实现千万级数据的分页...通过对SQL查询的优化、动态生成SQL以及使用存储过程等方式，成功实现了在短时间内高效获取大量数据的功能。该方案不仅适用于当前的需求，也为未来处理更大规模数据集提供了有力的技术支撑。

    存储过程分页以及实现.docx

    在IT领域，数据库管理和优化是至关重要的，尤其是在处理大量数据时。存储过程是数据库中预编译的...这个例子展示了如何结合存储过程和C#代码来实现这一目标，提供了一种实用的方法来处理和展示大规模数据的分页结果。

    一个梦想的三层架构+存储过程后台分页+AspNetPager前台分页的经典例子

    分页是Web应用程序中常见的功能，尤其是在处理大量数据时，可以提高用户体验。AspNetPager是一个用于ASP.NET的前端分页控件，它允许用户以分页的方式浏览大量的记录，而无需一次性加载所有数据，这有助于减少服务器...

    c#（vs2008）、sql2005存储过程、三层架构实现分页

    在分页场景中，存储过程通常用于在数据库端执行分页查询，减少网络传输的数据量。通过在存储过程中设置适当的参数，可以轻松地获取特定页码的数据。 三层架构是一种常见的软件设计模式，它将应用程序分为三个主要...

    sql2000分页存储过程优化

    在SQL Server 2000中，分页查询是一个常见的需求，特别是在大数据量的表中，用户可能希望一次只加载一部分数据，以提高网页或应用的加载速度和用户体验。本篇将详细介绍三个常用的分页方法，并对它们的执行效率进行...

Magicbox
Global site tag (gtag.js) - Google Analytics