2个高效存储过程分页及对比

        前两天在网上发现了一条存储过程分页的例子，是利用mssql2005中新添加的函数ROW_NUMBER()来实现的，于是修改了下并且和以前用游标的分页函数进行了以下对比，下面分别将两个存储过程写下来。
        1、使用ROW_NUMBER()的方法来实现：

 1
 2
 3
 4/**//*-----------------------------------------------------------------
 5模块：分页查询存储过程
 6
 7
 8    2008/03/10    创建    郑玉路
 9
10-------------------------------------------------------------------*/
11CREATE 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)
20AS
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,这个问题具体是因为什么我还没有查到原因！