项目中要使用分页的地方很多，园子里面也有很多各种分页方法的性能测试。但是如何既能满足快速开发的要求，又能保证良好的性能这是需要值得权衡的事情。经过几天的测试，我觉得比较好的方法是通用存储过程+ajax分页，为什么呢。且看以下分析。
使用通用存储过程，大大的节约了开发时间，一般情况下的分页，涉及的查询无非是单表多表查询，按条件排排序之类的，所以有个通用存储过程可以省很多力气。特殊的再使用专用的存储过程来解决。网上的这种通用SP比较多，我自己改了一个，这个SP经过长期使用，没有什么问题。里面优化了首页、尾页的算法，查询时经测试，前后翻页速度在1900W级别时也能基本保持一致。
存储过程如下
Code/*通用存储过程
支持多表查询，需要有索引，和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返回的，
Code/*通用存储过程
支持多表查询，需要有索引，和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传递，看你项目需要了，这个不是必须的。
需要说明的是，目前这个sp还不支持group by,多字段排序也不支持，如果你有这个需要，需要改一改了。
