海量存储过程(500w数据,分页只需2秒)

这个存储过程执行的速度还不错.500W速度分页只要2秒,任何页数
可是有点问题就是,如果排序不是主键,ID,那么分页就会出错,不知道各位有什么好个意见,修改一下
-------------------------------------------------------

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

    
    )      

    
    --

    
    排序附加语句

    
    

--

    
    ------------排序-----------------1是降序,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

    
    



    
    begin

    
    



    
    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

    
    



    
    +

    
     

    
    @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

    
    



    
    +

    
     

    
    '

    
     

    
    '

    
    +

    
     

    
    @strOrder

    
     

    
    +

    
    '

    
    ) as tb)

    
    '

    
    +

    
     

    
    @strOrder

    
    



    
    end

    
    



    
    end

    
    



    
    exec

    
     sp_executesql 

    
    @strSqlCount

    
    ,N

    
    '

    
    @TotalCout int output

    
    '

    
    ,

    
    @Total

    
     output



    
    exec

    
    (

    
    @strSql

    
    )