SQLServer分页功能性能优化

最新推荐文章于 2024-08-21 11:29:11 发布

SarahCla

最新推荐文章于 2024-08-21 11:29:11 发布

阅读量2.5k

点赞数 1

分类专栏：数据库文章标签： sqlserver 数据库并行处理

本文链接：https://blog.csdn.net/sarahcla/article/details/11726821

版权

数据库专栏收录该内容

34 篇文章 0 订阅

订阅专栏

1. 基础数据

RtlBill行数: 54754
RtlDetail行数：4723291

如果不分页，结果集行数：198

2. 常用的分页方式：用 With + Row_Number() 来取分页数据

    dbcc dropcleanbuffers;
   declare@begin int = 10;
   declare@endint= 20;
   set statistics io on;
    set statistics time on;
    ;witha as
    (
        SELECTBillDate,SUM([QTY]) PosInQty, row_number() over(order by billdate) as id
       FROMRtlDetail Ainner join RtlBill B on (A.billno = B.billno)
       GROUP BY BillDate
    )
   select* from a whereid>@begin andid<=@end;
   set statistics io off;
    set statistics time off;

(10 行受影响)
表 'RtlBill'。扫描计数 17，逻辑读取 496 次，物理读取 1 次，预读 165 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'RtlDetail'。扫描计数 17，逻辑读取 49888 次，物理读取 3 次，预读 49284 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:
CPU 时间 = 4767 毫秒，占用时间 = 5880 毫秒。

从IO及CPU时间可以看出，这个分页SQL消耗资源多，耗时长，下面通过执行计划来分析一下原因，寻找优化方法；

2. 执行计划分析

（1）从执行计划中，可以看到大量使用并行处理；

（2）红色圈出的“哈希匹配”部分，详情，如下图，其中可以看到，实际行数为198行，也就是未分页的完整结果的行数，为什么SQLServer不能在取到30行后就停止执行，直接返回呢？

（3）接着看红色圈出的“并行度”部分，详情如下图。SQLServer在进行多线程处理的时候，使用的任务分配方法，一般为Hash，或RoundRobin方式。Hash方式即对数据做Hash计算，来确定究竟传给哪个线程处理，RoundRobin为轮询方式。每个线程之间没有消息通讯。这样导致，在所有线程完成全部处理之前，系统无法获知究竟是否取到了所要获取的分页中的所有数据。那么，如果我们使用单线程的方式，并且在With语句内部就用TOP的方式告诉SQLServer，我只要获取@end条记录就可以了，是否就可以让SQLServer做出正确的相应呢？

3. 依据前面的分析，改进分页数据获取语句

注意：这里需要用SQL拼接的方式直接把行数拼接进语句中，否则，生成执行计划时，SQL无法预估结果集。

呵呵，关于参数的问题，单独一篇来分析。

   dbcc dropcleanbuffers;
  set statistics io on;
    set statistics time on;
    ;witha as
    (
        SELECT Top 20 BillDate, SUM([QTY]) PosInQty,row_number() over(order bybilldate) as id
       FROMRtlDetail Ainner join RtlBill B on (A.billno = B.billno)
       GROUP BY BillDate
    )
   select* from a whereid>10 option(maxdop 1);
   set statistics io off;
    set statistics time off;