列表查询使用单据上的分录号排序导致性能问题的解决方案原创
金蝶云社区-墨迹
墨迹
9人赞赏了该文章 212次浏览 编辑于2022年1月12日 17:17:41

问题描述

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

l  问题SQL

image.png

l  执行计划

image.png

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

    排序操作的详情

image.png

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

image.png

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

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

l  检查索引定义情况

image.png

image.png

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

image.png

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

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

image.png

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

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

解决方式

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

image.png

    执行计划发生了改变,T_BD_MATERIAL和T_AR_RECEIVABLEENTRY表右关联后,跟T_AR_RECEIVABLE关联,但总共的开销成本在整个计划中只有3%左右。

     从HASH Match可以看到,Hash键是以T_BD_MATERIAL的FMATERIAL字段,而这个字段属于主键,所以hash key生成的成本并不高,而FMATERIAL在T_AR_RECEIVABLEENTRY为索引字段,所以关联的总共成本并不高。

image.png

image.png

    整个执行时间花费28秒左右,比采用嵌套连接有明显改善。


  • 由于排序使用了t0.fid和t1.fseq,t1表也有fid,可以考虑在t1表上,增加组合索引fid,fseq。由于t1.fid=t0.fid,执行引擎对T1表会自动fid和fseq进行排序,由于只有两个字段,并且存储在索引表中,所以排序的成本将极大减少

image.png

    使用组合索引后,可以看到数据量,极大减少,说明IO得到明显下降,只需要593毫秒。

image.png

总结

       基于列表查询的功能,如果选择了按单据分录的行号排序时,出现执行时间很长或者无法返回结果后,可以比对是否跟上述的写法类型,如果是,需要在表体上增加fid和fseq为组合索引。但如果排序的条件不相同,还是需要具体问题具体分析。

       另外需要注意,如果两个表的比较的字段非常多的时候,这种组合索引也将带来大量的IO开销,不一定能解决问题,这个时候,如果不改写SQL写法,可能需要采用option(hash join)来解决问题。

9