2008年最新海量数据分页

最新推荐文章于 2022-11-11 14:48:24 发布

nutian

最新推荐文章于 2022-11-11 14:48:24 发布

阅读量825

点赞数

分类专栏：数据库文章标签： sql output 存储 sql server 优化 go

数据库专栏收录该内容

46 篇文章 0 订阅

订阅专栏

看了很多海量数据分页，大都都是单表的，而且当数据增大时效率下降但是排序的列不能有重复值，而在sql 2005 里通过 OVER子句可以解决，不过它也只是在后台把查询结果加一列，然后所有数据查一遍，每条记录插入序号，当有上千万数据时效率也是有点低的。
所以我归纳了下，如果排序的列有重复，就用sql2005 的存储过程，否则用我写的存储过程
sql 2005的存储过程：
create PROCEDURE [dbo].[SP_Common_PageChange]
@sql nvarchar(4000), --sql字符串
@OrderString nvarchar(100),
@PageIndex int,
@PageSize int,
@RecordCount int OUT,
@PageCount int OUT
AS
/*计算页面数据*/
Declare @PageIndex1 int
declare @sqlRecordCount nvarchar(4000)
Set @PageIndex1=@PageIndex+1;
set @sqlRecordCount ='select @RecordCount=count(*) from ('+@sql+') mxf'
/*获取记录数*/
EXEC sp_executesql @sqlRecordCount,N'@RecordCount int OUTPUT',@RecordCount OUTPUT--计算总页数
SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)
SET @sql='('+@sql+') h'
/* 基于SQL SERVER 2005 */
SET @sql=N'select SerialNumber,* from (select *,row_number() over (order by '+@OrderString+')'
+'as serialnumber from '+@sql+') as T where T.SerialNumber > ( '+convert(nvarchar(100),@PageIndex)+' * '+ convert(nvarchar(100),@PageSize)+' ) and'
+' T.SerialNumber <= ( '+convert(nvarchar(100),@PageIndex1)+' * '+convert(nvarchar(100),@PageSize)+' )'
EXEC (@sql)

本人写的存储过程：

由于比通用的改进了一些，所以要复杂些。。

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- 多表之间用逗号分开，条件不带where 但是要加括号确保万一
-- 多表一定要带条件,如果是单表字段前可不加表，排序字段不可有重复列
Create procedure [dbo].[USP_DWX_SqlDataPaging]
@tbNameS varchar(255),        --表名
@tbFields varchar(1000),      --返回字段字段前加所属表，
@OrderField varchar(255),    --排序的字段名，此字段不能有重复值前面不用加表
@PageSize int,                --页尺寸
@PageIndex int,                --页码 0 or 1 表示首页， -1 表示尾页
@OrderType bit,                --排序类型,1是升序,0是降序
@strWhere varchar(1500),      -- 查询条件字段前加所属表，
@Total int output ,          --返回总记录数
@OrderValue varchar(20) = '', --排序字段上一次页最大或最小值
-- 在@OrderValue不为空的情况下使用下面条件
@OrderFieldType char(1) = 'S', --排序字段类型,"S" 字符串,"I" 整型
@PageDirection bit = 1              --点击页的方向,1是下一页,0是上一页
as
declare @strSql varchar(5000)    --主语句
declare @strOrder varchar(200)    --排序
declare @strSqlCount nvarchar(500)--查询记录总数主语句
declare @strtemp varchar(500)      --排序附加语句
declare @strWhereSql varchar(200) --带where 条件
declare @strOrderIn varchar(200)    --内查询排序
if RTRIM(LTRIM(@strWhere)) !=''
set @strWhereSql = ' where '+ @strWhere
else
set @strWhereSql = ''
--------------总记录数---------------
set @strSqlCount='Select @TotalCout=count(*) from ' + @tbNameS + @strWhereSql
--------------排序-----------------0是降序,1未升序
set @Total=0
set @strOrderIn = ''
if @OrderType !=0
begin
set @strOrder='order by ' + @OrderField + ' asc '
end
else
begin
set @strOrder='order by '+ @OrderField + ' desc'
end

if RTRIM(LTRIM(@OrderValue)) = '' --传统分页
begin
-- -- 去掉前面的表
-- declare @tmpOrderField varchar(255)
-- set @tmpOrderField = @OrderField
-- set @tmpOrderField = REVERSE(@tmpOrderField);
-- set @tmpOrderField = SUBSTRING(@tmpOrderField,0,CHARINDEX('.',@tmpOrderField))
-- set @tmpOrderField = REVERSE(@tmpOrderField);
if @OrderType !=0
begin
    set @strtemp= '>(select max(' + ' ' +@OrderField+ ') from (select top '
                  +str((@PageIndex-1)*@PageSize) + ' '+@OrderField +' from '+@tbNameS
                  + @strWhereSql+ ' '+ @strOrder +') as tb)'
end
else
begin
    set @strtemp= ' <(select min('+ ' ' +@OrderField+ ') from (select top '
                  +str((@PageIndex-1)*@PageSize) + ' '+@OrderField +' from '+@tbNameS
                  + @strWhereSql+ ' '+ @strOrder +') as tb)'
end

end
else
begin -- 根据提供的比较值

declare @oper char(3)
set @oper = ''
if @PageDirection = 1
begin
    if @OrderType != 0 -- desc 降序
    begin
      set @oper = ' > '
    end
    else
      set @oper = ' < '
end
else
begin
    if @OrderType !=0 -- desc 降序
    begin
      set @oper = ' < '
      set @strOrderin = 'order by ' + @OrderField + ' desc '
    end
    else
    begin
      set @oper = ' > '
      set @strOrderin = 'order by ' + @OrderField + ' asc '
    end
end;
if @OrderFieldType = 'S'
begin
    set @strtemp= @oper + +''''+ @OrderValue +'''';
end
else
    set @strtemp= @oper + @OrderValue--cast(@OrderValue as int);

end
--------------分页为第一页------------
if @PageIndex =1 or @PageIndex =0
begin
set @PageIndex =1
set @strSql = 'Select top ' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbNameS + @strWhereSql+ ' ' + @strOrder
end
else if @PageIndex = -1    --最后一页
begin
if @OrderType !=0
begin
    set @strOrderIn='order by ' + @OrderField + ' desc '
end
else
begin
    set @strOrderIn='order by '+ @OrderField + ' asc'
end

set @strSql = 'Select top ' +str(@PageSize)+ ' ' + @tbFields + ' from '+ @tbNameS + @strWhereSql + ' ' + @strOrderIn
set @strSql = 'Select * from (' + @strSql + ' ) t '+ @strOrder;
end
else
begin
declare @temp varchar(200)
set @temp = ''
if RTRIM(LTRIM(@strWhere)) != ''
    set @temp = ' and ' + @strWhere

if if RTRIM(LTRIM(@OrderValue)) = ''
    set @strSql = 'Select top ' +str(@PageSize) + ' ' + @tbFields
                              +' from ' + @tbNameS + ' where '+@OrderField +' '
                              + @strtemp + ' ' + @temp + @strOrder
else
begin
    set @strSql = 'Select top ' +str(@PageSize) + ' ' + @tbFields
                              +' from ' + @tbNameS + ' where '+@OrderField +' '
                              + @strtemp + ' ' + @temp + @strOrderIn
    set @strSql = 'select * from(' + @strSql + ') tb ' + @strOrder
end
end
exec sp_executesql @strSqlCount,N'@TotalCout int output',@Total output
exec(@strSql)