解決幾百萬條以上資料分頁讓人蛋疼的 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

          SET @SQLRowCount = 'SELECT @RecordCount=COUNT(1) FROM ' + @TableName

    END

    -- SELECT @RecordCount=@@ROWCOUNT

    EXEC sp_executesql @SQLRowCount, N'@RecordCount INT OUT', @RecordCount out

       SET @RecordCount = 0

    -- 這裡是控制頁數最多少

    SET @PageCount = @RecordCount / @PageSize + 1

    -- 這裡檢查目前頁的有效性

    IF (@PageIndex < 1)

        SET @PageIndex = 1

    -- 這裡限制最後一頁的有效性

    IF (@PageIndex > @PageCount)

        SET @PageIndex = @PageCount

    IF @SortDire != 'ASC'

        SET @SQLTemp = '<(SELECT MIN'

        SET @SQLOrder = ' ORDER BY ' + @SortExpression + ' DESC'

    ELSE

        set @SQLTemp = '>(SELECT MAX'

        set @SQLOrder = ' ORDER BY ' + @SortExpression + ' ASC'

    -- 這裡是調試資訊

    -- SELECT @SQLOrder

    -- 擷取幾條資料？ 吉日嘎拉 2010-11-02 更新

    SET @TopN = @RecordCount - @PageSize * (@PageIndex - 1)

    IF @TopN > @PageSize

        SET @TopN = @PageSize

    SET @TopLimit = @PageSize * (@PageIndex - 1)

    IF @TopLimit > @RecordCount

        SET @TopLimit = @RecordCount

    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

        -- 第一頁的顯示效率提高

        SET @SQLTemp = ''

            SET @SQLTemp = ' WHERE ' + @WhereConditional

        SET @commandText = 'SELECT TOP ' + STR(@TopN) + ' ' + @SelectField 

                          + ' FROM ' + @TableName + @SQLTemp + ' ' + @SQLOrder

    BEGIN    

        -- 解決大資料最有一頁卡死的問題

        IF @PageIndex = @PageCount

            IF @SortDire = 'ASC'

            BEGIN

                SET @SQLOrder = ' ORDER BY ' + @SortExpression + ' DESC'

            END

            ELSE

                SET @SQLOrder = ' ORDER BY ' + @SortExpression + ' ASC'

            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

    EXEC (@commandText)

    -- 這個是調試程式用的

    -- SELECT @commandText

END

将權限管理、工作流管理做到我能力的極緻，一個人隻能做好那麼很少的幾件事情。

About

吉日嘎拉(蒙古語為吉祥如意)，2000年畢業于黑龍江大學計算機系軟體專業，目前定居杭州，典型的IT軟體土鼈一個，外号“軟體包工頭”。

通用權限管理系統元件(GPM - General Permissions Manager)自2003年開始釋出，目前是國内注冊使用者和免費盜版使用者最多的權限管理系統，是各種資訊管理系統開發中徹底的權限解決方案。本元件支援多種主流資料庫（Oracle、sqlsever、db2、mysql），功能強大，使用友善，代碼簡潔，思路嚴謹，被廣大支援者稱為權限管理系統中的“走火入魔級權限管理系統”。

精心維護通用權限管理系統元件（GPM - General Permissions Manager）有8年多,3年的不斷推廣,20萬行經典的業務邏輯積累,經過上萬次的調試修正,經曆了四百個付費客戶,上百軟體公司的實戰開發。

11年以上開發經驗，外企工作5年,上市公司3年,獨立經營軟體公司2年，主持研發部門管理工作4年以上。

将權限管理、工作流做到我能力的極緻，一個人隻能做好那麼很少的幾件事情。

QQ:252056973，Mail:[email protected]

通用權限管理子產品的嚴謹設計定位、精心編碼實作、不斷維護推廣、持續優化改進，主要是為了實作一個可以高度重複利用勞動成果的工具軟體并有償提供給所需的人們，另想成為國人值得驕傲的知名軟體功能子產品。

可供國内管理類開發人員在日常工作中進行靈活二次開發利用的子產品，開發管理類軟體的必備工具之一，我們的目标就是讓程式員早點兒回家休息。

本文轉自jirigala_bao 51CTO部落格，原文連結:http://blog.51cto.com/jirigala/813110