开发者社区> lzhdim> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

千万级分页高效存储过程

简介: 这两天测试了前几天写的SQL2005专用分页的存储过程,当数据量达到2千多万的时候,效率相当的低,每次执行都要8秒左右(CPU:Q6600)。不过在2百多万数据量的情况下性能还是蛮不错的,在网上找了找,发现这下面的这两个,其实还是一个,不过后面那个是灵活了许多,仅供参考。
+关注继续查看
这两天测试了前几天写的SQL2005专用分页的存储过程,当数据量达到2千多万的时候,效率相当的低,每次执行都要8秒左右(CPU:Q6600)。不过在2百多万数据量的情况下性能还是蛮不错的,在网上找了找,发现这下面的这两个,其实还是一个,不过后面那个是灵活了许多,仅供参考。
复制  保存
/******
Object:  StoredProcedure [dbo].[GetRecordFromPage]
Script Date: 07/23/2008 18:42:05
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
函数名称: GetRecordFromPage
函数功能: 获取指定页的数据
参数说明: @tblName      包含数据的表名
@fldName      关键字段名
@PageSize     每页记录数
@PageIndex    要获取的页码
@OrderType    排序类型, 0 - 升序, 1 - 降序
@strWhere     查询条件 (注意: 不要加 where)
*/
ALTER PROCEDURE [dbo].[GetRecordFromPage]
@tblName      varchar(255),       -- 表名
    @fldName      varchar(255),       -- 字段名
    @PageSize     int = 10,           -- 页尺寸
    @PageIndex    int = 1,            -- 页码
    @OrderType    bit = 0,            -- 设置排序类型, 非 0 值则降序
    @strWhere     varchar(2000) = ''  -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL   varchar(6000)       -- 主语句
declare @strTmp   varchar(1000)       -- 临时变量
declare @strOrder varchar(500)        -- 排序类型

if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where (' + @strWhere + ')'
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
exec (@strSQL)

这是我目前见过效率最高的,不过它的order by 是不是有点儿问题,不能指定某一个,还有没有返回总记录数。于是有了下面这个改进的。

如果需要总页数还可以再改一下,不过目前已经足够用了,当然,当达到千万数据量后,比上面的效率上稍微低了一点点,不过还不算太坏,

比较推荐下面这个。
复制  保存
/******
Object:  StoredProcedure [dbo].[usp_GetRecordFromPage]
Script Date: 07/23/2008 18:42:37
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE       PROCEDURE [dbo].[usp_GetRecordFromPage]
@tblName       varchar(1000),        -- 表名
    @SelectFieldName    varchar(4000),              -- 要显示的字段名(不要加select)
    @strWhere       varchar(4000),              -- 查询条件(注意: 不要加 where)
    @OrderFieldName      varchar(255),               -- 排序索引字段名
    @PageSize       int ,                 -- 页大小
    @PageIndex      int = 1,                  -- 页码
    @iRowCount      int output,                 -- 返回记录总数
    @OrderType      bit = 0                  -- 设置排序类型, 非 0 值则降序

AS
declare @strSQL    varchar(4000)       -- 主语句
declare @strTmp    varchar(4000)        -- 临时变量
declare @strOrder  varchar(400)        -- 排序类型
declare @strRowCount    nvarchar(4000)      -- 用于查询记录总数的语句

set @OrderFieldName=ltrim(rtrim(@OrderFieldName))
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' + @OrderFieldName +' desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' + @OrderFieldName +' asc'
end
set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from '
+ @tblName + ' where ' + @OrderFieldName + @strTmp + '('
+ right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)
+ @OrderFieldName + ' from ' + @tblName  + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from '
+ @tblName + ' where ' + @OrderFieldName + @strTmp + '('
+ right(@OrderFieldName,len(@OrderFieldName)-charindex('.',@OrderFieldName)) + ') from (select top ' + str((@PageIndex-1)*@PageSize)
+ @OrderFieldName + ' from ' + @tblName + ' where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere
set @strSQL = 'select top ' + str(@PageSize) + @SelectFieldName+' from '
+ @tblName + @strTmp + ' ' + @strOrder
end
exec(@strSQL)
if @strWhere!=''
begin
set @strRowCount = 'select @iRowCount=count(*) from ' + @tblName+' where '+@strWhere
end
else
begin
set @strRowCount = 'select @iRowCount=count(*) from ' + @tblName
end
exec sp_executesql @strRowCount,N'@iRowCount int out',@iRowCount out

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
通用分页存储过程,干货无污染
通用分页存储过程,干货无污染
6 0
分页存储过程
IF EXISTS(SELECT Name FROM SYSOBJECTS WHERE Name='PrcPager' AND TYPE='P')DROP PROCEDURE PrcPager...
528 0
通用分页存储过程
/*--实现分页的通用存储过程 显示指定表、视图、查询结果的第X页对于表中主键或标识列的情况,直接从原表取数查询,其它情况使用临时表的方法如果视图或查询结果中有主键,不推荐此方法如果使用查询语...
573 0
通用存储过程.分页存储过程
/*名称:spAll_ReturnRows输入:输出:调用:  EXEC spAll_ReturnRows 'SELECT  * FROM 表名', 页号, 返回记录数, '主键', '排序字段'  spAll_ReturnRows 'SELECT  * FROM all_Categories',2,10,'[ID]','[ID]'说明:[百万级]通用存储过程.
529 0
+关注
lzhdim
人在20岁以意志力著称,在30岁以智慧取胜,在40岁则靠的是理智的判断。 一个人只有时刻保持幸福快乐的感觉,才会使自己更加热爱生命,热爱生活。只有快乐,愉快的心情,才是创造力和人生动力的源泉;只有不断自己创造快乐,与自己快乐相处的人,才能远离痛苦与烦恼,才能拥有快乐的人生。
521
文章
4
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载