MS SQL 2005 分页分析及优化

MS SQL 分页方式说明:
目前我所知的有以下几种方式

• 临时表
• 表变量
• 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 时间