随笔- 151 文章- 9 评论- 11
2、游标方式:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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,这个问题具体是因为什么我还没有查到原因!
2个高效存储过程分页及对比
前两天在网上发现了一条存储过程分页的例子,是利用mssql2005中新添加的函数ROW_NUMBER()来实现的,于是修改了下并且和以前用游标的分页函数进行了以下对比,下面分别将两个存储过程写下来。
1、使用ROW_NUMBER()的方法来实现:
1![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
/*-----------------------------------------------------------------
5
模块:分页查询存储过程
6![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
8
2008/03/10 创建 郑玉路
9![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
RETURN
45![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockEnd.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
24
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
26
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
28
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
29
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
30
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
31
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
32
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
33
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
34
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
36
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
38
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
42
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2、游标方式:
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
然后分别对15000条的数据进行测试,发现2个存储过程的耗时几乎一样,第一种方法的耗时相对要比第二种稍微快几毫秒,而且不管是查询第几页的数据基本上比较稳定,只是相对的使用起来没有第二种方法方便,但是对于百万条以上的数据的效果如何确没有进行测试,具体哪种方法更好还不得而知,而且哪位朋友如果方便的话可以帮忙修改下相应的不足之处,同时还有一个比较头痛的问题,就是再使用EXEC SP_EXECUTESQL @TPSEL...这个方法的时候,变量@TPSEL不能声明为varchar类型,只能为nvarchar,这个问题具体是因为什么我还没有查到原因!