列表查询使用单据上的分录号排序导致性能问题的解决方案

问题描述

       星空列表查询，使用单据上的分录号排序，如果没有日期范围的过滤条件，可能导致语句无法执行出来结果。

l  问题SQL

l  执行计划

    执行计划中t_AR_receivableEntry采用聚集索引，然后使用fseq字段进行排序，占了整个计划的绝大部分成本。

    排序操作的详情

    然后再对sort的结果进行Table Spool，从IO统计中可以看到产生了Worktable。

    可以看到由于排序和Table Spool带来的Worktable的逻辑读达到了69429876次，一次读操作是8kb，那么总共带来的IO为(69429876*8/1024/1024)=529GB，即这个操作产生的中间表的数据量达到529GB，最后导致耗时376秒。

说明：（Worktable： worktable是一个内部的关系表，用于存储中间结果集。关系引擎在执行 order by、group by或者union操作时，会创建Worktable）

l  检查索引定义情况

    发现fseq字段非索引字段，对fseq字段增加索引后(idx_fseq)，执行计划如下

    可以看到，执行计划使用到了新增加的索引，但需要从t_AR_receivableEntry中循环获取表中的其他字段，其循环的次数非常大。

    idx_fseq中满足的行数为5101120条，每个执行估计的行为5052680行，循环次数，将达到5101120*5052680=25774332054280次，单纯这个操作耗时就已经占整个计划的89%。也就是耗时的主要成本。

    说明这样增加索引是无法解决问题的。由于表的数据量非常庞大，嵌套循环的次数，导致循环次数太高，耗费大量的CPU时间，从而导致需要非常长的时间。

    如果机器的配置稍差，如tempdb说在的磁盘IO能力不够，CPU的处理能力不够，这个语句可能将无法执行出结果，并且导致其他相关的SQL查询也变慢。

解决方式

• 由于是嵌套循环成本过高而引起的问题，我们可以考虑使用hash的关联方式来避免，因为按照过滤的条件，属于全表扫描。在SQL语句最后，加上option(hash join)指定走哈希连接方式。

    执行计划发生了改变，T_BD_MA