[注意]今天做了一个测试, 发现~~~~~~

_TMG_ 2003-04-14 03:59:25
分页方法:
1)临时表
2)游标
3)子查询

以上都上只从数据库返回一个 Page 的记录.

发现:
最快的分页方法是3), 晕倒
...全文
33 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
_TMG_ 2003-04-14
  • 打赏
  • 举报
回复
3)使用子查询
CREATE PROCEDURE [sp_ForumThreadPagination2]
@nPageSize INT,
@nPageNo INT,
@PageCount INT OUTPUT,
@RecordCount INT OUTPUT
AS

SET NOCOUNT ON
/*
SELECT @RecordCount = COUNT(*) FROM [ForumTopic] WHERE [ForumId] = 1

IF (@RecordCount % @nPageSize) > 0 BEGIN

SET @PageCount = (@RecordCount / @nPageSize) + 1

END
ELSE BEGIN

SET @PageCount = (@RecordCount / @nPageSize)

END
*/
IF @nPageNo = 1 BEGIN

SET ROWCOUNT @nPageSize
SELECT [ForumId], [TopicId] AS [ThreadId] FROM [ForumTopic] WHERE [ForumId] = 1 ORDER BY [TopicId] DESC

END
ELSE BEGIN

DECLARE @RowAffected INT
DECLARE @SQL VARCHAR(8000)

SET @RowAffected = @nPageSize * @nPageNo
--这里最好不用D-SQL, 也是没有时间去想
SET @SQL = 'SELECT TOP ' + CONVERT(VARCHAR, @nPageSize) + ' * FROM
(SELECT TOP ' + CONVERT(VARCHAR, @RowAffected) + ' [ForumId], [TopicId] FROM [ForumTopic] WHERE [ForumId] = 1 ORDER BY [TopicId] DESC) [TEMP]
ORDER BY [TopicId] ASC'
EXEC(@SQL)
END
GO

--1.得到的记录是倒序, 如果是ASP, 则不能使用 FORWARD_ONLY 的光标了:(
--2.最后一页有问题, 需要判断, 如果是ASP, 程序里面则需要判断, 不过SQL执行的速度快
--3.有啥修改意见建议, 欢迎讨论
--4.去http://isf.vicp.net:8080也可以
_TMG_ 2003-04-14
  • 打赏
  • 举报
回复
2)使用游标
CREATE PROCEDURE [sp_ForumThreadPagination1]
@nPageSize INT,
@nPageNo INT,
@PageCount INT OUTPUT,
@RecordCount INT OUTPUT
AS

SET NOCOUNT ON
/*
SELECT @RecordCount = COUNT(*) FROM [ForumTopic] WHERE [ForumId] = 1

IF (@RecordCount % @nPageSize) > 0 BEGIN

SET @PageCount = (@RecordCount / @nPageSize) + 1

END
ELSE BEGIN

SET @PageCount = (@RecordCount / @nPageSize)

END
*/
IF @nPageNo = 1 BEGIN

SET ROWCOUNT @nPageSize
SELECT [ForumId], [TopicId] AS [ThreadId] FROM [ForumTopic] WHERE [ForumId] = 1 ORDER BY [TopicId] DESC

END
ELSE BEGIN

DECLARE @StartKeyFieldValue INT
DECLARE @EndKeyFieldValue INT
DECLARE @nRowAffected INT
DECLARE @nAbsoluteStartPos INT
DECLARE @nAbsoluteEndPos INT

SET @nRowAffected = @nPageSize * @nPageNo
SET ROWCOUNT @nRowAffected
DECLARE [CertainCursor] CURSOR LOCAL STATIC READ_ONLY FOR
SELECT [TopicId] FROM [ForumTopic] WHERE [ForumId] = 1

OPEN [CertainCursor]
SET @nAbsoluteEndPos = @nRowAffected
--取得最后一页时可能会有BUG,没有时间研究
FETCH NEXT FROM [CertainCursor] INTO @StartKeyFieldValue

IF @@FETCH_STATUS = 0 BEGIN

SET @nAbsoluteStartPos = (@nPageNo - 1) * @nPageSize + 1
FETCH ABSOLUTE @nAbsoluteStartPos FROM [CertainCursor] INTO @StartKeyFieldValue

IF @@FETCH_STATUS <> 0 BEGIN
RETURN -1
END

FETCH ABSOLUTE @nAbsoluteEndPos FROM [CertainCursor] INTO @EndKeyFieldValue

IF @@FETCH_STATUS <> 0 BEGIN
RETURN -2
END

END

CLOSE [CertainCursor]
DEALLOCATE [CertainCursor]

SELECT [ForumId], [TopicId] FROM [ForumTopic] WHERE [ForumId] = 1 AND [TopicId] BETWEEN @StartKeyFieldValue AND @EndKeyFieldValue
RETURN 0
END
GO
_TMG_ 2003-04-14
  • 打赏
  • 举报
回复
测试数据 50 万条记录
查看SQL的执行计划, 发现SELECT COUNT(*) FROM....特别耗资源

1)临时表存储过程
CREATE PROCEDURE [sp_ForumThreadPagination]
@nPageSize INT,
@nPageNo INT,
@PageCount INT OUTPUT,
@RecordCount INT OUTPUT
AS

SET NOCOUNT ON
/*
SELECT @RecordCount = COUNT(*) FROM [ForumTopic] WHERE [ForumId] = 1

IF (@RecordCount % @nPageSize) > 0 BEGIN

SET @PageCount = (@RecordCount / @nPageSize) + 1

END
ELSE BEGIN

SET @PageCount = (@RecordCount / @nPageSize)

END
*/
IF @nPageNo = 1 BEGIN

SET ROWCOUNT @nPageSize
SELECT [ForumId], [TopicId] AS [ThreadId] FROM [ForumTopic] WHERE [ForumId] = 1 ORDER BY [TopicId] DESC

END
ELSE BEGIN

CREATE TABLE [#TempCertainTable](
[TempId] INT IDENTITY(1, 1),
[ForumId] INT,
[ThreadId] INT
)
DECLARE @nRowAffected INT
SET @nRowAffected = @nPageSize * @nPageNo
SET ROWCOUNT @nRowAffected

INSERT INTO [#TempCertainTable] ([ForumId], [ThreadId])
SELECT [ForumId], [TopicId]
FROM [ForumTopic]
WHERE [ForumId] = 1

SELECT [ForumId], [ThreadId] FROM [#TempCertainTable] WHERE [TempId] > (@nPageNo - 1) * @nPageSize AND [TempId] < @nPageNo * @nPageSize + 1
DROP TABLE [#TempCertainTable]
END
GO
fatcat_xp 2003-04-14
  • 打赏
  • 举报
回复
好 支持。//

最好有 最优代码可以一起探讨。
KimSoft 2003-04-14
  • 打赏
  • 举报
回复
分页现在在ASP中已经不算是一个技术性的问题,
但是却好象都不太好。
我一般用ASP自带的方法,知道这个不太好(数据量大时)
但还好ASP对小型应用反应速度还可以,所以一直都没有
重写,有好的方法代码共享啊。。。!
malefox 2003-04-14
  • 打赏
  • 举报
回复
楼主意思是不是用子查询最快呀
yonghengdizhen 2003-04-14
  • 打赏
  • 举报
回复
没看懂..

28,390

社区成员

发帖
与我相关
我的任务
社区描述
ASP即Active Server Pages,是Microsoft公司开发的服务器端脚本环境。
社区管理员
  • ASP
  • 无·法
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧