2个高效存储过程分页及对比
前两天在网上发现了一条存储过程分页的例子,是利用mssql2005中新添加的函数ROW_NUMBER()来实现的,于是修改了下并且和以前用游标的分页函数进行了以下对比,下面分别将两个存储过程写下来。
1、使用ROW_NUMBER()的方法来实现:
1
2
3
4 /**/ /*-----------------------------------------------------------------
5模块:分页查询存储过程
6
7
8 2008/03/10 创建 郑玉路
9
10-------------------------------------------------------------------*/
11 CREATE PROCEDURE PAGINATION_FUNCTION
12 (
13 @strTable VARCHAR ( 1500 ) = '' , -- 表名-FROM以后的字符串 例:(单表)ORDERS\(多表)ORDERS LEFT JION CUSTOMER ON ORDERS.CUSTOMERID = ORDERS.CUSTOMERID
14 @strReturn VARCHAR ( 1500 ) = ' * ' , -- 需要返回的列 例:ORDERID,CUSTOMERID
15 @strOrderBy VARCHAR ( 1000 ) = '' , -- 排序的字段名 orderdate asc,shippeddate desc
16 @PageSize INT = 10 , -- 一页的记录数大小
17 @PageIndex INT = 1 , -- 当前页页码
18 @doCount INT = 0 output -- 返回记录总数
19 )
20 AS
21
22
23 -- 获取总记录数
24 DECLARE @tpSEL NVARCHAR ( 4000 ) -- 获取总记录数临时语句
25 SET @tpSEL = N ' SELECT @doCount=COUNT(*) FROM ' + rtrim ( @strTable )
26 EXEC sp_executesql @tpSEL , N ' @doCount int output ' , @doCount output
27 -- 获取总记录数
28
29 DECLARE @tpSEL2 VARCHAR ( 8000 )
30 DECLARE @RowStart INT -- 开始行标记
31 DECLARE @RowEnd INT -- 结束行标记
32
33 SET @RowStart = @PageSize * @PageIndex - @PageSize + 1
34 SET @RowEnd = @PageSize * @PageIndex
35
36 SET @tpSEL2 = ' SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY ' + rtrim ( @strOrderBy )
37 + ' ) AS ROWS, ' + rtrim ( @strReturn )
38 + ' FROM ' + rtrim ( @strTable )
39 + ' ) AS TEMP WHERE ROWS BETWEEN ' + rtrim ( @RowStart )
40 + ' AND ' + rtrim ( @RowEnd )
41
42 EXEC ( @tpSEL2 )
43
44 RETURN
45
46
47
2
3
4 /**/ /*-----------------------------------------------------------------
5模块:分页查询存储过程
6
7
8 2008/03/10 创建 郑玉路
9
10-------------------------------------------------------------------*/
11 CREATE PROCEDURE PAGINATION_FUNCTION
12 (
13 @strTable VARCHAR ( 1500 ) = '' , -- 表名-FROM以后的字符串 例:(单表)ORDERS\(多表)ORDERS LEFT JION CUSTOMER ON ORDERS.CUSTOMERID = ORDERS.CUSTOMERID
14 @strReturn VARCHAR ( 1500 ) = ' * ' , -- 需要返回的列 例:ORDERID,CUSTOMERID
15 @strOrderBy VARCHAR ( 1000 ) = '' , -- 排序的字段名 orderdate asc,shippeddate desc
16 @PageSize INT = 10 , -- 一页的记录数大小
17 @PageIndex INT = 1 , -- 当前页页码
18 @doCount INT = 0 output -- 返回记录总数
19 )
20 AS
21
22
23 -- 获取总记录数
24 DECLARE @tpSEL NVARCHAR ( 4000 ) -- 获取总记录数临时语句
25 SET @tpSEL = N ' SELECT @doCount=COUNT(*) FROM ' + rtrim ( @strTable )
26 EXEC sp_executesql @tpSEL , N ' @doCount int output ' , @doCount output
27 -- 获取总记录数
28
29 DECLARE @tpSEL2 VARCHAR ( 8000 )
30 DECLARE @RowStart INT -- 开始行标记
31 DECLARE @RowEnd INT -- 结束行标记
32
33 SET @RowStart = @PageSize * @PageIndex - @PageSize + 1
34 SET @RowEnd = @PageSize * @PageIndex
35
36 SET @tpSEL2 = ' SELECT * FROM ( SELECT ROW_NUMBER() OVER(ORDER BY ' + rtrim ( @strOrderBy )
37 + ' ) AS ROWS, ' + rtrim ( @strReturn )
38 + ' FROM ' + rtrim ( @strTable )
39 + ' ) AS TEMP WHERE ROWS BETWEEN ' + rtrim ( @RowStart )
40 + ' AND ' + rtrim ( @RowEnd )
41
42 EXEC ( @tpSEL2 )
43
44 RETURN
45
46
47
2、游标方式:
CREATE procedure p_splitpage
@sql nvarchar ( 4000 ), -- 要执行的sql语句
@page int = 1 , -- 要显示的页码
@pageSize int , -- 每页的大小
@pageCount int = 0 out, -- 总页数
@recordCount int = 0 out, -- 总记录数
@SearchTime int = 0 out -- 计算耗时
as
declare @usetime datetime
set @usetime = getdate ()
set nocount on
declare @p1 int
exec sp_cursoropen @p1
output, @sql , @scrollopt = 1 , @ccopt = 1 , @rowcount = @pagecount output
set @recordCount = @pageCount
select @pagecount = ceiling ( 1.0 * @pagecount / @pagesize )
, @page = ( @page - 1 ) * @pagesize + 1
exec sp_cursorfetch @p1 , 16 , @page , @pagesize
exec sp_cursorclose @p1
set @SearchTime = datediff (ms, @usetime , getdate ())
print @SearchTime
然后分别对15000条的数据进行测试,发现2个存储过程的耗时几乎一样,第一种方法的耗时相对要比第二种稍微快几毫秒,而且不管是查询第几页的数据基本上比较稳定,只是相对的使用起来没有第二种方法方便,但是对于百万条以上的数据的效果如何确没有进行测试,具体哪种方法更好还不得而知,而且哪位朋友如果方便的话可以帮忙修改下相应的不足之处,同时还有一个比较头痛的问题,就是再使用EXEC SP_EXECUTESQL @TPSEL...这个方法的时候,变量@TPSEL不能声明为varchar类型,只能为nvarchar,这个问题具体是因为什么我还没有查到原因!