2个高效存储过程分页及对比
前两天在网上发现了一条存储过程分页的例子,是利用mssql2005中新添加的函数ROW_NUMBER()来实现的,于是修改了下并且和以前用游标的分页函数进行了以下对比,下面分别将两个存储过程写下来。
1、使用ROW_NUMBER()的方法来实现:
1
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
模块:分页查询存储过程
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)
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)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.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,这个问题具体是因为什么我还没有查到原因!