SQLServer分页功能性能优化

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,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;

4. 实验结果对比

5. 不适用的情况

（1）使用 Dense_Rank（）等函数进行分页时，不能使用TOP方式（因为，结果行数都不同）；

（2）需要大量计算的SQL（这个需要实际分析，如果CPU耗时太多，单线程处理的优势就体现不出来了）。

6. 优化策略

? ? ? ? 尝试Top+SingleThread的方式。有效？OK

??????? 折中一下，单独使用Top方式

? ? ? ? 尝试，用count(*) Over()优化首页

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!