千万级分页高效存储过程

这两天测试了前几天写的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
时间: 2024-09-25 15:48:32

千万级分页高效存储过程的相关文章

Mysql limit 优化,百万至千万级快速分页 复合索引的引用并应用于轻量级框架_Mysql

MySql 这个数据库绝对是适合dba级的高手去玩的,一般做一点1万篇新闻的小型系统怎么写都可以,用xx框架可以实现快速开发.可是数据量到了10万,百万至千万,他的性能还能那么高吗?一点小小的失误,可能造成整个系统的改写,甚至更本系统无法正常运行!好了,不那么多废话了.用事实说话,看例子: 数据表 collect ( id, title ,info ,vtype) 就这4个字段,其中 title 用定长,info 用text, id 是逐渐,vtype是tinyint,vtype是索引.这是一个

MySQL 百万级分页优化(Mysql千万级快速分页)_Mysql

以下分享一点我的经验 一般刚开始学SQL的时候,会这样写 复制代码 代码如下: SELECT * FROM table ORDER BY id LIMIT 1000, 10; 但在数据达到百万级的时候,这样写会慢死 复制代码 代码如下: SELECT * FROM table ORDER BY id LIMIT 1000000, 10; 也许耗费几十秒 网上很多优化的方法是这样的 复制代码 代码如下: SELECT * FROM table WHERE id >= (SELECT id FROM

如何优化Mysql千万级快速分页_Mysql

看例子: 数 据表 collect ( id, title ,info ,vtype) 就这4个字段,其中 title 用定长,info 用text, id 是逐渐,vtype是tinyint,vtype是索引.这是一个基本的新闻系统的简单模型.现在往里面填充数据,填充10万篇新闻. 最后collect 为 10万条记录,数据库表占用硬盘1.6G. OK ,看下面这条sql语句: select id,title from collect limit 1000,10; 很快:基本上0.01秒就OK

SQL Server 千万数量级 分页 存储过程

随着信息时代的发展信息系统的使用越来越多,信息量越来越大:当信息量越来越大,这在数据展示,特别是报表这块对系统展现效率要求越来越高,对于千万级数据量的展示必须得使用分页来展示. If object_id('SP_Pagination')is not null drop proc SP_Pagination go Create PROCEDURE SP_Pagination /**//* *******************************************************

sqlserver 千万数量级分页存储过程代码_MsSql

复制代码 代码如下: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go Create PROCEDURE [dbo].[SP_Pagination] /**//* *************************************************************** ** 千万数量级分页存储过程 ** *************************************************************** 参

sql 通用高效分页代码(存储过程是带有sql注入)(1/2)

sql 通用高效分页代码(存储过程是带有sql注入) 大家可以看到上面的存储过程中是通过一些步骤最终拼接成一个sql字符串,然后通过exec执行这个串得到分页的结果. 我们假定要做一个这样的查询,通过用户名username模糊查询用户,为了叙述方便,便于理解我们只考虑取第一页的情况,取出存储过程中取第一页的拼串行如下: 为了便于说明问题,我们可以假定@pagesize为20,@strgetfields为 '*',@tblname为useraccount,@strorder为' order by

sqlserver 千万数量级分页存储过程代码

复制代码 代码如下: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go Create PROCEDURE [dbo].[SP_Pagination] /**//* *************************************************************** ** 千万数量级分页存储过程 ** *************************************************************** 参

sql server 千万级数据处理分页

****** 对象: StoredProcedure [dbo].[sp_GetEmployee] 脚本日期: 11/12/2009 14:10:29 ******/ Create PROCEDURE [dbo].[sp_GetPageList] @tblName varchar(255), -- 表名 @strGetFields varchar(1000) = '*', -- 需要返回的列 @fldName varchar(255)='', -- 排序的字段名 @PageSize int =

MySQL千万级多表关联SQL语句调优

本文不涉及复杂的底层数据结构,通过explain解释SQL,并根据可能出现的情况,来做具体的优化,使千万级表关联查询第一页结果能在2秒内完成(真实业务告警系统优化结果).   需要优化的查询:使用explain      出现了Using temporary:      有分页时出现了Using filesort则表示使用不了索引,需要根据下面的技巧来调整语句      rows过多,或者几乎是全表的记录数:      key 是 (NULL):      possible_keys 出现过多(