分页存储过程

1. /*通用存储过程
2. 支持多表查询，需要有索引，和int的ID字段
3. 2008-1-10
4. 2009-7-21    支持非主键的单字段排序
5. 2009-11-20  去掉out total
6. by emilchan
7. */
8. create                    PROCEDURE [dbo].[spCRM_GetPageListByNoTotal]
9. (
10.     @strSelect        varchar(2000),            -- 要返回的列  strSelect=
11.     @strFrom        varchar(2000),            -- 主表名 strFrom=
12.     @strWhere        varchar(2000)='1=1',      -- 查询条件
13.     @strPk            varchar(2000)='a.Id',      -- 主键字段名
14.     @strOrder        varchar(100),            -- 排序字段
15.     @strDirection    varchar(5)='desc',        -- 排序方向
16.     @PageSize        int = 10,              -- 单页大小
17.     @PageIndex        int = 1                -- 第几页 
18. )
19. AS
20. declare @sql nvarchar(4000)
21. declare @sort                varchar(10)        --desc or asc
22. declare @operator            varchar(2)        --方向符号
23. IF LOWER(@strDirection)='desc'            --如果是逆序
24.     BEGIN
25.         set @sort=' desc '
26.         SET @operator = '<='
27.     END
28. ELSE                                        --否则正序
29.     BEGIN
30.         set @sort=' asc '
31.         SET @operator = '>='
32.     END
34. declare @strOrderSql            varchar(6000)    --没有排序的包含主键、order字段的列表
35. declare @strSelectSQL                  varchar(6000)    --返回列表
36. declare @strGetRecordSql        varchar(6000)    --最终输出表
37. declare @tmpStrPk                varchar(2000)    --把形如a.Id的主键还原为Id
40. if(@strPk<>@strOrder)
41.     set @strOrderSql = 'select '+@strPk+','+@strOrder+' from '+ @strFrom+' where '+@strWhere
42. else
43.     set @strOrderSql = 'select '+@strPk+' from '+ @strFrom+' where '+@strWhere
45. set @strSelectSQL = 'select '+@strSelect+' from '+ @strFrom+' where '+@strWhere
48. declare @tmpStrOrder        varchar(50)
49. --修改pkstr以适合构造sql,去掉a.Id的a.
50. select @tmpStrPk=@strPk
51. select @tmpStrOrder=@strOrder
52. if(charindex('.',@tmpStrPk)>0)
53.     select @tmpStrPk=substring(@tmpStrPk,3, len(@tmpStrPk)-2)
54. if(charindex('.',@tmpStrOrder)>0)
55.     select @tmpStrOrder=substring(@tmpStrOrder,3, len(@tmpStrOrder)-2)
58. /*默认当前页*/
59. /*直接显示第一页*/
60. IF @PageIndex <= 1
61. begin
62.     SET @PageIndex = 1
63.     if @PageIndex=1
64.     begin
65.         set  @strSelectSQL=stuff(@strSelectSQL,7,0,' top '+cast(@PageSize as varchar)+' ')+ ' order by '+@strOrder+@sort
66.         exec(@strSelectSQL)
67.     end
68. end
69. else
70. begin
72.     --主键是int，且排序为主键的情况
73.     if(@strPk=@strOrder)
74.     begin
75.         --设置分页参数
76.         DECLARE @strPageSize        varchar(50)        --页大小str
77.         DECLARE @strStartRow        varchar(50)        --开始的行的计数值       
78.  
79.         SET @strPageSize = CAST(@PageSize AS varchar(50))
80.         SET @strStartRow = CAST(((@PageIndex-1)*@PageSize+1) AS varchar(50))
81.        
82.         declare @topPostion int        -- select后插入top 语句的位置
83.         set @topPostion= len('select')+1
84.         DECLARE @SortColumn varchar(50) --返回边界值，第一条记录的pkid
85.         --返回@SortColumn值，该值为条件搜索结果的第一条返回记录，通过该记录，由@operator来决定排序，该值就是当前的的上边界值
86.         set @sql='
87.         SET ROWCOUNT ' + @strStartRow + '
88.         Select @SortColumn=m. '+@tmpStrPk+' from (' + @strOrderSql+ ') m order by m.'+@tmpStrOrder+@sort
89.         --返回一个pkstr值
91.         exec sp_executesql @sql, N'@SortColumn varchar(50) output',@SortColumn output
92.        
93.  
94.         --符合条件的，还没有按页大小取记录的记录集
95.         set @strGetRecordSql=@strSelectSQL+' and '+@strPk+ @operator +cast(@SortColumn as varchar)+'order by '+@strOrder+@sort
96.  
97.         --获取top n行
98.         set @strGetRecordSql=stuff(@strGetRecordSql,@topPostion,0, ' top '+cast(@PageSize as varchar)+' ') 
99.         exec(@strGetRecordSql)
101.     end
102. else
103.     begin
104.     --非主键排序情况
105.         declare @maxPage int
106.         declare @lastPageSize int
107.         declare @tmpOrderIn    varchar(100)        --内层order带a.xx
108.         declare @tmpOrderOut    varchar(100)    --外层反向order
109.         declare @tmpOrder    varchar(100)        --最外层order没有a.xx
110.         declare @total    int
112.         if(@strDirection='desc')
113.         begin
114.             set @tmpOrderIn= @strOrder+' desc,'+@strPk+' desc'
115.             set @tmpOrderOut= @tmpStrOrder+' asc,'+@tmpStrPk+' asc'
116.             set @tmpOrder= @tmpStrOrder+' desc,'+@tmpStrPk+' desc'
117.         end
118.         else
119.         begin
120.             set @tmpOrderIn= @strOrder+' asc,'+@strPk+' asc'
121.             set @tmpOrderOut= @tmpStrOrder+' desc,'+@tmpStrPk+' desc'
122.             set @tmpOrder= @tmpStrOrder+' asc,'+@tmpStrPk+' asc'
123.         end
125.         set @lastPageSize=@pageSize
126.         set @maxPage=@total/@pageSize
128.         set @sql = 'select @total=count('+@strPk+') from '+@strFrom+' where '+ @strWhere
129.         exec sp_executesql @sql, N'@total int output',@total output
131.         if(@total%@pageSize)>0
132.         begin
133.             set @maxPage=@maxPage+1
134.             set @lastPageSize=@pageSize-((@pageSize*@pageIndex)-@total)
135.         end   
137.         --最后一页不是满页的情况
138.         if (@total<@pageSize*@pageIndex and @pageIndex=@maxPage)
139.             begin
140.             set @strGetRecordSql='select * from (  select TOP '+cast(@lastPageSize as varchar)+' * FROM
141.                 ( SELECT TOP '+cast(@total as varchar)+ ' '+@strSelect+' from '+@strFrom+' where '+@strWhere+' ORDER BY '+@tmpOrderIn+' ) as m  ORDER BY '+@tmpOrderOut+') as b  ORDER BY '+@tmpOrder
142.             end
143.         else
144.             begin
145.             set @strGetRecordSql='select * from (  select TOP '+cast(@pageSize as varchar)+' * FROM
146.                 ( 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
147.             end
148.         exec(@strGetRecordSql)
149.     end
150. end
153. GO

复制代码

一般的，我们需要一个total来显示在页面，如果直接在sp里面执行，效率在小表里面看不出来，如果在百W级别、千W级别里面，性能差距是相当明显，下面这个SP是自带total返回的，

1. /*通用存储过程
2. 支持多表查询，需要有索引，和int的ID字段
3. 2008-1-10
4. 2009-7-21    支持非主键的单字段排序
5. by emilchan
6. */
7. CREATE                    PROCEDURE [dbo].[spCRM_GetPageListBy]
8. (
9.     @strSelect        varchar(2000),            -- 要返回的列  strSelect=
10.     @strFrom        varchar(2000),            -- 主表名 strFrom=
11.     @strWhere        varchar(2000)='1=1',      -- 查询条件
12.     @strPk            varchar(2000)='a.Id',      -- 主键字段名
13.     @strOrder        varchar(100),            -- 排序字段
14.     @strDirection    varchar(5)='desc',        -- 排序方向
15.     @PageSize        int = 10,              -- 单页大小
16.     @PageIndex        int = 1,                -- 第几页 
17.     @total            int  output        --总记录数
18. )
19. AS
20. --return @total
21. declare @sql nvarchar(4000)
22. set @sql = 'select @total=count('+@strPk+') from '+@strFrom+' where '+ @strWhere
23. exec sp_executesql @sql, N'@total int output',@total output
27. declare @sort                varchar(10)        --desc or asc
28. declare @operator            varchar(2)        --方向符号
29. IF LOWER(@strDirection)='desc'            --如果是逆序
30.     BEGIN
31.         set @sort=' desc '
32.         SET @operator = '<='
33.     END
34. ELSE                                        --否则正序
35.     BEGIN
36.         set @sort=' asc '
37.         SET @operator = '>='
38.     END
40. declare @strOrderSql            varchar(6000)    --没有排序的包含主键、order字段的列表
41. declare @strSelectSQL                  varchar(6000)    --返回列表
42. declare @strGetRecordSql        varchar(6000)    --最终输出表
43. declare @tmpStrPk                varchar(2000)    --把形如a.Id的主键还原为Id
46. if(@strPk<>@strOrder)
47.     set @strOrderSql = 'select '+@strPk+','+@strOrder+' from '+ @strFrom+' where '+@strWhere
48. else
49.     set @strOrderSql = 'select '+@strPk+' from '+ @strFrom+' where '+@strWhere
51. set @strSelectSQL = 'select '+@strSelect+' from '+ @strFrom+' where '+@strWhere
54. declare @tmpStrOrder        varchar(50)
55. --修改pkstr以适合构造sql,去掉a.Id的a.
56. select @tmpStrPk=@strPk
57. select @tmpStrOrder=@strOrder
58. if(charindex('.',@tmpStrPk)>0)
59.     select @tmpStrPk=substring(@tmpStrPk,3, len(@tmpStrPk)-2)
60. if(charindex('.',@tmpStrOrder)>0)
61.     select @tmpStrOrder=substring(@tmpStrOrder,3, len(@tmpStrOrder)-2)
64. /*默认当前页*/
65. /*直接显示第一页*/
66. IF @PageIndex <= 1
67. begin
68.     SET @PageIndex = 1
69.     if @PageIndex=1
70.     begin
71.         set  @strSelectSQL=stuff(@strSelectSQL,7,0,' top '+cast(@PageSize as varchar)+' ')+ ' order by '+@strOrder+@sort
72.         exec(@strSelectSQL)
73.     end
74. end
75. else
76. begin
78.     --主键是int，且排序为主键的情况
79.     if(@strPk=@strOrder)
80.     begin
81.         --设置分页参数
82.         DECLARE @strPageSize        varchar(50)        --页大小str
83.         DECLARE @strStartRow        varchar(50)        --开始的行的计数值       
84.  
85.         SET @strPageSize = CAST(@PageSize AS varchar(50))
86.         SET @strStartRow = CAST(((@PageIndex-1)*@PageSize+1) AS varchar(50))
87.        
88.         declare @topPostion int        -- select后插入top 语句的位置
89.         set @topPostion= len('select')+1
90.         DECLARE @SortColumn varchar(50) --返回边界值，第一条记录的pkid
91.         --返回@SortColumn值，该值为条件搜索结果的第一条返回记录，通过该记录，由@operator来决定排序，该值就是当前的的上边界值
92.         set @sql='
93.         SET ROWCOUNT ' + @strStartRow + '
94.         Select @SortColumn=m. '+@tmpStrPk+' from (' + @strOrderSql+ ') m order by m.'+@tmpStrOrder+@sort
95.         --返回一个pkstr值
97.         exec sp_executesql @sql, N'@SortColumn varchar(50) output',@SortColumn output
98.        
99.  
100.         --符合条件的，还没有按页大小取记录的记录集
101.         set @strGetRecordSql=@strSelectSQL+' and '+@strPk+ @operator +cast(@SortColumn as varchar)+'order by '+@strOrder+@sort
102.  
103.         --获取top n行
104.         set @strGetRecordSql=stuff(@strGetRecordSql,@topPostion,0, ' top '+cast(@PageSize as varchar)+' ') 
105.         exec(@strGetRecordSql)
107.     end
108. else
109.     begin
110.     --非主键排序情况
111.         declare @maxPage int
112.         declare @lastPageSize int
113.         declare @tmpOrderIn    varchar(100)        --内层order带a.xx
114.         declare @tmpOrderOut    varchar(100)    --外层反向order
115.         declare @tmpOrder    varchar(100)        --最外层order没有a.xx
117.         if(@strDirection='desc')
118.         begin
119.             set @tmpOrderIn= @strOrder+' desc,'+@strPk+' desc'
120.             set @tmpOrderOut= @tmpStrOrder+' asc,'+@tmpStrPk+' asc'
121.             set @tmpOrder= @tmpStrOrder+' desc,'+@tmpStrPk+' desc'
122.         end
123.         else
124.         begin
125.             set @tmpOrderIn= @strOrder+' asc,'+@strPk+' asc'
126.             set @tmpOrderOut= @tmpStrOrder+' desc,'+@tmpStrPk+' desc'
127.             set @tmpOrder= @tmpStrOrder+' asc,'+@tmpStrPk+' asc'
128.         end
130.         set @lastPageSize=@pageSize
131.      
132.         set @maxPage=@total/@pageSize
133.         if(@total%@pageSize)>0
134.         begin
135.             set @maxPage=@maxPage+1
136.             set @lastPageSize=@pageSize-((@pageSize*@pageIndex)-@total)
137.         end   
139.         --最后一页不是满页的情况
140.         if (@total<@pageSize*@pageIndex and @pageIndex=@maxPage)
141.             begin
142.             set @strGetRecordSql='select * from (  select TOP '+cast(@lastPageSize as varchar)+' * FROM
143.                 ( SELECT TOP '+cast(@total as varchar)+ ' '+@strSelect+' from '+@strFrom+' where '+@strWhere+' ORDER BY '+@tmpOrderIn+' ) as m  ORDER BY '+@tmpOrderOut+') as b  ORDER BY '+@tmpOrder
144.             end
145.         else
146.             begin
147.             set @strGetRecordSql='select * from (  select TOP '+cast(@pageSize as varchar)+' * FROM
148.                 ( 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
149.             end
150.         exec(@strGetRecordSql)
151.     end
152. end
154. GO

复制代码

如果你的分页控件需要后台绑定total，则用第二个SP，但这里，我真的向你推荐使用第一种不带TOTAL返回的，最佳做法是在页面前台，使用一个ajax异步查询total,然后绑定到ajax的分页上。这样性能将是最佳的。

在1900w级的测试中，ajax分页和后台绑定total的性能测试中， 其首次加载页面的性能体验差距基本上是10倍计。

因此，分页的快速开发最佳做法是通用sp+ajax分页，至于数据绑定，泛型还是dataset,我的建议是能用泛型的时候还是泛型，不是因为性能好，而是因为从后翻页泛型的效率和首页加载的性能基本一致。而dataset效率慢了4.5倍左右。数据可以绑定到gridview,也可以仍然使用ajax传递，看你项目需要了，这个不是必须的。