前言
之前在做 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 @[email protected] 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 @[email protected] 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 (@[email protected]*@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 @[email protected] + @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
来源: http://www.bubuko.com/infodetail-3194077.html