解决几百万条以上数据分页让人蛋疼的 SQL2005, SQL2008最后一页卡死，最后一页查询超时的源码...

应该很多人也遇到过这个问题，大概在2年前我也遇到过标题中的问题，当时研究了几天很是纠结没能彻底解决问题，后来也找了很多方法没能解决问题。最近又遇到这个问题，实在是不解决也不行了，冷静的想了想，完善了一下分页查询的方法，现在把代码贴上，给大家参考，若有什么漏洞，请及时联系吉日嘎拉，有错我会积极修正。希望不要重复浪费生命，直接拿过去用就可以了，在通用权限管理系统组件里也用了这个方法在进行分页。

   最近维护一个每天有10万多IP访问的网站，也是用了这个分页存储过程，分页效率还可以，最后一页没在出现卡死状态，若有问题及时联系作者QQ：252056973，欢迎大家交流分享。当系统有少数几个用户实用时问题也不严重，但是系统每时每刻都有很多人访问时那就闹心了，很容易产生网站效率极低的，访问量严重下降的趋势。

   最后一页分页一卡死，整个网站的性能都会非常明显的下降，不知道为啥，微软有这个BUG一直没处理好。希望SQL2012里不要有这个问题就好了。

   参考代码如下：

  

-- =============================================
-- Author:        吉日嘎拉
-- Create date: 2012年02月23日
-- Description:    2012年02月23日编码规范化
-- =============================================
ALTER PROCEDURE [dbo].[GetRecordByPage] 
    @TableName          VARCHAR( 4000),           -- 表名
    @SelectField        VARCHAR( 4000),           -- 要显示的字段名(不要加select)
    @WhereConditional   VARCHAR( 4000),           -- 查询条件(注意: 不要加  where)
    @SortExpression     VARCHAR( 255),            -- 排序索引字段名
    @PageSize           INT =  20,                -- 页大小
    @PageIndex          INT =  1,                 -- 页码
    @RecordCount        INT OUTPUT,              -- 返回记录总数
    @SortDire           VARCHAR( 5) =  ' DESC '      -- 设置排序类型, 非  0 值则降序
AS
BEGIN

    DECLARE @commandText VARCHAR( 8000)      -- 主语句
    DECLARE @TopN INT                         -- 获取前几条记录
    DECLARE @PageCount INT                     -- 总共会是几页
    DECLARE @TopLimit INT                     -- 获取多少条记录
    DECLARE @SQLRowCount NVARCHAR( 4000)     -- 用于查询记录总数的语句
    DECLARE @SQLOrder VARCHAR( 400)          -- 排序类型
    DECLARE @SQLTemp VARCHAR( 4000)          -- 临时变量

    SET @SortExpression = LTRIM(RTRIM(@SortExpression))
    SET @SortDire = UPPER(LTRIM(RTRIM(@SortDire)))
    
    -- 这里是计算整体记录行数
    IF @RecordCount IS NULL
    BEGIN
        IF @WhereConditional !=  ''
        BEGIN
          SET @SQLRowCount =  ' SELECT @RecordCount=COUNT(1) FROM  ' + @TableName +  '  WHERE  ' + @WhereConditional
        END
        ELSE
        BEGIN
          SET @SQLRowCount =  ' SELECT @RecordCount=COUNT(1) FROM  ' + @TableName
        END
    END

    -- SELECT @RecordCount=@@ROWCOUNT
    EXEC sp_executesql @SQLRowCount, N ' @RecordCount INT OUT ', @RecordCount  out

    IF @RecordCount IS NULL
    BEGIN
       SET @RecordCount =  0
    END
    
    -- 这里是控制页数最多少
    SET @PageCount = @RecordCount / @PageSize +  1
    
    -- 这里检查当前页的有效性
    IF (@PageIndex <  1)
    BEGIN
        SET @PageIndex =  1
    END
    
    -- 这里限制最后一页的有效性
    IF (@PageIndex > @PageCount)
    BEGIN
        SET @PageIndex = @PageCount
    END

    IF @SortDire !=  ' ASC '
    BEGIN
        SET @SQLTemp =  ' <(SELECT MIN '
        SET @SQLOrder =  '  ORDER BY  ' + @SortExpression +  '  DESC '
    END
    ELSE
    BEGIN
         set @SQLTemp =  ' >(SELECT MAX '
         set @SQLOrder =  '  ORDER BY  ' + @SortExpression +  '  ASC '
    END
    
    -- 这里是调试信息
    -- SELECT @SQLOrder

    -- 获取几条数据？ 吉日嘎拉  2010- 11- 02 更新
    SET @TopN = @RecordCount - @PageSize * (@PageIndex -  1)
    IF @TopN > @PageSize
    BEGIN
        SET @TopN = @PageSize
    END

    SET @TopLimit = @PageSize * (@PageIndex -  1)
    IF @TopLimit > @RecordCount
    BEGIN
        SET @TopLimit = @RecordCount
    END

    SET @commandText =  ' SELECT TOP  ' + STR(@TopN) +  '   ' + @SelectField +  '  FROM  '
        + @TableName +  '  WHERE  ' + @SortExpression + @SQLTemp +  ' ( '
        + RIGHT(@SortExpression, LEN(@SortExpression) - CHARINDEX( ' . ', @SortExpression)) +  ' ) FROM (SELECT TOP  ' + STR(@TopLimit)
        +  '   ' + @SortExpression +  '  FROM  ' + @TableName  + @SQLOrder +  ' ) AS TableTemp) '
        + @SQLOrder

    IF @WhereConditional !=  ''
        SET @commandText =  ' SELECT TOP  ' + STR(@TopN) +  '   ' + @SelectField +  '  FROM  '
            + @TableName +  '  WHERE  ' + @SortExpression + @SQLTemp +  ' ( '
            + RIGHT(@SortExpression, LEN(@SortExpression) - CHARINDEX( ' . ',@SortExpression)) +  ' ) FROM (SELECT TOP  ' + STR(@TopLimit)
            +  '   ' + @SortExpression +  '  FROM  ' + @TableName +  '  WHERE  ' + @WhereConditional +  '   '
            + @SQLOrder +  ' ) AS TableTemp) AND  ' + @WhereConditional +  '   ' + @SQLOrder

    IF @PageIndex =  1
    BEGIN
        -- 第一页的显示效率提高
        SET @SQLTemp =  ''
        IF @WhereConditional !=  ''
            SET @SQLTemp =  '  WHERE  ' + @WhereConditional

        SET @commandText =  ' SELECT TOP  ' + STR(@TopN) +  '   ' + @SelectField 
                          +  '  FROM  ' + @TableName + @SQLTemp +  '   ' + @SQLOrder
    END
    ELSE
    BEGIN    
        -- 解决大数据最有一页卡死的问题
        IF @PageIndex = @PageCount
        BEGIN
            IF @SortDire =  ' ASC '
            BEGIN
                SET @SQLOrder =  '  ORDER BY  ' + @SortExpression +  '  DESC '
            END
            ELSE
            BEGIN
                SET @SQLOrder =  '  ORDER BY  ' + @SortExpression +  '  ASC '
            END
        
            SET @SQLTemp =  ''
            IF @WhereConditional !=  ''
                SET @SQLTemp =  '  WHERE  ' + @WhereConditional
                
            SET @commandText =  ' SELECT TOP  ' + STR(@TopN) +  '   ' + @SelectField 
                              +  '  FROM  ' + @TableName + @SQLTemp +  '   ' + @SQLOrder
            
            SET @commandText =  ' SELECT  ' + @SelectField
                              +  '  FROM ( ' + @commandText +  ' ) AS TableTemp ORDER BY  ' + @SortExpression +  '   ' + @SortDire
        END
    END
    
    EXEC (@commandText)
    
    -- 这个是调试程序用的
    -- SELECT @commandText
    
END

 

将权限管理、工作流管理做到我能力的极致，一个人只能做好那么很少的几件事情。