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

来源：互联网发布：一体智能马桶知乎编辑：程序博客网时间：2024/04/29 17:17

转载:http://www.cnblogs.com/jirigala/archive/2012/03/08/2385895.html

应该很多人也遇到过这个问题，大概在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 值则降序ASBEGIN    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