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

[承影1024]

程序优点：
做到了调用方便、通用；
由于是把查询结果先存放到临时表中，所以，不需要主查询有主键；
个人测试，在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

[天地客人]

不错，最好有排序列！

[承影1024]

排序是在调用分页前的查询中完成的。这样可以更自由。

[lizq2004]

不错！

[张海霖]

up

[syeerzy]

80W都要4秒。。。。性能不太行。 那最少的时候80W数据要多少？

[承影1024]

用了临时表，性能方面是要差一点。运行后，我查看了执行计划，大概的时间消耗比例如下

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

查询2：查询成本（相对于批处理）：45.67%
table scan 成本96%
Sort 成本 4%

可以看出，调用的分页过程，基本上也占用了一半的执行时间。就是想让大家看看，是否还能优化一下。

[zxbyhcsdn]

最好不用临时表！用的人多了，系统开销很大！