SQL Server Data Paging的方式

2009-12-31

7633
0
SQL

前言

之前在做Portal時，因為Portal上要放一堆的Widget，所以每個找出來的資料一定要分頁，不然一堆Widget把資料全都取到AP Server，AP Server的Memory一下子就被操掛了! 所以，要針對資料做Paging，以下就是一些做法說明。

研究

ROW_NUMBER (SQL 2005以上才SUPPORT)
以下的範例是利用ROW_NUMBER產生出來的流水號，來取得6到10筆的資料，在Subquery中可再加入TOP N以減少Subquery的資料量。
FROM 
    (SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY SortColumn) AS Row, * 
        FROM ua_query) T1
WHERE  T1.Row >= 6 AND T1.Row <= 10
使用IDENTITY來建立流水號
這種方式類似使用ROW_NUMBER函式，只是流水號是我們自動去建立出來的。
1.建立Temp Table
INTO #TMP_RS
FROM ua_query 
ORDER BY SortColumn
2.利用IDENTITY_FIELD來產生流水號
  ADD ROW_NO INT IDENTITY(1, 1)
3.依流水號取出資料
    ON (A.PK = B.PK) WHERE ROW_NO >= 6 
    ORDER BY ROW_NO

使用CURSOR
使用CURSOR會很慢嗎? 聽說FETCH RELATIVE會很快哦!
1.建立PK的Cursor依Sort Order
    DYNAMIC READ_ONLY FOR 
    SELECT PK FROM FROM ua_query 
        ORDER BY SortColumn

2.使用CURSOR跳到第N筆開始(以下是第6筆開始)
    FETCH RELATIVE 6 
    FROM PCursor INTO @PK 

3.取第N頁的資料到Temp Table中
BEGIN
    INSERT @tblPK  VALUES (@PK)
    FETCH NEXT FROM PCursor INTO @PK 
    SET @PageSize = @PageSize - 1
END

4.拿剛才建立的Temp Table再跟原有SQL再Join
    (SELECT * FROM ua_query ) PG 
    INNER JOIN @tblPK tblPK 
    ON PG.PK= tblPK.PK
    ORDER BY SortColumn
有趣的發現(TOP + ORDER BY + SUBQUERY)
以下兩個QUERY，當SortColumn的內容不是唯一值時，出來的結果是不同的哦!
FROM ua_query 
ORDER BY SortColumn

SELECT A.* FROM 
(SELECT TOP  10  * 
FROM ua_query 
ORDER BY SortColumn)  A

實作

為了讓SQL 2K的DB可以分頁，利用上面的Cursor的方式建立一個SP(usp_paging)來專門做分頁。使用方式如下，

declare @count int
set @count=0
exec usp_paging @RecordCount=@count output
    ,@strSQL='select * from dbo.客戶'
    ,@strORDER='ORDER BY 公司名稱',@PageNumber='2',@PageSize='5', @PK = '客戶編號', @PK_DEF = 'nvarchar(5)'
select @count

SP的Source如下，

CREATE PROCEDURE [dbo].[usp_paging](
/****************************************************************************************
'程式代號： usp_paging
'程式名稱： 將資料做分頁for sql2k
'目　　的： 傳入SQL、第N頁、每頁筆數，回傳該頁的資料，及全部資料筆數
'限         制： SQL長度不可大於4000
declare @count int
set @count=0
exec usp_paging @RecordCount=@count output
    ,@strSQL='select * from dbo.客戶'
    ,@strORDER='ORDER BY 公司名稱',@PageNumber='2',@PageSize='5', @PK = '客戶編號', @PK_DEF = 'nvarchar(5)'
select @count
select * from dbo.客戶 ORDER BY 公司名稱
****************************************************************************************/
@strSQL nvarchar(4000),
@strORDER nvarchar(1000), 
@RecordCount int = 0 out,
@PageNumber int=1, 
@PageSize int=5,
@PK varchar(100),
@PK_DEF varchar(100)
)
AS

    SET NOCOUNT ON
    DECLARE @EndRow int, @StartRow int
  DECLARE @execSQL nvarchar(4000), @joinSQL nvarchar(4000)

  --取得總筆數
  SELECT @execSQL= 'SELECT COUNT(*) FROM (' + @strSQL + ' ) A'
    CREATE TABLE #RECORD_CNT (RECORD_COUNT INT)
    INSERT INTO #RECORD_CNT
        EXEC (@execSQL)
    SELECT @RecordCount = RECORD_COUNT FROM #RECORD_CNT
    --取得總筆數
    
    --取得開始位置及結束位置
    SELECT @StartRow= (@PageNumber-1)*@PageSize+1 
    SELECT @EndRow=CASE WHEN (@RecordCount-@PageNumber*@PageSize)>0 THEN 
                              @PageNumber * @PageSize
                        WHEN (@RecordCount >= @StartRow) THEN
                              @RecordCount
                        ELSE 0 END
    
 
    DECLARE @strPageSize varchar(50)
    DECLARE @strStartRow varchar(50)
    DECLARE @strEndRow varchar(50)

    SET @strPageSize = CAST(@PageSize AS varchar(50))
    SET @strStartRow = CAST(@StartRow AS varchar(50))
    SET @strEndRow = CAST(@EndRow AS varchar(50))
    --組出PK的定義
    --如'col1, col2' 'varchar(20), varchar(20)' => PK_col1 varchar(20), PK_col2 varchar(20)
    DECLARE @PKColDef varchar(4000)
    
    DECLARE @strDelColumnList VARCHAR(4000), @strColumnList varchar(512), @strColumnTypeDefineList varchar(512), @strField varchar(1024)
  DECLARE @iPOS INT

  SET @strDelColumnList=''

  SET @strColumnList=RTRIM(LTRIM(@PK))
  SET @strColumnTypeDefineList=RTRIM(LTRIM(@PK_DEF))

  WHILE(LEN(@strColumnList) > 0) 
  BEGIN
        SET @iPOS=CHARINDEX(',', @strColumnList)
        IF (@iPOS=0) 
        BEGIN
            SET @strField= 'PK_' + @strColumnList + ' ' + @strColumnTypeDefineList + ' ,'
            SET @strColumnList=''
            SET @strColumnTypeDefineList = ''
        END 
        ELSE 
        BEGIN
            SET @strField= 'PK_' + RTRIM(LTRIM(LEFT(@strColumnList, @iPOS-1)))
            SET @strColumnList=RTRIM(LTRIM(SUBSTRING(@strColumnList, @iPOS+1, 4000)))
            SET @iPOS=CHARINDEX(',', @strColumnTypeDefineList)
            SET @strField = @strField + ' ' +  RTRIM(LTRIM(LEFT(@strColumnTypeDefineList, @iPOS-1))) + ' ,'
            SET @strColumnTypeDefineList=RTRIM(LTRIM(SUBSTRING(@strColumnTypeDefineList, @iPOS+1, 4000)))
      END
        
        SET @strDelColumnList = @strDelColumnList + @strField
  END
  SET @PKColDef = SUBSTRING(@strDelColumnList, 1, LEN(@strDelColumnList)-1)
    
    --將PK_col1 varchar(20), PK_col2 varchar(20) 組成SQL的定義 
    --=> @PK_col1 varchar(20), @PK_col2 varchar(20)    
    DECLARE @PKDeclareList varchar(4000)
    SET @PKDeclareList = '@' + REPLACE(@PKColDef, ',', ' ,@')
    
    --組出放PK的變數 @PK_col1, @PK_col2
    DECLARE @PKVarList varchar(4000)
    SET @PKVarList = '@PK_' + REPLACE(REPLACE(@PK, ' ', ''), ',', ',@PK_')
    
    --組出PK的JOIN tab1, tab2, pk => tab1.pk = tab2.pk
    DECLARE @TBL1 VARCHAR(64), @TBL2 VARCHAR(64)
    SET @TBL1 = 'PG' --原本的SQL SUB QUERY TABLE NAME
    SET @TBL2 = 'tblPK' --分頁放PK的TABLE NAME
    DECLARE @joinStr varchar(4000)
    DECLARE @strJOIN NVARCHAR(2000), @strREMAIN NVARCHAR(2000)

  SET @strJOIN=''

  SET @strREMAIN=RTRIM(LTRIM(@PK))

  WHILE(LEN(@strREMAIN) > 0) BEGIN
        SET @iPOS=CHARINDEX(',', @strREMAIN)
        IF (@iPOS=0) 
        BEGIN
            SET @strField= @strREMAIN
            SET @strREMAIN=''
        END 
        ELSE 
        BEGIN
            SET @strField= LEFT(@strREMAIN, @iPOS-1)
            SET @strREMAIN=RTRIM(LTRIM(SUBSTRING(@strREMAIN, @iPOS+1, 4000)))
      END
        
        IF (LEN(@strJOIN) > 0) 
          SET @strJOIN= @strJOIN + ' AND '
    
        SET @strJOIN=@strJOIN + @TBL1 + '.' + @strField + '=' + @TBL2 + '.' + @strField 
  END
  --因為有加上PK_
    SET @joinStr = REPLACE(@strJOIN, 'tblPK.','tblPK.PK_' )
    --開始執行分頁
    IF @RecordCount > 0
    BEGIN
        --有資料，使用CURSOR分頁
        exec(
        'DECLARE @PageSize int
        SET @PageSize = ' + @strPageSize + '
    
        DECLARE ' + @PKDeclareList + ' 
        DECLARE @tblPK TABLE  (' + @PKColDef + ')
    
        DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR SELECT ' + @PK + ' FROM (' +  @strSQL  + ') PG_CUR ' + @strORDER + ' 
        OPEN PagingCursor
        FETCH RELATIVE ' + @strStartRow + ' FROM PagingCursor INTO ' + @PKVarList + ' 
    
        WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
        BEGIN
                    INSERT @tblPK VALUES (' + @PKVarList + ')
                    FETCH NEXT FROM PagingCursor INTO ' + @PKVarList + ' 
                    SET @PageSize = @PageSize - 1
        END
    
        CLOSE       PagingCursor
        DEALLOCATE  PagingCursor
        SELECT * FROM (' + @strSQL + ') PG INNER JOIN @tblPK tblPK ON ' + @joinStr +  ' ' + @strORDER) 
    END
    ELSE
    BEGIN
        --沒資料，直接執行該SQL
        EXEC(@strSQL)
    END
    /*
    SQL 2005提供了ROW_NUMBER() 可做排序，所以可以輕易做出PAGING
    如下，取出第6到10的資料
    SELECT * 
    FROM 
    (
        SELECT 
        ROW_NUMBER() OVER(ORDER BY 公司名稱) AS ID_SEQNO
        , * 
        FROM dbo.客戶) PG
    WHERE (PG.ID_SEQNO BETWEEN 6 AND 10)
    
    */
GO

結論

Identity & Cursor都需要PK的資訊，Identity是花IO，Cursor是花CPU，需要視情況調整。
如果是SQL2K5以上的話，可以使用ROW_NUMBER的方式。
參考資訊:http://www.codeproject.com/KB/aspnet/PagingLarge.aspx
附上SP SOURCE:usp_paging.rar

Hi,

亂馬客Blog已移到了「亂馬客 : Re:從零開始的軟體開發生活」

請大家繼續支持 ^_^

Paging

回首頁

亂馬客

https://rainmakerho.github.io/

SQL Server Data Paging的方式

前言

研究

ROW_NUMBER (SQL 2005以上才SUPPORT)

使用IDENTITY來建立流水號

1.建立Temp Table

2.利用IDENTITY_FIELD來產生流水號

3.依流水號取出資料

使用CURSOR

1.建立PK的Cursor依Sort Order

2.使用CURSOR跳到第N筆開始(以下是第6筆開始)

3.取第N頁的資料到Temp Table中

4.拿剛才建立的Temp Table再跟原有SQL再Join

有趣的發現(TOP + ORDER BY + SUBQUERY)

實作

結論

學習網站

標籤雲

系列文章

最新留言