应该很多人也遇到过这个问题,大概在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

    — 获取几条数据? 吉日嘎拉 20101102 更新
    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

 

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