存储过程实现大数据量快速分页的例子

前期准备如下：

/**//*插入30万条记录模拟分页操作*/
create table Table_A
(
   [Guid] [nvarchar] (40)  NOT NULL  DEFAULT (newid()),
    Client_ID nvarchar(8),
    Client_Name nvarchar(8),
    Client_Age int
)
select * from Table_A
truncate table Table_A

declare @age int
set @age=0;
begin transaction 
while (@age<=300000)
begin
insert 
into Table_A(Client_ID,Client_Name,Client_Age)
values ('A110','jhtchina',@age)
set @age=@age+1
end
IF @@ERROR <> 0
    ROLLBACK transaction
  ELSE
    COMMIT transaction
go
select * from Table_A

这里初始化了300000条数据进入Table_A
分页的存储过程如下：

/**//*建立分页的存储过程*/
CREATE PROCEDURE pagination 
@tblName   varchar(255),       -- 表名
@strGetFields varchar(1000) = '*',  -- 需要返回的列 
@fldName varchar(255)=' ',      -- 排序的字段名
@PageIndex  int =30,           -- 页码
@PageSize  int =11,          -- 页尺寸
@doCount  bit =0,   -- 返回记录总数, 非 0 值则返回
@OrderType bit =0,  -- 设置排序类型, 非 0 值则降序
@strWhere  varchar(1500) = ''  -- 查询条件 (注意: 不要加 where) 
AS
declare @strSQL   varchar(5000)       -- 主语句
declare @strTmp   varchar(110)        -- 临时变量
declare @strOrder varchar(400)        -- 排序类型
declare @pagecount int                --总页数
declare @TotalCount int
create table #t
(
         [CID]  [bigint] NOT NULL
)
/**//*
if @doCount != 0  
  begin
    if @strWhere !=''
       set @strSQL =' declare  @TotalCount int   select   @TotalCount=count(*)  from [' + @tblName + '] where '+@strWhere + 'as tblTmp  select  @TotalCount'
    else
       set @strSQL = ' declare  @TotalCount int  select  @TotalCount=count(*)    from [' + @tblName + ']' + 'as tblTmp select  @TotalCount  '
       exec sp_executesql @strSQL,N'@RecordCount int output',@RecordCount output
       exec (@strSQL) 
 */
 -- end
--else  
 -- begin
    if @OrderType != 0 --非0为降序
       begin
         set @strTmp = '<(select min'
         set @strOrder = 'order by [' + @fldName +'] desc'  --需要降序的列
         --如果@OrderType不是0，就执行降序，这句很重要！
       end
    else  
      begin
        --这时执行升序
        set @strTmp = '>(select max'
        set @strOrder = 'order by [' + @fldName +'] asc'
      end
  
  if @PageIndex = 1--@PageIndex 页码
     begin       
       if @strWhere != ''   
         -- set @strSQL = 'select top ' + str(@PageSize,len(@PageSize)) +' '+@strGetFields+ '  from [' + @tblName + '] where ' + @strWhere +' ' + @strOrder
           set @strSQL = 'select top ' + str(@PageSize,len(@PageSize)) +' ' + '  *  from [' + @tblName + '] where ' + @strWhere +' ' + @strOrder
       else
         -- set @strSQL = 'select top ' + str(@PageSize,len(@PageSize)) +' '+@strGetFields+ '  from ['+ @tblName + '] '+ @strOrder
         set @strSQL = 'select top ' + str(@PageSize,len(@PageSize)) +' ' + ' *  from ['+ @tblName + '] '+ @strOrder
       
     end
     
  else
     begin
       --以下代码赋予了@strSQL以真正执行的SQL代码
       set @strSQL ='select top ' + str(@PageSize,len(@PageSize)) +' ' + ' *  from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top '+str((@PageIndex-1)*@PageSize) + '['+ @fldName + '] from [' + @tblName + ']'+@strOrder+')as tblTmp)'+ @strOrder
       if @strWhere != ''
     begin
       set @strSQL ='select top ' + str(@PageSize,len(@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
     end
  end 
--end 
print @strSQL
exec (@strSQL)
if @doCount != 0  
  begin
    if @strWhere !=''
       set @strSQL =' insert into #t  (CID) select   count(*)     from [' + @tblName + '] where '+@strWhere  
    else
       set @strSQL = 'insert into #t  (CID)  select  count(*)      from [' + @tblName + ']' 
    exec (@strSQL) 
select @TotalCount=[cid] from #t
select @TotalCount
--select  @TotalCount/3
declare @Conditoin int
select  @Conditoin=@TotalCount  % @PageSize
--select @Conditoin
if @Conditoin !=0
begin  
set @pagecount=@TotalCount /@PageSize + 1
end
else 
begin
set @pagecount=@TotalCount /@PageSize 
end
--select @pagecount
end
GO

执行存储过程：

exec pagination  'Table_A',' Guid,Client_ID,Client_Age ','Client_Age',6,10,10,1,''

实际执行的SQL如下：

select top 10  *  
from [Table_A] 
where [Client_Age]<
(
select min([Client_Age]) 
from (
select top  50[Client_Age] 
from [Table_A]
order by [Client_Age] desc
)as tblTmp)
order by [Client_Age] desc

这个存储过程适用与大数据量的查询分页操作。

另一种方式的分页存储过程

/**//*
  经测试，在 14483461 条记录中查询第 100000 页，每页 10 条记录按升序和降序第一次时间均为 0.47 秒，第二次时间均为 0.43 秒，测试语法如下：
  exec GetRecordFromPage news,newsid,10,100000
  news 为 表名, newsid 为关键字段, 使用时请先对 newsid 建立索引。
*/
/**//*
  函数名称: GetRecordFromPage
  函数功能: 获取指定页的数据
  参数说明: @tblName      包含数据的表名
           @fldName      关键字段名
           @PageSize     每页记录数
           @PageIndex    要获取的页码
           @OrderType    排序类型, 0 - 升序, 1 - 降序
           @strWhere     查询条件 (注意: 不要加 where)
  作　　者: 
  邮　　箱: 
  创建时间:   修改时间: 
*/
CREATE PROCEDURE 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)

GO