通用分页存储过程

 
 
 

 

  1Community_Common_Count_USP 
  2/* 
  3  作者: SLIGHTBOY 
  4  建立日期: 2005-8-17 
  5  修改日期: 
  6  存储作用: 通用记录统计 
  7  作用数据库: 自定义 
  8  输入变量: 
  9      @KeyColumn 主键字段名 
 10      @TableName 表格名称 
 11      @SearchQuery 查询条件 默认 '' 
 12  输出变量: 
 13  中间变量: 
 14*/ 
 15CREATE PROCEDURE dbo.Community_Common_Count_USP 
 16( 
 17  @KeyColumn varchar(20), 
 18  @TableName varchar(20), 
 19  @SearchQuery varchar(400) = '' 
 20) 
 21AS 
 22  SET NOCOUNT ON 
 23 DECLARE @Timer datetime 
 24 SET @Timer = getdate() 
 25
 26 IF ( @SearchQuery IS Not NULL ) AND ( @SearchQuery <> '' ) 
 27  exec('SELECT Count('+ @KeyColumn +') FROM ['+ @TableName +'] Where '+ @SearchQuery) 
 28 Else 
 29  exec('SELECT Count('+ @KeyColumn +') FROM ['+ @TableName +']') 
 30
 31 print(DATEDIFF(millisecond, @Timer, getdate())) 
 32GO 
 33
 34
 35Community_Common_Page_USP 
 36/* 
 37  作者:  SLIGHTBOY 
 38  建立日期: 2005-8-12 
 39  修改日期: 
 40  存储作用: 通用分页存储过程 
 41  作用数据库: 自定义 
 42  版本:  1.0 
 43  输入变量: 
 44      @AbsolutePage 请求页 默认 1 
 45      @PageSize  每页记录数 默认 20 
 46      @RecordCount 总记录 
 47      @KeyColumn 主键字段名 
 48      @SelectColumn 查询字段 
 49      @TableName 表格名称 
 50      @Sort  排序方式 DESC 
 51      @SearchQuery 查询条件 默认 '' 
 52      @ExpandQuery 扩展内容(FOR XML EXPLICIT) 默认 '' 
 53  输出变量: 
 54  过程变量 
 55      @SqlQuery 查询语句 
 56      @AbsolutePosition 当前位置 
 57*/ 
 58CREATE PROCEDURE dbo.Community_Common_Page_USP 
 59( 
 60  @AbsolutePage int = 1, 
 61  @PageSize int = 20, 
 62  @RecordCount int, 
 63  @KeyColumn varchar(20), 
 64  @SelectColumn varchar(1000), 
 65  @TableName varchar(20), 
 66  @Sort varchar(4) = '', 
 67  @SearchQuery varchar(400) = '', 
 68  @ExpandQuery varchar(50) = '' 
 69) 
 70AS 
 71  SET NOCOUNT ON 
 72 DECLARE @Timer datetime 
 73 SET @Timer = getdate() 
 74
 75 DECLARE @SqlQuery varchar(3000) 
 76 DECLARE @AbsolutePosition int 
 77 SET @AbsolutePosition = @AbsolutePage * @PageSize 
 78
 79 IF ( @AbsolutePosition = @PageSize ) 
 80  IF ( @Sort = 1 ) 
 81   SET @SqlQuery = ' 
 82   SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' 
 83   '+ @SelectColumn +' 
 84   FROM ['+ @TableName +'] 
 85   '+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +' 
 86   ORDER BY '+ @KeyColumn +' ASC 
 87   '+ @ExpandQuery 
 88  Else 
 89   
 90   SET @SqlQuery = ' 
 91   SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' 
 92   '+ @SelectColumn +' 
 93   FROM ['+ @TableName +'] 
 94   '+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +' 
 95   ORDER BY '+ @KeyColumn +' DESC 
 96   '+ @ExpandQuery 
 97 -- 大于 首页 小于 中间页 
 98 Else IF ( @AbsolutePosition <= @RecordCount/2  ) 
 99  IF ( @Sort = 1 ) 
100   SET @SqlQuery = ' 
101   SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' '+ @SelectColumn +' 
102   FROM ['+ @TableName +'] 
103   WHERE '+ @KeyColumn +' > 
104   ( 
105    SELECT MAX('+ @KeyColumn +') 
106    FROM 
107    ( 
108     SELECT TOP '+ LTRIM( STR( ( @AbsolutePage - 1)*@PageSize) ) +' 
109     '+ @KeyColumn +' 
110     FROM ['+ @TableName +'] 
111     '+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +' 
112     ORDER BY '+ @KeyColumn +' ASC 
113    ) As Child 
114   ) 
115   '+ dbo.iif(@SearchQuery, ' AND '+ @SearchQuery, '' ) +' 
116   ORDER BY '+ @KeyColumn +' ASC 
117   '+ @ExpandQuery 
118  Else 
119   SET @SqlQuery = ' 
120   SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' '+ @SelectColumn +' 
121   FROM ['+ @TableName +'] 
122   WHERE '+ @KeyColumn +' <= 
123   ( 
124    SELECT Min('+ @KeyColumn +') 
125    FROM 
126    ( 
127     SELECT TOP '+ LTRIM( STR( ( @AbsolutePage - 1)*@PageSize + 1 ) ) +' 
128     '+ @KeyColumn +' 
129     FROM ['+ @TableName +'] 
130     '+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +' 
131     ORDER BY '+ @KeyColumn +' DESC 
132    ) As Child 
133   ) 
134   '+ dbo.iif(@SearchQuery, ' AND '+ @SearchQuery, '' ) +' 
135   ORDER BY '+ @KeyColumn +' DESC 
136   '+ @ExpandQuery 
137 Else IF (@AbsolutePosition > @RecordCount/2 ) 
138  IF ( @Sort = 1 ) 
139   SET @SqlQuery = ' 
140   SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' '+ @SelectColumn +' 
141   FROM ['+ @TableName +'] 
142   WHERE '+ @KeyColumn +' > 
143   ( 
144    SELECT Min('+ @KeyColumn +') 
145    FROM 
146    ( 
147     SELECT TOP '+ LTRIM( STR( @RecordCount - ((@AbsolutePage - 1) * @PageSize ) + 1) ) +' 
148     '+ @KeyColumn +' 
149     FROM ['+ @TableName +'] 
150     '+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +' 
151     ORDER BY '+ @KeyColumn +' DESC 
152    ) As Child 
153   ) 
154   '+ dbo.iif(@SearchQuery, ' AND '+ @SearchQuery, '' ) +' 
155   ORDER BY '+ @KeyColumn +' ASC 
156   '+ @ExpandQuery 
157  Else 
158   SET @SqlQuery = ' 
159   SELECT TOP '+ LTRIM( STR( @PageSize ) ) +' '+ @SelectColumn +' 
160   FROM ['+ @TableName +'] 
161   WHERE '+ @KeyColumn +' < 
162   ( 
163    SELECT MAX('+ @KeyColumn +') 
164    FROM 
165    ( 
166     SELECT TOP '+ LTRIM( STR( @RecordCount - ((@AbsolutePage - 1) * @PageSize ) + 1) ) +' 
167     '+ @KeyColumn +' 
168     FROM ['+ @TableName +'] 
169     '+ dbo.iif(@SearchQuery, 'Where '+ @SearchQuery, '' ) +' 
170     ORDER BY '+ @KeyColumn +' ASC 
171    ) As Child 
172   ) 
173   '+ dbo.iif(@SearchQuery, ' AND '+ @SearchQuery, '' ) +' 
174   ORDER BY '+ @KeyColumn +' DESC 
175   '+ @ExpandQuery 
176
177exec(@SqlQuery) 
178print(@SqlQuery) 
179print(DATEDIFF(millisecond, @Timer, getdate())) 
180
181GO 



第一个存储过程 计算总记录
第二个存储过程 为选择记录部分
根据传入页码判断位置 实现三个逻辑
请求页 = 第一页
请求页 <= 总页/2
请求页 > 总页/2
并且针对排序方式做了优化

总记录统计部分消耗资源比较大 尽可能做冗余

具体参数作用 存储过程说明里已经做了说明 这里就不重复了

 

 

 

呵呵，一般
1、统计的：如果多表关联统计呢？
2、如果不按照主键排序呢？

结论：对于您的项目可能比较适合，通用？　做不到！

 

 

太复杂了，这里有一个简单的，还可以返回总的记录数

Create Procedure ComListPaged
@Sql nvarchar(800), --构造好的sql语句
@PKey nvarchar(50), --主键
@PageSize int, --每页大小
@Page int, --页码
@Order nvarchar(50), --排序表达式，比如"pub_date desc"
@TotalCount int output --总记录数
As
Declare @Sql1 nvarchar(1000)
Declare @Sql2 nvarchar(1000)
Select @Sql1 = 'Select @Count = count(ta.' + @PKey + ') From (' + @Sql + ') ta '
Exec sp_executesql @Sql1, N'@Count int output', @TotalCount output

Select @Sql2 = 'Select Top ' + Cast(@PageSize As varchar) + ' * From (' + @Sql + ') ta '
Select @Sql2 = @Sql2 + 'Where ta.' + @PKey + ' Not In(Select Top ' + Cast((@Page - 1)* @PageSize As varchar) + ' ' + @PKey + ' From (' + @Sql + ') tb Order By ' + @Order + ') '
Select @Sql2 = @Sql2 + ' Order By ' + @Order
Exec(@Sql2)
Go
 
 
 
 
回子扬:
在大容量表里做联合查询 本来就极不划算 所以这种情况 我不考虑
即使要做 也宁愿做冗余

不按主键排序是可以的 希望你看清楚发话

另外我的通用只是 一般意义的话
不是 All in one.

回tubo:
简单是简单了
但
第一 排序没有优化
第二 记录状态选择没有优化
 
 
 
一般
给你个好一些的
CREATE PROCEDURE P_GetPagedOrders2005
@startIndex INT, // 第几页
@pageSize INT //一页显示几条数据

AS

with orderlist as(
select row_number() over(order by freight) as rownumber, orderid,customerid from orders)
select orderid,customerid from orderlist
where rownumber between @startindex and @startindex+@pagesize-1
go
 

 

 

MSSQL 2005 分页分析及优化
MSSQL 分页方式说明:
目前我所知的有以下几种方式

  • 临时表
  • 表变量
  • in, not in
  • SET ROWCOUNT
  • CTE
  • id >, id <
优缺点分析: 性能最低, 可操作性差
第一种方式和第二种方实际上是比较类似的.
优点: 排序方式比较随意
缺点:
第一种方式 有大量的 IO 开销.
第二种方式则会开销内存, 但当表数据量比较大的时候性能会直线下降.
所以这两种方式都不适合做大数据量的分页.

第三种方式: 性能次之, 可操作较差
优点: 排序方式比较随意
缺点: 资源开销比较大, 数据库会承担不小的运算压力, 所以也不适合做大表分页.

第四种方式: 性能平均, 可操作性尚可
优点: 排序相对比较随意, 各分页情况下速度平均, 属于不是最快也不是最慢.
缺点: 没有明显缺点.

第五种方式: 性能较好, 可操作性良好
优点: 排序相对比较随意, 代码简洁, 适用面广.
缺点: 尾页速度比较慢(需针对优化).

第六种方式: 性能最好, 可操作性比较差
优点: 速度快.
缺点: 尾页速度比较慢(需针对优化), 对排序键有要求.

PS: 以上内容居于以前测试结果说得.

测试用库 DB_PagingTest, 测试用表: Paing_New
主键: ID Desc
总记录 @RecordCount: 10000331
分页尺寸 @PageSize: 30
总页数 @PageCount: 333345
请求页 @AbsolutePage

分页情况分析:
  • @AbsolutePage == 1
  • @AbsolutePage < @PageCount/2
  • @AbsolutePage >= @PageCount/2
  • @AbsolutePage == @PageCount
情况 1:
请求页等于第一页, 这种情况是最简单的.
复制内容到剪贴板
代码:
Select TOP @PageSize * From [Paing_New] Order BY ID Desc
情况 2:
请求页小于总页数/2
复制内容到剪贴板
代码:

    WITH CTE AS
    (
      SELECT TOP @AbsolutePage * @PageSize
      *
      ROW_NUMBER() Over (Order By ID Desc) as _RowNumber
      FROM [Paing_New]
    )
    SELECT
      *
    FROM CTE
    WHERE _RowNumber > (@AbsolutePage - 1) * @PageSize);
情况 3:
请求页大于等于总页数/2
理论上 请求页等于总页数/2的时候应该也有优化方法.
复制内容到剪贴板
代码:

    WITH CTE AS
    (
      SELECT TOP @RecordCount - (@AbsolutePage - 1) * @PageSize
      *,
      ROW_NUMBER() Over (Order BY ID Asc) as _RowNumber
      FROM [Paing_New]  
    )
    SELECT
      *
    FROM CTE
    WHERE _RowNumber > (@RecordCount - @AbsolutePage * @PageSize) Order BY ID Desc;
情况 4:
请求页等于总页数
复制内容到剪贴板
代码:

    WITH CTE AS
    (
      SELECT TOP @RecordCount - (@AbsolutePage - 1) * @PageSize
      *,
      ROW_NUMBER() Over (Order BY ID Asc) as _RowNumber
      FROM [Paing_New]  
    )
    SELECT
      *
    FROM CTE Order BY ID Desc;
数据测试结果:
第 30 条, 即 1 页, CPU 时间 = 0 毫秒，占用时间 = 1 毫秒, 实际执行时间 = 0 毫秒;
第 1W 条, 即 334 页, CPU 时间 = 0 毫秒，占用时间 = 3 毫秒, 实际执行时间 = 0 毫秒;
第 10W 条, 即 3334 页, CPU 时间 = 31 毫秒，占用时间 = 26~28 毫秒, 实际执行时间 = 16~33 毫秒;
第 100W 条, 即 3334 页, CPU 时间 = 250~260 毫秒，占用时间 = 250~260 毫秒, 实际执行时间 = 250~260 毫秒;
第 5000130 条(中间页), 即 166671 页, CPU 时间 = 1200~1300 毫秒，占用时间 = 1200~1300 毫秒, 实际执行时间 = 1200~1300 毫秒;
第 5000160 条(中间页), 即 166672 页, CPU 时间 = 3400~3600 毫秒，占用时间 = 3400~3600 毫秒, 实际执行时间 = 3400~3600 毫秒;
第 9000331 条, 即 300012 页, CPU 时间 = 266~281 毫秒，占用时间 = 273~285 毫秒, 实际执行时间 = 266~296 毫秒;
第 9900331 条, 即 330012 页, CPU 时间 = 31~32 毫秒，占用时间 = 29~30 毫秒, 实际执行时间 = 30~33 毫秒;
第 9999331 条, 即 333312 页, CPU 时间 = 0 毫秒，占用时间 = 2~3 毫秒, 实际执行时间 = 0 毫秒;
第 10000331 条(尾页), 即 333345 页, CPU 时间 = 0 毫秒，占用时间 = 1 毫秒, 实际执行时间 = 0 毫秒;
PS: 关于时间的说明, CPU 时间和占用时间为 MSSQL 的统计结果, 实行时间是人为技术所得;

分页方案优点:
对分页多数情况进行了针对优化, 并且可以对非主键和顺序编号等情况进行分页.
开始和结尾速度都非常快, 因为选择的记录集相对较少.

分页方案缺点:
请求页在总页数中间的时候速度比较慢.


结论:
对于使用 ID 为主键索引的分页, 还是使用传统的 ID 大于或小于这种方式最好.
对于分页主键不明确的, 使用 CTE 的方式比较好.

posted on 2012-11-23 13:03  844916072  阅读(143)  评论(0)    收藏  举报

导航