写数据分页的存储过程

写数据分页的存储过程

依稀记得初初向一位师兄推荐.NET平台的时候,用的例子就是一个简单数据表格的展现,师兄看完例子后劈头盖脸就问这数据分页怎么做?性能如何?对大量的数据支持如何?...那时的我其实也十分懵懂,水平充其量就是照着ScottGu博客中的文章生硬模仿而已,对与这些问题没有什么感性的认知.进入职场后,面对记录数上百万级,甚至上千万级的数据库,数据处理效能问题慢慢浮现在工作的每个角落,编写的代码和设计的结构使效率慢上那几秒钟,也深怕客户来句"体现不良好"之云云,现在想想也惹人发笑:)

今日拿了些旧项目出来扫扫尘,左动动又动动,可全是无伤大雅之举,唯独那些数据分页不明确的迂腐代码特别碍眼,说砍就砍,参照百家之言,憋了一股劲就写好一个比较通用的数据分页的存储过程,生成百万行的数据测试了一下,性能还行,基本都是毫秒级的运算.

我向来的做分页的习惯都是用索引过的主键进行分页,但新问题突然就来了,如果主键是GUID,是个uniqueidentifier怎么办呢?想了很多方法,最后只能锁定在SQL 2005 的新函ROW_NUMBER()上,用上它不就没有那些限制了么?说改就改,代码完工以测试,效能上还没有一些大大吹得那么鸡肋,基本还是毫秒级就完成我给它的任务.

正当我自鸣得意的时候,突然发觉原来自己还是个傻冒,试想谁会去用一个没有规律的值来进行分页,即是做到又有什么意义呢?看这次真是走火入魔,庸人自扰咯 T_T.不过也好,碰壁的过程就是求知的过程,至少今天也对SQL 2005的系统数据库和一些函数特性有进一步了解,不错不错:)

1.没有使用ROW_NUMBER()的存储过程

 

/**//***********************************************************************
* 文件名:            GetRecordsPagination.sql
* 功能:                创建存储过程GetRecordsPagination
                    [注:只能用于能用MAX,MIN等方法的字段进行分页!]

* 创建时间:            2007-10-17
* 创建人:            清风
* 最后修改时间:        2007-10-17
* 最后修改人:        清风
***********************************************************************/

IF EXISTS (
    SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE SPECIFIC_NAME = 'GetRecordsPagination')
DROP PROCEDURE GetRecordsPagination

GO

CREATE PROCEDURE GetRecordsPagination
(
    @tableName            varchar(256),       -- 进行分页表名
    @columnName            varchar(256),        -- 用作分页的字段名
    @pageSize            int = 10,           -- 每页记录数 (默认是10)
    @pageIndex            int = 1,            -- 页码 (默认第一页)
    @sortType            bit = 0,            -- 数据查找排序类型, 
                                            -- 0 升序,1 降序 (默认 0)
    @visibleColumns        varchar(2000) = '',    -- 要返回的字段,格式"A1,B1,C1" (若为空,则返回全部)
    @sqlCondition        varchar(2000) = '', -- 附加查询条件 (不包含'where'字串)     
    @orderByColumn        varchar(256) = '',    -- 记录最后排序OrderBy的字段 (默认等于@columnName)
    @orderBySortType    bit = 0                -- 记录最后排序类型, 
                                            -- 0 升序,1 降序 (默认 0)
)
AS

declare @strSQL            varchar(8000)        -- 最终合成的SQL语句
declare @strTemp        varchar(256)        -- 临时字符串
declare @strOrderBy        varchar(1000)       -- 查找排序类型子句

-- 设置数据查找排序类型子句
if @sortType != 0
begin
    set @strTemp = '<(select min'
    set @strOrderBy = ' order by [' + @columnName + '] desc'
end
else
begin
    set @strTemp = '>(select max'
    set @strOrderBy = ' order by [' + @columnName +'] asc'
end

-- 设置要返回字段
if @visibleColumns = ''
    set  @visibleColumns = ' * '

-- 设置查找语句
set @strSQL = 'select top ' + str(@pageSize) + ' ' + @visibleColumns + '  from ['
    + @tableName + '] where [' + @columnName + ']' + @strTemp + '(['
    + @columnName + ']) from (select top ' + str((@pageIndex-1)*@pageSize) + ' ['
    + @columnName + '] from [' + @tableName + ']' + @strOrderBy + ') as TempTable)'
    + @strOrderBy

-- 添加附加查询条件
if @sqlCondition != ''
    set @strSQL = 'select top ' + str(@pageSize) + ' ' + @visibleColumns + '  from ['
        + @tableName + '] where [' + @columnName + ']' + @strTemp + '(['
        + @columnName + ']) from (select top ' + str((@pageIndex-1)*@pageSize) + ' ['
        + @columnName + '] from [' + @tableName + '] where ' + @sqlCondition + ' '
        + @strOrderBy + ') as TempTable) and ' + @sqlCondition + ' ' + @strOrderBy

-- 优化第一页查询
if @pageIndex = 1
begin
    set @strTemp = ''
    if @sqlCondition != ''
        set @strTemp = ' where (' + @sqlCondition + ')'

    set @strSQL = 'select top ' + str(@pageSize) + ' ' + @visibleColumns + '  from ['
        + @tableName + ']' + @strTemp + ' ' + @strOrderBy
end

-- 设置自定义输出OrderBy
if @orderByColumn != ''
begin
    set @strTemp = @strSQL
    if @orderBySortType = 0 
        set @strSQL =  ' select * from ( ' + @strTemp + ' ) as TempTable2 order by ['
            + @orderByColumn + '] asc' 
    else
        set @strSQL =  ' select * from ( ' + @strTemp + ' ) as TempTable2 order by ['
            + @orderByColumn + '] desc'
end  

--测试生成的SQL语句
--PRINT @strSQL

exec (@strSQL)

 

2.使用ROW_NUMBER()的存储过程

 

/**//***********************************************************************
* 文件名:            GetRecordsPaginationWithRowNumber.sql
* 功能:                创建存储过程GetRecordsPaginationWithRowNumber
                    [注:运用SQL 2005新增函数ROW_NUMBER(),有局限性!]

* 创建时间:            2007-10-17
* 创建人:            清风
* 最后修改时间:        2007-10-17
* 最后修改人:        清风
***********************************************************************/

IF EXISTS (
    SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE SPECIFIC_NAME = 'GetRecordsPaginationWithRowNumber')
DROP PROCEDURE GetRecordsPaginationWithRowNumber

GO

CREATE PROCEDURE GetRecordsPaginationWithRowNumber
(
    @tableName            varchar(256),       -- 进行分页表名
    @columnName            varchar(256),        -- 用作分页的字段名
    @pageSize            int = 10,           -- 每页记录数 (默认是10)
    @pageIndex            int = 1,            -- 页码 (默认第一页)
    @sortType            bit = 0,            -- 数据查找排序类型, 
                                            -- 0 升序,1 降序 (默认 0)
    @visibleColumns        varchar(2000) = '',    -- 要返回的字段,格式"A1,B1,C1" (若为空,则返回全部)
    @sqlCondition        varchar(2000) = '', -- 附加查询条件 (不包含'where'字串)     
    @orderByColumn        varchar(256) = '',    -- 记录最后排序OrderBy的字段 (默认等于@columnName)
    @orderBySortType    bit = 0                -- 记录最后排序类型, 
                                            -- 0 升序,1 降序 (默认 0)
)
AS

declare @strSQL            varchar(8000)        -- 最终合成的SQL语句
declare @strTemp        varchar(256)        -- 临时字符串
declare @strOrderBy        varchar(1000)       -- 查找排序类型子句

-- 设置数据查找排序类型子句
if @sortType != 0
begin
    set @strOrderBy = ' order by [' + @columnName + '] desc'
end
else
begin
    set @strOrderBy = ' order by [' + @columnName +'] asc'
end

-- 设置要返回字段
if @visibleColumns = ''
    set  @visibleColumns = ' * '

-- 设置查找语句
set @strSQL = 'select top ' + str(@pageSize) + ' ' + @visibleColumns  
                + ' from ( select * , ROW_NUMBER() Over ( ' 
                + @strOrderBy + ' ) as RowNum from [' + @tableName + '] ) as TempTable '
                + ' where RowNum between ' + str( (@pageIndex-1) * @pageSize ) 
                + ' and ' + str( @pageIndex * @pageSize )

-- 添加附加查询条件
if @sqlCondition != ''
    set @strSQL = 'select top ' + str(@pageSize) + ' ' + @visibleColumns 
                + ' from ( select * , ROW_NUMBER() Over ( ' 
                + @strOrderBy + ' ) as RowNum from [' + @tableName + '] where '
                + @sqlCondition + ' ) as TempTable '
                + ' where RowNum between ' + str( (@pageIndex-1) * @pageSize ) 
                + ' and ' + str( @pageIndex * @pageSize )

-- 优化第一页查询
if @pageIndex = 1
begin
    set @strTemp = ''
    if @sqlCondition != ''
        set @strTemp = ' where (' + @sqlCondition + ')'

    set @strSQL = 'select top ' + str(@pageSize) + ' ' + @visibleColumns + '  from ['
        + @tableName + ']' + @strTemp + ' ' + @strOrderBy
end

-- 设置自定义输出OrderBy
if @orderByColumn != ''
begin
    set @strTemp = @strSQL
    if @orderBySortType = 0 
        set @strSQL =  ' select * from ( ' + @strTemp + ' ) as TempTable2 order by ['
            + @orderByColumn + '] asc' 
    else
        set @strSQL =  ' select * from ( ' + @strTemp + ' ) as TempTable2 order by ['
            + @orderByColumn + '] desc'
end  

--测试生成的SQL语句
PRINT @strSQL

exec (@strSQL)

 

3.测试代码

 

/**//***********************************************************************
* 文件名:            PageTest.sql
* 功能:                测试分页存储过程

* 创建时间:            2007-10-17
* 创建人:            清风
* 最后修改时间:        2007-10-17
* 最后修改人:        清风
***********************************************************************/

-- 创建测试数据
--CREATE TABLE PagerTest
--(
--    pGUID    uniqueidentifier primary key,
--    pNum    int    NULL
--);
--
--DECLARE @i int
--set @i = 1
-- 这里只使用了一百万行数据进行测试
--WHILE @i < 1000000
--BEGIN
--    INSERT INTO PagerTest
--    VALUES(NEWID(),@i)
--    set @i = @i + 1
--END
--
--CREATE INDEX IdxGUID ON PagerTest(pGUID);
--CREATE INDEX IdxNum ON PagerTest(pNum);

-- 测试数据
--SELECT *
--FROM PagerTest
--ORDER BY pNUM ASC
--
-- 清空数据
--DROP TABLE PagerTest


-- 测试GetRecordsPagination
exec GetRecordsPagination PagerTest,pNum,10,10000,0,'','',pNum,1; 
exec GetRecordsPagination PagerTest,pNum,10,10

-- 测试GetRecordsPaginationWithRowNumber
exec GetRecordsPaginationWithRowNumber PagerTest,pNum,10,10
exec GetRecordsPaginationWithRowNumber PagerTest,pNum,10,10,1,'pNum','pNum > 999903',pNum,0; 
exec GetRecordsPaginationWithRowNumber PagerTest,pGUID,10,10,1,'','',pNum,0;