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

http://topic.csdn.net/u/20080421/15/b0e4c874-7c72-481d-8a95-37c2ce1f7d89.html

这个存储过程执行的速度还不错.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
    
    )
   
   

SQL code

   
   

    
    
    
    CREATE
    
     
    
    PROC
    
     sp_PageView

    
    @tbname
    
         sysname,             
    
    --
    
    要分页显示的表名
    
    

    
    @FieldKey
    
       
    
    nvarchar
    
    (
    
    1000
    
    ),      
    
    --
    
    用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
    
    

    
    @PageCurrent
    
     
    
    int
    
    =
    
    1
    
    ,              
    
    --
    
    要显示的页码
    
    

    
    @PageSize
    
       
    
    int
    
    =
    
    10
    
    ,              
    
    --
    
    每页的大小(记录数)
    
    

    
    @FieldShow
    
      
    
    nvarchar
    
    (
    
    1000
    
    )
    
    =
    
    ''
    
    ,   
    
    --
    
    以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
    
    

    
    @FieldOrder
    
      
    
    nvarchar
    
    (
    
    1000
    
    )
    
    =
    
    ''
    
    ,  
    
    --
    
    以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC用于指定排序顺序
    
    

    
    @Where
    
         
    
    nvarchar
    
    (
    
    1000
    
    )
    
    =
    
    ''
    
    ,    
    
    --
    
    查询条件
    
    

    
    @PageCount
    
      
    
    int
    
     OUTPUT           
    
    --
    
    总页数
    
    

    
    AS
    
    

    
    SET
    
     NOCOUNT 
    
    ON
    
    

    
    --
    
    检查对象是否有效
    
    

    
    IF
    
     
    
    OBJECT_ID
    
    (
    
    @tbname
    
    ) 
    
    IS
    
     
    
    NULL
    
    

    
    BEGIN
    
    
    
    
    RAISERROR
    
    (N
    
    '
    
    对象"%s"不存在
    
    '
    
    ,
    
    1
    
    ,
    
    16
    
    ,
    
    @tbname
    
    )
    
    
    RETURN
    
    

    
    END
    
    

    
    IF
    
     
    
    OBJECTPROPERTY
    
    (
    
    OBJECT_ID
    
    (
    
    @tbname
    
    ),N
    
    '
    
    IsTable
    
    '
    
    )
    
    =
    
    0
    
    
    
    
    AND
    
     
    
    OBJECTPROPERTY
    
    (
    
    OBJECT_ID
    
    (
    
    @tbname
    
    ),N
    
    '
    
    IsView
    
    '
    
    )
    
    =
    
    0
    
    
    
    
    AND
    
     
    
    OBJECTPROPERTY
    
    (
    
    OBJECT_ID
    
    (
    
    @tbname
    
    ),N
    
    '
    
    IsTableFunction
    
    '
    
    )
    
    =
    
    0
    
    

    
    BEGIN
    
    
    
    
    RAISERROR
    
    (N
    
    '
    
    "%s"不是表、视图或者表值函数
    
    '
    
    ,
    
    1
    
    ,
    
    16
    
    ,
    
    @tbname
    
    )
    
    
    RETURN
    
    

    
    END
    
    


    
    --
    
    分页字段检查
    
    

    
    IF
    
     
    
    ISNULL
    
    (
    
    @FieldKey
    
    ,N
    
    ''
    
    )
    
    =
    
    ''
    
    

    
    BEGIN
    
    
    
    
    RAISERROR
    
    (N
    
    '
    
    分页处理需要主键（或者惟一键）
    
    '
    
    ,
    
    1
    
    ,
    
    16
    
    )
    
    
    RETURN
    
    

    
    END
    
    


    
    --
    
    其他参数检查及规范
    
    

    
    IF
    
     
    
    ISNULL
    
    (
    
    @PageCurrent
    
    ,
    
    0
    
    )
    
    <
    
    1
    
     
    
    SET
    
     
    
    @PageCurrent
    
    =
    
    1
    
    

    
    IF
    
     
    
    ISNULL
    
    (
    
    @PageSize
    
    ,
    
    0
    
    )
    
    <
    
    1
    
     
    
    SET
    
     
    
    @PageSize
    
    =
    
    10
    
    

    
    IF
    
     
    
    ISNULL
    
    (
    
    @FieldShow
    
    ,N
    
    ''
    
    )
    
    =
    
    N
    
    ''
    
     
    
    SET
    
     
    
    @FieldShow
    
    =
    
    N
    
    '
    
    *
    
    '
    
    

    
    IF
    
     
    
    ISNULL
    
    (
    
    @FieldOrder
    
    ,N
    
    ''
    
    )
    
    =
    
    N
    
    ''
    
    
    
    
    SET
    
     
    
    @FieldOrder
    
    =
    
    N
    
    ''
    
    

    
    ELSE
    
    
    
    
    SET
    
     
    
    @FieldOrder
    
    =
    
    N
    
    '
    
    ORDER BY 
    
    '
    
    +
    
    LTRIM
    
    (
    
    @FieldOrder
    
    )

    
    IF
    
     
    
    ISNULL
    
    (
    
    @Where
    
    ,N
    
    ''
    
    )
    
    =
    
    N
    
    ''
    
    
    
    
    SET
    
     
    
    @Where
    
    =
    
    N
    
    ''
    
    

    
    ELSE
    
    
    
    
    SET
    
     
    
    @Where
    
    =
    
    N
    
    '
    
    WHERE (
    
    '
    
    +
    
    @Where
    
    +
    
    N
    
    '
    
    )
    
    '
    
    


    
    --
    
    如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
    
    

    
    IF
    
     
    
    @PageCount
    
     
    
    IS
    
     
    
    NULL
    
    

    
    BEGIN
    
    
    
    
    DECLARE
    
     
    
    @sql
    
     
    
    nvarchar
    
    (
    
    4000
    
    )
    
    
    SET
    
     
    
    @sql
    
    =
    
    N
    
    '
    
    SELECT @PageCount=COUNT(*)
    
    '
    
    
        
    
    +
    
    N
    
    '
    
     FROM 
    
    '
    
    +
    
    @tbname
    
    
        
    
    +
    
    N
    
    '
    
     
    
    '
    
    +
    
    @Where
    
    
    
    
    EXEC
    
     sp_executesql 
    
    @sql
    
    ,N
    
    '
    
    @PageCount int OUTPUT
    
    '
    
    ,
    
    @PageCount
    
     OUTPUT
    
    
    SET
    
     
    
    @PageCount
    
    =
    
    (
    
    @PageCount
    
    +
    
    @PageSize
    
    -
    
    1
    
    )
    
    /
    
    @PageSize
    
    

    
    END
    
    


    
    --
    
    计算分页显示的TOPN值
    
    

    
    DECLARE
    
     
    
    @TopN
    
     
    
    varchar
    
    (
    
    20
    
    ),
    
    @TopN1
    
     
    
    varchar
    
    (
    
    20
    
    )

    
    SELECT
    
     
    
    @TopN
    
    =
    
    @PageSize
    
    ,
    
    
    @TopN1
    
    =
    
    @PageCurrent
    
    *
    
    @PageSize
    
    


    
    --
    
    第一页直接显示
    
    

    
    IF
    
     
    
    @PageCurrent
    
    =
    
    1
    
    
    
    
    EXEC
    
    (N
    
    '
    
    SELECT TOP 
    
    '
    
    +
    
    @TopN
    
    
        
    
    +
    
    N
    
    '
    
     
    
    '
    
    +
    
    @FieldShow
    
    
        
    
    +
    
    N
    
    '
    
     FROM 
    
    '
    
    +
    
    @tbname
    
    
        
    
    +
    
    N
    
    '
    
     
    
    '
    
    +
    
    @Where
    
    
        
    
    +
    
    N
    
    '
    
     
    
    '
    
    +
    
    @FieldOrder
    
    )

    
    ELSE
    
    

    
    BEGIN
    
    
    
    
    --
    
    生成主键(惟一键)处理条件
    
    

    
        
    
    DECLARE
    
     
    
    @Where1
    
     
    
    nvarchar
    
    (
    
    4000
    
    ),
    
    @s
    
     
    
    nvarchar
    
    (
    
    1000
    
    )
    
    
    SELECT
    
     
    
    @Where1
    
    =
    
    N
    
    ''
    
    ,
    
    @s
    
    =
    
    @FieldKey
    
    
    
    
    WHILE
    
     
    
    CHARINDEX
    
    (N
    
    '
    
    ,
    
    '
    
    ,
    
    @s
    
    )
    
    >
    
    0
    
    
        
    
    SELECT
    
     
    
    @s
    
    =
    
    STUFF
    
    (
    
    @s
    
    ,
    
    1
    
    ,
    
    CHARINDEX
    
    (N
    
    '
    
    ,
    
    '
    
    ,
    
    @s
    
    ),N
    
    ''
    
    ),
            
    
    @Where1
    
    =
    
    @Where1
    
    
                
    
    +
    
    N
    
    '
    
     AND a.
    
    '
    
    +LEFT
    
    (
    
    @s
    
    ,
    
    CHARINDEX
    
    (N
    
    '
    
    ,
    
    '
    
    ,
    
    @s
    
    )
    
    -
    
    1
    
    )
                
    
    +
    
    N
    
    '
    
    =
    
    '
    
    +LEFT
    
    (
    
    @s
    
    ,
    
    CHARINDEX
    
    (N
    
    '
    
    ,
    
    '
    
    ,
    
    @s
    
    )
    
    -
    
    1
    
    )
    
    
    SELECT
    
     
    
    @Where1
    
    =
    
    STUFF
    
    (
    
    @Where1
    
    +
    
    N
    
    '
    
     AND a.
    
    '
    
    +
    
    @s
    
    +
    
    N
    
    '
    
    =
    
    '
    
    +
    
    @s
    
    ,
    
    1
    
    ,
    
    5
    
    ,N
    
    ''
    
    ),
        
    
    @TopN
    
    =
    
    @TopN1
    
    -
    
    @PageSize
    
    

    
    
    --
    
    执行查询
    
    

    
        
    
    EXEC
    
    (N
    
    '
    
    SET ROWCOUNT 
    
    '
    
    +
    
    @TopN1
    
    
        
    
    +
    
    N
    
    '
    
     SELECT 
    
    '
    
    +
    
    @FieldKey
    
    
        
    
    +
    
    N
    
    '
    
     INTO # FROM 
    
    '
    
    +
    
    @tbname
    
    
        
    
    +
    
    N
    
    '
    
     
    
    '
    
    +
    
    @Where
    
    
        
    
    +
    
    N
    
    '
    
     
    
    '
    
    +
    
    @FieldOrder
    
    
        
    
    +
    
    N
    
    '
    
     SET ROWCOUNT 
    
    '
    
    +
    
    @TopN
    
    
        
    
    +
    
    N
    
    '
    
     DELETE FROM #
    
    '
    
    
        
    
    +
    
    N
    
    '
    
     SELECT 
    
    '
    
    +
    
    @FieldShow
    
    
        
    
    +
    
    N
    
    '
    
     FROM 
    
    '
    
    +
    
    @tbname
    
    
        
    
    +
    
    N
    
    '
    
     a WHERE EXISTS(SELECT * FROM # WHERE 
    
    '
    
    +
    
    @Where1
    
    
        
    
    +
    
    N
    
    '
    
    ) 
    
    '
    
    +
    
    @FieldOrder
    
    )

    
    END
   
   

哪个快?