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

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

/*
  经测试，在 14483461 条记录中查询第 100000 页，每页 10 条记录按升序和降序第一次时间均为 0.47 秒，第二次时间均为 0.43 秒，测试语法如下：
  exec GetRecordFromPage news,newsid,10,100000
  news 为 表名, newsid 为关键字段, 使用时请先对 newsid 建立索引。
*/

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

分页SQL语句2

 这个存储过程执行的速度还不错.500W速度分页只要2秒.
-------------------------------------------------------

SQL code

   
   

    
    
    
    

    
    create
    
       
    
    procedure
    
     SqlDataPaging

    
    @tbName
    
     
    
    varchar
    
    (
    
    255
    
    ),        
    
    --
    
    表名
    
    

    
    @tbFields
    
     
    
    varchar
    
    (
    
    1000
    
    ),      
    
    --
    
    返回字段
    
    

    
    @OrderField
    
     
    
    varchar
    
    (
    
    255
    
    ),   
    
    --
    
    排序的字段名
    
    

    
    @PageSize
    
     
    
    int
    
    ,                 
    
    --
    
    页尺寸
    
    

    
    @PageIndex
    
     
    
    int
    
    ,                
    
    --
    
    页码
    
    

    
    @OrderType
    
     
    
    bit
    
    ,                
    
    --
    
    排序类型,1是升序,0是降序
    
    

    
    @strWhere
    
     
    
    varchar
    
    (
    
    1500
    
    ),    
    
    --
    
    查询条件
    
    

    
    @Total
    
     
    
    int
    
     output            
    
    --
    
    返回总记录数
    
    

    
    as
    
    

    
    declare
    
     
    
    @strSql
    
     
    
    varchar
    
    (
    
    5000
    
    )     
    
    --
    
    主语句
    
    

    
    declare
    
     
    
    @strOrder
    
     
    
    varchar
    
    (
    
    200
    
    )    
    
    --
    
    排序
    
    

    
    declare
    
     
    
    @strSqlCount
    
     
    
    nvarchar
    
    (
    
    500
    
    )
    
    --
    
    查询记录总数主语句
    
    

    
    declare
    
     
    
    @strtemp
    
     
    
    varchar
    
    (
    
    50
    
    )      
    
    --
    
    排序附加语句
    
    
--
    
    ------------排序-----------------0是降序,1未升序
    
    

    
    set
    
     
    
    @Total
    
    =
    
    0
    
     

    
    if
    
     
    
    @OrderType
    
     
    
    !=
    
    0
    
    
    
    
    begin
    
    
        
    
    set
    
     
    
    @strtemp
    
    =
    
     
    
    '
    
    >=(select max(
    
    '
    
    
        
    
    set
    
     
    
    @strOrder
    
    =
    
    '
    
    order by 
    
    '
    
     
    
    +
    
     
    
    @OrderField
    
     
    
    +
    
     
    
    '
    
     asc 
    
    '
    
    
    
    
    end
    
    

    
    else
    
    
    
    
    begin
    
    
        
    
    set
    
     
    
    @strtemp
    
    =
    
     
    
    '
    
    <=(select min(
    
    '
    
    
        
    
    set
    
     
    
    @strOrder
    
    =
    
    '
    
    order by 
    
    '
    
    +
    
     
    
    @OrderField
    
     
    
    +
    
     
    
    '
    
     desc
    
    '
    
    
    
    
    end
    
    

    
    --
    
    ------------总记录数---------------
    
    

    
    if
    
     
    
    @strWhere
    
     
    
    !=
    
    ''
    
    
    
    
    begin
    
    
        
    
    set
    
     
    
    @strSqlCount
    
    =
    
    '
    
    Select @TotalCout=count(*) from  
    
    '
    
     
    
    +
    
     
    
    @tbName
    
     
    
    +
    
     
    
    '
    
     where  
    
    '
    
    +
    
     
    
    @strWhere
    
    
    
    
    end
    
    

    
    else
    
    
    
    
    begin
    
    
        
    
    set
    
     
    
    @strSqlCount
    
    =
    
    '
    
    Select @TotalCout=count(*) from  
    
    '
    
     
    
    +
    
     
    
    @tbName
    
    
    
    
    end
    
    

    
    --
    
    ------------分页为第一页------------
    
    

    
    if
    
     
    
    @PageIndex
    
     
    
    =
    
    1
    
     
    
    or
    
     
    
    @PageIndex
    
     
    
    <=
    
    0
    
    
    
    
    begin
    
    
        
    
    set
    
     
    
    @PageIndex
    
     
    
    =
    
    1
    
    
        
    
    if
    
     
    
    @strWhere
    
     
    
    !=
    
    ''
    
    
            
    
    begin
    
    
                
    
    set
    
     
    
    @strSql
    
     
    
    =
    
     
    
    '
    
    Select top
    
    '
    
     
    
    +
    
    str
    
    (
    
    @PageSize
    
    )
    
    +
    
     
    
    '
    
     
    
    '
    
     
    
    +
    
     
    
    @tbFields
    
     
    
    +
    
     
    
    '
    
      from 
    
    '
    
    +
    
     
    
    @tbName
    
     
    
    +
    
    '
    
     where 
    
    '
    
     
    
    +
    
     
    
    @strWhere
    
     
    
    +
    
     
    
    '
    
     
    
    '
    
     
    
    +
    
     
    
    @strOrder
    
    
            
    
    end
    
    
        
    
    else
    
    
            
    
    begin
    
    
                
    
    set
    
     
    
    @strSql
    
     
    
    =
    
     
    
    '
    
    Select top
    
    '
    
     
    
    +
    
    str
    
    (
    
    @PageSize
    
    )
    
    +
    
     
    
    '
    
     
    
    '
    
     
    
    +
    
     
    
    @tbFields
    
     
    
    +
    
     
    
    '
    
      from 
    
    '
    
    +
    
     
    
    @tbName
    
     
    
    +
    
     
    
    '
    
     
    
    '
    
     
    
    +
    
     
    
    @strOrder
    
    
            
    
    end
    
    
    
    
    end
    
    

    
    else
    
     
    
    
    begin
    
    
        
    
    if
    
     
    
    @strWhere
    
     
    
    !=
    
    ''
    
    
            
    
    begin
    
    
                
    
    set
    
     
    
    @strSql
    
    =
    
    '
    
    Select top 
    
    '
    
     
    
    +
    
    str
    
    (
    
    @PageSize
    
    ) 
    
    +
    
     
    
    '
    
     
    
    '
    
     
    
    +
    
     
    
    @tbFields
    
     
                            
    
    +
    
    '
    
     from 
    
    '
    
     
    
    +
    
     
    
    @tbName
    
     
    
    +
    
     
    
    '
    
     where 
    
    '
    
    +
    
    @OrderField
    
     
    
    +
    
    '
    
     
    
    '
    
    
                            
    
    +
    
     
    
    @strtemp
    
    +
    
     
    
    '
    
     
    
    '
    
     
    
    +
    
    @OrderField
    
    +
    
     
    
    '
    
    ) from (select top 
    
    '
    
    
                            
    
    +
    
    str
    
    ((
    
    @PageIndex
    
    -
    
    1
    
    )
    
    *
    
    @PageSize
    
    ) 
    
    +
    
     
    
    '
    
     
    
    '
    
    +
    
    @OrderField
    
     
    
    +
    
    '
    
     from 
    
    '
    
    +
    
    @tbName
    
    
                            
    
    +
    
    '
    
     where 
    
    '
    
    +
    
     
    
    @strWhere
    
    +
    
     
    
    '
    
     
    
    '
    
    +
    
     
    
    @strOrder
    
     
    
    +
    
    '
    
    ) as tb)
    
    '
    
    +
    
     
    
    @strOrder
    
    
            
    
    end
    
    
        
    
    else
    
    
            
    
    begin
    
    
                
    
    set
    
     
    
    @strSql
    
    =
    
    '
    
    Select top 
    
    '
    
     
    
    +
    
     
    
    str
    
    (
    
    @PageSize
    
    ) 
    
    +
    
     
    
    '
    
     
    
    '
    
     
    
    +
    
     
    
    @tbFields
    
     
                            
    
    +
    
    '
    
     from 
    
    '
    
     
    
    +
    
     
    
    @tbName
    
     
    
    +
    
     
    
    '
    
     where 
    
    '
    
    +
    
    @OrderField
    
     
    
    +
    
     
    
    '
    
     
    
    '
    
    
                            
    
    +
    
     
    
    @strtemp
    
    +
    
     
    
    '
    
     
    
    '
    
     
    
    +
    
    @OrderField
    
    +
    
     
    
    '
    
    ) from (select top 
    
    '
    
    
                            
    
    +
    
    str
    
    ((
    
    @PageIndex
    
    -
    
    1
    
    )
    
    *
    
    @PageSize
    
    ) 
    
    +
    
     
    
    '
    
     
    
    '
    
    +
    
    @OrderField
    
     
    
    +
    
    '
    
     from 
    
    '
    
    +
    
    @tbName
    
     
                            
    
    +
    
     
    
    '
    
     
    
    '
    
    +
    
     
    
    @strWhere
    
     
    
    +
    
    '
    
     
    
    '
    
    +
    
     
    
    @strOrder
    
     
    
    +
    
    '
    
    ) as tb)
    
    '
    
    +
    
     
    
    @strOrder
    
    
            
    
    end
    
    
    
    
    end
    
    

    
    exec
    
     sp_executesql 
    
    @strSqlCount
    
    ,N
    
    '
    
    @TotalCout int output
    
    '
    
    ,
    
    @Total
    
     output

    
    exec
    
    (
    
    @strSql
    
    )