解决几百万条以上数据分页让人蛋疼的 SQL2005, SQL2008最后一页卡死,最后一页查询超时的源码-阿里云开发者社区

开发者社区> 安全> 正文
登录阅读全文

解决几百万条以上数据分页让人蛋疼的 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 @Sort 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




本文转自 jirigala 51CTO博客,原文链接:http://blog.51cto.com/2347979/1196318,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
+ 订阅

云安全开发者的大本营

其他文章
最新文章
相关文章