分页存储过程
- /*通用存储过程
- 支持多表查询,需要有索引,和int的ID字段
- 2008-1-10
- 2009-7-21 支持非主键的单字段排序
- 2009-11-20 去掉out total
- by emilchan
- */
- create PROCEDURE [dbo].[spCRM_GetPageListByNoTotal]
- (
- @strSelect varchar(2000), -- 要返回的列 strSelect=
- @strFrom varchar(2000), -- 主表名 strFrom=
- @strWhere varchar(2000)='1=1', -- 查询条件
- @strPk varchar(2000)='a.Id', -- 主键字段名
- @strOrder varchar(100), -- 排序字段
- @strDirection varchar(5)='desc', -- 排序方向
- @PageSize int = 10, -- 单页大小
- @PageIndex int = 1 -- 第几页
- )
- AS
- declare @sql nvarchar(4000)
- declare @sort varchar(10) --desc or asc
- declare @operator varchar(2) --方向符号
- IF LOWER(@strDirection)='desc' --如果是逆序
- BEGIN
- set @sort=' desc '
- SET @operator = '<='
- END
- ELSE --否则正序
- BEGIN
- set @sort=' asc '
- SET @operator = '>='
- END
- declare @strOrderSql varchar(6000) --没有排序的包含主键、order字段的列表
- declare @strSelectSQL varchar(6000) --返回列表
- declare @strGetRecordSql varchar(6000) --最终输出表
- declare @tmpStrPk varchar(2000) --把形如a.Id的主键还原为Id
- if(@strPk<>@strOrder)
- set @strOrderSql = 'select '+@strPk+','+@strOrder+' from '+ @strFrom+' where '+@strWhere
- else
- set @strOrderSql = 'select '+@strPk+' from '+ @strFrom+' where '+@strWhere
- set @strSelectSQL = 'select '+@strSelect+' from '+ @strFrom+' where '+@strWhere
- declare @tmpStrOrder varchar(50)
- --修改pkstr以适合构造sql,去掉a.Id的a.
- select @tmpStrPk=@strPk
- select @tmpStrOrder=@strOrder
- if(charindex('.',@tmpStrPk)>0)
- select @tmpStrPk=substring(@tmpStrPk,3, len(@tmpStrPk)-2)
- if(charindex('.',@tmpStrOrder)>0)
- select @tmpStrOrder=substring(@tmpStrOrder,3, len(@tmpStrOrder)-2)
- /*默认当前页*/
- /*直接显示第一页*/
- IF @PageIndex <= 1
- begin
- SET @PageIndex = 1
- if @PageIndex=1
- begin
- set @strSelectSQL=stuff(@strSelectSQL,7,0,' top '+cast(@PageSize as varchar)+' ')+ ' order by '+@strOrder+@sort
- exec(@strSelectSQL)
- end
- end
- else
- begin
- --主键是int,且排序为主键的情况
- if(@strPk=@strOrder)
- begin
- --设置分页参数
- DECLARE @strPageSize varchar(50) --页大小str
- DECLARE @strStartRow varchar(50) --开始的行的计数值
- SET @strPageSize = CAST(@PageSize AS varchar(50))
- SET @strStartRow = CAST(((@PageIndex-1)*@PageSize+1) AS varchar(50))
- declare @topPostion int -- select后插入top 语句的位置
- set @topPostion= len('select')+1
- DECLARE @SortColumn varchar(50) --返回边界值,第一条记录的pkid
- --返回@SortColumn值,该值为条件搜索结果的第一条返回记录,通过该记录,由@operator来决定排序,该值就是当前的的上边界值
- set @sql='
- SET ROWCOUNT ' + @strStartRow + '
- Select @SortColumn=m. '+@tmpStrPk+' from (' + @strOrderSql+ ') m order by m.'+@tmpStrOrder+@sort
- --返回一个pkstr值
- exec sp_executesql @sql, N'@SortColumn varchar(50) output',@SortColumn output
- --符合条件的,还没有按页大小取记录的记录集
- set @strGetRecordSql=@strSelectSQL+' and '+@strPk+ @operator +cast(@SortColumn as varchar)+'order by '+@strOrder+@sort
- --获取top n行
- set @strGetRecordSql=stuff(@strGetRecordSql,@topPostion,0, ' top '+cast(@PageSize as varchar)+' ')
- exec(@strGetRecordSql)
- end
- else
- begin
- --非主键排序情况
- declare @maxPage int
- declare @lastPageSize int
- declare @tmpOrderIn varchar(100) --内层order带a.xx
- declare @tmpOrderOut varchar(100) --外层反向order
- declare @tmpOrder varchar(100) --最外层order没有a.xx
- declare @total int
- if(@strDirection='desc')
- begin
- set @tmpOrderIn= @strOrder+' desc,'+@strPk+' desc'
- set @tmpOrderOut= @tmpStrOrder+' asc,'+@tmpStrPk+' asc'
- set @tmpOrder= @tmpStrOrder+' desc,'+@tmpStrPk+' desc'
- end
- else
- begin
- set @tmpOrderIn= @strOrder+' asc,'+@strPk+' asc'
- set @tmpOrderOut= @tmpStrOrder+' desc,'+@tmpStrPk+' desc'
- set @tmpOrder= @tmpStrOrder+' asc,'+@tmpStrPk+' asc'
- end
- set @lastPageSize=@pageSize
- set @maxPage=@total/@pageSize
- set @sql = 'select @total=count('+@strPk+') from '+@strFrom+' where '+ @strWhere
- exec sp_executesql @sql, N'@total int output',@total output
- if(@total%@pageSize)>0
- begin
- set @maxPage=@maxPage+1
- set @lastPageSize=@pageSize-((@pageSize*@pageIndex)-@total)
- end
- --最后一页不是满页的情况
- if (@total<@pageSize*@pageIndex and @pageIndex=@maxPage)
- begin
- set @strGetRecordSql='select * from ( select TOP '+cast(@lastPageSize as varchar)+' * FROM
- ( SELECT TOP '+cast(@total as varchar)+ ' '+@strSelect+' from '+@strFrom+' where '+@strWhere+' ORDER BY '+@tmpOrderIn+' ) as m ORDER BY '+@tmpOrderOut+') as b ORDER BY '+@tmpOrder
- end
- else
- begin
- set @strGetRecordSql='select * from ( select TOP '+cast(@pageSize as varchar)+' * FROM
- ( SELECT TOP '+cast(@pageSize*@pageIndex as varchar)+ ' '+@strSelect+' from '+@strFrom+' where '+@strWhere+' ORDER BY '+@tmpOrderIn+' ) as m ORDER BY '+@tmpOrderOut+' ) as b ORDER BY '+@tmpOrder
- end
- exec(@strGetRecordSql)
- end
- end
- GO
一般的,我们需要一个total来显示在页面,如果直接在sp里面执行,效率在小表里面看不出来,如果在百W级别、千W级别里面,性能差距是相当明显,下面这个SP是自带total返回的,
- /*通用存储过程
- 支持多表查询,需要有索引,和int的ID字段
- 2008-1-10
- 2009-7-21 支持非主键的单字段排序
- by emilchan
- */
- CREATE PROCEDURE [dbo].[spCRM_GetPageListBy]
- (
- @strSelect varchar(2000), -- 要返回的列 strSelect=
- @strFrom varchar(2000), -- 主表名 strFrom=
- @strWhere varchar(2000)='1=1', -- 查询条件
- @strPk varchar(2000)='a.Id', -- 主键字段名
- @strOrder varchar(100), -- 排序字段
- @strDirection varchar(5)='desc', -- 排序方向
- @PageSize int = 10, -- 单页大小
- @PageIndex int = 1, -- 第几页
- @total int output --总记录数
- )
- AS
- --return @total
- declare @sql nvarchar(4000)
- set @sql = 'select @total=count('+@strPk+') from '+@strFrom+' where '+ @strWhere
- exec sp_executesql @sql, N'@total int output',@total output
- declare @sort varchar(10) --desc or asc
- declare @operator varchar(2) --方向符号
- IF LOWER(@strDirection)='desc' --如果是逆序
- BEGIN
- set @sort=' desc '
- SET @operator = '<='
- END
- ELSE --否则正序
- BEGIN
- set @sort=' asc '
- SET @operator = '>='
- END
- declare @strOrderSql varchar(6000) --没有排序的包含主键、order字段的列表
- declare @strSelectSQL varchar(6000) --返回列表
- declare @strGetRecordSql varchar(6000) --最终输出表
- declare @tmpStrPk varchar(2000) --把形如a.Id的主键还原为Id
- if(@strPk<>@strOrder)
- set @strOrderSql = 'select '+@strPk+','+@strOrder+' from '+ @strFrom+' where '+@strWhere
- else
- set @strOrderSql = 'select '+@strPk+' from '+ @strFrom+' where '+@strWhere
- set @strSelectSQL = 'select '+@strSelect+' from '+ @strFrom+' where '+@strWhere
- declare @tmpStrOrder varchar(50)
- --修改pkstr以适合构造sql,去掉a.Id的a.
- select @tmpStrPk=@strPk
- select @tmpStrOrder=@strOrder
- if(charindex('.',@tmpStrPk)>0)
- select @tmpStrPk=substring(@tmpStrPk,3, len(@tmpStrPk)-2)
- if(charindex('.',@tmpStrOrder)>0)
- select @tmpStrOrder=substring(@tmpStrOrder,3, len(@tmpStrOrder)-2)
- /*默认当前页*/
- /*直接显示第一页*/
- IF @PageIndex <= 1
- begin
- SET @PageIndex = 1
- if @PageIndex=1
- begin
- set @strSelectSQL=stuff(@strSelectSQL,7,0,' top '+cast(@PageSize as varchar)+' ')+ ' order by '+@strOrder+@sort
- exec(@strSelectSQL)
- end
- end
- else
- begin
- --主键是int,且排序为主键的情况
- if(@strPk=@strOrder)
- begin
- --设置分页参数
- DECLARE @strPageSize varchar(50) --页大小str
- DECLARE @strStartRow varchar(50) --开始的行的计数值
- SET @strPageSize = CAST(@PageSize AS varchar(50))
- SET @strStartRow = CAST(((@PageIndex-1)*@PageSize+1) AS varchar(50))
- declare @topPostion int -- select后插入top 语句的位置
- set @topPostion= len('select')+1
- DECLARE @SortColumn varchar(50) --返回边界值,第一条记录的pkid
- --返回@SortColumn值,该值为条件搜索结果的第一条返回记录,通过该记录,由@operator来决定排序,该值就是当前的的上边界值
- set @sql='
- SET ROWCOUNT ' + @strStartRow + '
- Select @SortColumn=m. '+@tmpStrPk+' from (' + @strOrderSql+ ') m order by m.'+@tmpStrOrder+@sort
- --返回一个pkstr值
- exec sp_executesql @sql, N'@SortColumn varchar(50) output',@SortColumn output
- --符合条件的,还没有按页大小取记录的记录集
- set @strGetRecordSql=@strSelectSQL+' and '+@strPk+ @operator +cast(@SortColumn as varchar)+'order by '+@strOrder+@sort
- --获取top n行
- set @strGetRecordSql=stuff(@strGetRecordSql,@topPostion,0, ' top '+cast(@PageSize as varchar)+' ')
- exec(@strGetRecordSql)
- end
- else
- begin
- --非主键排序情况
- declare @maxPage int
- declare @lastPageSize int
- declare @tmpOrderIn varchar(100) --内层order带a.xx
- declare @tmpOrderOut varchar(100) --外层反向order
- declare @tmpOrder varchar(100) --最外层order没有a.xx
- if(@strDirection='desc')
- begin
- set @tmpOrderIn= @strOrder+' desc,'+@strPk+' desc'
- set @tmpOrderOut= @tmpStrOrder+' asc,'+@tmpStrPk+' asc'
- set @tmpOrder= @tmpStrOrder+' desc,'+@tmpStrPk+' desc'
- end
- else
- begin
- set @tmpOrderIn= @strOrder+' asc,'+@strPk+' asc'
- set @tmpOrderOut= @tmpStrOrder+' desc,'+@tmpStrPk+' desc'
- set @tmpOrder= @tmpStrOrder+' asc,'+@tmpStrPk+' asc'
- end
- set @lastPageSize=@pageSize
- set @maxPage=@total/@pageSize
- if(@total%@pageSize)>0
- begin
- set @maxPage=@maxPage+1
- set @lastPageSize=@pageSize-((@pageSize*@pageIndex)-@total)
- end
- --最后一页不是满页的情况
- if (@total<@pageSize*@pageIndex and @pageIndex=@maxPage)
- begin
- set @strGetRecordSql='select * from ( select TOP '+cast(@lastPageSize as varchar)+' * FROM
- ( SELECT TOP '+cast(@total as varchar)+ ' '+@strSelect+' from '+@strFrom+' where '+@strWhere+' ORDER BY '+@tmpOrderIn+' ) as m ORDER BY '+@tmpOrderOut+') as b ORDER BY '+@tmpOrder
- end
- else
- begin
- set @strGetRecordSql='select * from ( select TOP '+cast(@pageSize as varchar)+' * FROM
- ( SELECT TOP '+cast(@pageSize*@pageIndex as varchar)+ ' '+@strSelect+' from '+@strFrom+' where '+@strWhere+' ORDER BY '+@tmpOrderIn+' ) as m ORDER BY '+@tmpOrderOut+' ) as b ORDER BY '+@tmpOrder
- end
- exec(@strGetRecordSql)
- end
- end
- GO
如果你的分页控件需要后台绑定total,则用第二个SP,但这里,我真的向你推荐使用第一种不带TOTAL返回的,最佳做法是在页面前台,使用一个ajax异步查询total,然后绑定到ajax的分页上。这样性能将是最佳的。
在1900w级的测试中,ajax分页和后台绑定total的性能测试中, 其首次加载页面的性能体验差距基本上是10倍计。
因此,分页的快速开发最佳做法是通用sp+ajax分页,至于数据绑定,泛型还是dataset,我的建议是能用泛型的时候还是泛型,不是因为性能好,而是因为从后翻页泛型的效率和首页加载的性能基本一致。而dataset效率慢了4.5倍左右。数据可以绑定到gridview,也可以仍然使用ajax传递,看你项目需要了,这个不是必须的。