我自己写的一个分页存储过程,请大家评评。

承影1024 2005-09-28 12:10:29
程序优点:
做到了调用方便、通用;
由于是把查询结果先存放到临时表中,所以,不需要主查询有主键;
个人测试,在2.0G、1G内存、80万条记录条件下,最长需要4秒时间;

想请高手给点指点,看看是否能够优化,或者提出更好的方案。
-------------------------------------

首先实现这个分页的通用存储过程:

--------------------------------------------------------------
-- 通用分页查询过程
-- exec p_PageRecordset_Table '#TempTable',20,1,100,0
--------------------------------------------------------------
CREATE PROCEDURE p_PageRecordset_Table
(
@TableName NVARCHAR(100),--全局临时表名称
@PageSize INT, --每页的记录条数
@PageNumber INT, --当前页面
@TotalSize INT, --总记录条数
@TotalPage INT OUTPUT --总页数
)
AS
BEGIN

SET NOCOUNT ON
DECLARE @SqlText AS NVARCHAR(1000)

-- 计算总页数
IF @PageSize>0
BEGIN
SET @TotalPage=@TotalSize/@PageSize
IF (@PageSize*@TotalPage)<>@TotalSize
SET @TotalPage=@TotalPage+1
END
ELSE
BEGIN
SET @TotalPage=1
END

-- 校正输入参数
IF @PageNumber<1 SET @PageNumber=1
IF @PageNumber>@TotalPage SET @PageNumber=@TotalPage

IF @PageSize<=0 OR @TotalSize=0
BEGIN
--如果设置PageSize小于等于0,表示不分页。
SET @SqlText='
SELECT * FROM ' + @TableName + '
DROP TABLE ' + @TableName
END
ELSE
BEGIN
SET @SqlText='
SET NOCOUNT ON

SELECT * FROM ' + @TableName + '
WHERE
' + CAST((@PageNumber-1)*@PageSize+1 AS NVARCHAR(30)) + '<=ROWNUM and ROWNUM<=' + CAST(@PageNumber*@PageSize AS NVARCHAR(30)) + '
ORDER BY ROWNUM

DROP TABLE ' + @TableName
END

-- PRINT @SqlText
EXEC(@SqlText)

End
GO


-------------------------------------
调用实例:


DECLARE @PageSize INT
DECLARE @PageNumber INT
DECLARE @TotalSize INT
DECLARE @TotalPage INT

SET @PageSize=5
SET @PageNumber=1

SET NOCOUNT ON

-- 把查询的结果放到一个临时表中,供分页处理。
SELECT
CAST(EmployeeID as INT) AS EmployeeID, -- 如果源表中有自编号的字段,要转换一下。
LastName,
FirstName,
BirthDate,
Address,
Region,
PostalCode,
Country,
IDENTITY(INT,1,1) AS ROWNUM -- 添加一个新的编号列,供分页存储过程使用。
INTO #tbl_p_Page_List -- 可以随机生成一个临时表名
FROM Employees
ORDER BY LastName,FirstName



--------------------------------------------------
-- 以下是通用的分页过程
--------------------------------------------------
--取得总记录条数
SELECT @TotalSize=@@ROWCOUNT

-- 调用通用的分页处理过程
-- 注意:临时表中必须有自动编号的字段 IDENTITY(INT,1,1) AS ROWNUM
EXEC p_PageRecordset_Table '#tbl_p_Page_List',
@PageSize,@PageNumber,@TotalSize,@TotalPage OUTPUT
--------------------------------------------------

PRINT @TotalSize
PRINT @TotalPage

GO
...全文
266 7 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
zxbyhcsdn 2005-10-18
  • 打赏
  • 举报
回复
最好不用临时表!用的人多了,系统开销很大!
承影1024 2005-10-18
  • 打赏
  • 举报
回复
用了临时表,性能方面是要差一点。运行后,我查看了执行计划,大概的时间消耗比例如下

查询1:查询成本(相对于批处理):54.33%
table select(clustered index scan) 82%
table insert 成本 15%

查询2:查询成本(相对于批处理):45.67%
table scan 成本96%
Sort 成本 4%

可以看出,调用的分页过程,基本上也占用了一半的执行时间。就是想让大家看看,是否还能优化一下。
syeerzy 2005-10-17
  • 打赏
  • 举报
回复
80W都要4秒。。。。性能不太行。 那最少的时候80W数据要多少?
张海霖 2005-10-12
  • 打赏
  • 举报
回复
up
lizq2004 2005-09-30
  • 打赏
  • 举报
回复
不错!
承影1024 2005-09-29
  • 打赏
  • 举报
回复
排序是在调用分页前的查询中完成的。这样可以更自由。
天地客人 2005-09-29
  • 打赏
  • 举报
回复
不错,最好有排序列!

27,582

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

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