查看原文
其他

开源SPL提速银行贷款协议跑批10+倍

乾学院 2022-11-14

问题描述

L银行跑批任务包含很多存储过程,其中“对公贷款协议明细”存储过程运行时间2个小时,是整个跑批任务的瓶颈,亟需优化。
从这个存储过程运行日志可以看出,对公贷款协议明细共有48个步骤,每个步骤包括几个主要SQL和一些辅助SQL组成,共有3300行。从下图可以看出各个步骤执行的时间长度:

解决办法

首先,深入了解数据和计算特征。对公贷款协议明细存储过程的48个步骤比较复杂,但每个步骤基本上都是三部曲:1、执行主要SQL;2、结果保存为临时表;3、为临时表建索引。执行主要SQL的时候,一般会用到前序步骤生成的临时表。
我们以耗时最长的步骤7、8为例来具体看看数据和计算特征。步骤7耗时49分钟,主要SQL语句,是用小表过滤大表,还有其他条件过滤。脱敏后的SQL语句如下:
selectB_NO,CUST_NO,BAL,nvl(nullif(a.D_BILL, ''), a.BILL_NO),BEGIN_DATE,END_DATEfromBOOK_ HIS aWHERE CORP = v_corp and v_sDate >= BEGIN_DATE and v_eDate <= END_DATE and not exists(select 1from SESSION.TMP_BILL dWHERE nvl(nullif(a.D_BILL, ''), a.BILL_NO)=d.R_NO)

贷款台账信息历史表BOOK_HIS数据量是1.1亿条,有78个字段,参与计算的有7个字段,主键是CORP,D_BILL,BEGIN_DATE,END_DATE。

SESSION.TMP_W_BILL是前序步骤生成的临时表,是由企业贷款本金表D_HIS_P经过条件过滤得到,数据量约244万条。步骤7中,要用TMP_BILL表中的借据号过滤BOOK_HIS表,计算结果保存为临时表,并且要按照CUST_NO和R_NO分别建索引,为下一个步骤做准备。
步骤8耗时36分钟,是用两个小表来过滤步骤7的结果临时表,过滤之后再按照RC_NO分组汇总。
SELECT LN_R_NO, SUM(CASE WHEN v_sDate_LM BETWEEN BEGIN_DATE AND END_DATE THEN BAL END) BAL_LM, SUM(CASE WHEN v_sDate_LY BETWEEN BEGIN_DATE AND END_DATE THEN BAL ELSE 0 END) BAL_LYYFROM session.BOOK_HIS_TEMP aWHERE (exists(select 1 from CUST b where a.CUST_NO=b.CUST_NO and b.CORP=v_corp and v_sDate BETWEEN b.BEGIN_DATE AND b.END_DATE) orexists(select 1 from session.SALE_BILL c WHERE a.R_NO=c.R_NO) )GROUP BY R_NO

对公客户基本信息历史表CUST,共57万条数据,session.SALE_BILL 是从ACC表(3.2万条数据)过滤而来。两个表数据量都不是很大。

跑批服务器是16核CPU,内存32G。数据每天通过ETL从生产数据库增量更新到DB2数据库中。

第二,确定优化思路。服务器内存不太大,贷款台账信息历史表BOOK_HIS这样的大表无法全部装入内存,因此采用大表外存、小表内存的计算方式。

步骤7中大表BOOK_HIS要和小表做关联过滤。其结果还要在步骤8中和两个小表做过滤。两个步骤的三个小表都可以全内存,因此要考虑对大表建立游标,一次遍历完成条件过滤、三个小表的关联过滤和最后的分组汇总。这样,可以省去中间结果写硬盘的时间和建立索引的时间。
大表BOOK_HIS要按照CORP,BEGIN_DATE,END_DATE过滤,可以考虑将大表按照CORP, BEGIN_DATE,END_DATE有序存放,这样可以快速过滤,找到需要的数据。
大表BOOK_HIS与小表SESSION.TMP_BILL做not exists关联计算。关联字段是大表两个字段的表达式,要在遍历大表的时候,先计算表达式,然后直接做关联过滤。
大表BOOK_HIS与两个小表cust和session.SALE_BILL分别做关联过滤,结果需要去重合并,因此要采取大表一次遍历,分批在内存中计算两种关联过滤,结果合并的方式,仍可以保证只对大表遍历一次。这种算法,我们称为遍历复用。
最后的分组汇总结果也不大,可以采用在内存中向结果集聚合的方式计算。
大表BOOK_HIS有78个字段,参与计算的有7个字段;小表参与计算的字段都只有一个。因此适合列存,可以减少硬盘读取的数据量,提高性能。
两个步骤合并之后,对大表一次遍历,可以采用多线程并行计算,充分发挥CPU16核的计算能力,提高计算速度。

第三,确定技术选型和实施方案。关系数据库建立在无序集合理论的基础上,没有办法实现大表BOOK_HIS在物理上有序存放。
另外,如果将步骤7、8两个主要SQL合并成一个SQL,会变得更复杂,数据库自动化优化机制很难达到最佳的查询路径。实际测试也证明,多临时表关联性能会急剧下降,所以整个存储过程才被重构拆分为目前的很多个SQL。也就是说,上面的优化方案没有办法在关系数据库里用SQL实现了。
如果不用关系数据库,那将数据外置到文件来自行处理会更方便一些。服务器内存不大,无法装入全部数据,也无法实施全内存计算技术(包括某些被优化过的内存数据库)。
跑批用到的新增数据每天从生产库导出到跑批库,数据本来就要移动,我们可以在移动过程中同时将数据写出到文件,以实现上述的高性能算法。文件系统的IO性能更好,写入到文件会比写入到数据库更快,数据外置在工程上也是可行的。
基于文件,再使用Java或C++等高级语言可以实现上述算法,但编码量过大,实现周期过长,容易出现代码错误隐患,也很难调试和维护。
开源集算器的SPL语言提供上述所有的算法支持,包括高性能列存文件、文件游标、多线程并行、小结果内存分组、游标复用等机制,能够让我们用较少的代码量快速实现这种个性化的计算。

第四,实现实施方案。先要将存储过程用到的大表BOOK_HIS导出为 SPL 列存组表文件,第一次导出时间较长,以后每天增量导出、有序归并,时间就较短了。实际测试发现,新增数据137万,有序归并的时间是5分钟。用到的小表可以每天全量导出,也可以采取增量导出方式。
然后编写SPL代码,实现原存储过程的计算需求。对公贷款协议明细存储过程只是跑批任务很多存储过程的一个,和其他存储过程有先后关系。因此,要采取旁路加速的方式,计算结果还要写回数据库。如下图:

集算器SPL支持命令行调用,DB2数据库的跑批存储过程可以通过命令行调用已经写好的SPL代码。SPL 也支持JDBC接口,可以用第三方调度工具调用。

应用效果

进过几周时间的编程调试,对公贷款协议明细存储过程的优化完成,效果明显。使用相同的硬件,优化前运行时间是2个小时,优化后仅需要10分钟就可以完成计算,性能提高12倍。
在编程难度方面,SPL做了大量封装,提供了丰富的函数,内置了上述优化方案需要的基本算法和存储机制。上面描述的算法比较复杂,但实际编写的代码并不长,和原来的SQL相比少很多,开发效率很高。原存储过程3300多行,用SPL改写后,仅约500格SPL语句即可实现,代码量减少了6倍多,这会极大的提升易维护度。
上述步骤7、8对应的SPL计算代码如下图:

后记

解决性能优化难题,最重要的是设计出高性能的计算方案,有效降低计算复杂度,最终把速度提上去。因此,一方面要充分理解计算和数据的特征,另一方面也要熟知常见的高性能算法,才能因地制宜地设计出合理的优化方案。本次工作中用到的基本高性能算法,都可以从下面这门课程中找到:点击这里学习性能优化课程(底部原文中可点击链接),有兴趣的同学可以参考。
很遗憾的是,当前业界主流大数据体系仍以关系数据库为基础,无论是传统的MPP还是HADOOP体系以及新的一些技术,都在努力将编程接口向SQL靠拢。兼容SQL确实能让用户更容易上手,但受制于理论限制的SQL却无法实现大多数高性能算法,眼睁睁地看着硬件资源被浪费,还没有办法改进。SQL不应是大数据计算的未来。
有了优化方案后,还要用好的程序语言来高效地实现这个算法。虽然常见的高级语言能够实现大多数优化算法,但代码过于冗长,开发效率过低,会严重影响程序的可维护性。集算器SPL是个很好的选择,它有足够的算法底层支持,代码能做到很简洁,还提供了友好的可视化调试机制,能有效提高开发效率,以及降低维护成本。


重磅!开源SPL交流群成立了

简单好用的SPL开源啦!

为了给感兴趣的小伙伴们提供一个相互交流的平台,

特地开通了交流群(群完全免费,不广告不卖课)

需要进群的朋友,可长按扫描下方二维码

本文感兴趣的朋友,请转到阅读原文去收藏 ^_^

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存