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

【摘要】
L 银行贷款协议存储过程执行需要 2 个小时,严重影响整个跑批任务!如何破解?点击了解开源 SPL 提速银行贷款协议跑批 10+ 倍

问题描述

L银行跑批任务包含很多存储过程,其中“对公贷款协议明细”存储过程运行时间2个小时,是整个跑批任务的瓶颈,亟需优化。

从这个存储过程运行日志可以看出,对公贷款协议明细共有48个步骤,每个步骤包括几个主要SQL和一些辅助SQL组成,共有3300行。从下图可以看出各个步骤执行的时间长度:

..

 

解决办法

首先,深入了解数据和计算特征。对公贷款协议明细存储过程的48个步骤比较复杂,但每个步骤基本上都是三部曲:1、执行主要SQL2、结果保存为临时表;3、为临时表建索引。执行主要SQL的时候,一般会用到前序步骤生成的临时表。

我们以耗时最长的步骤78为例来具体看看数据和计算特征。步骤7耗时49分钟,主要SQL语句,是用小表过滤大表,还有其他条件过滤。脱敏后的SQL语句如下:

select

B_NO,

CUST_NO,

BAL,

nvl(nullif(a.D_BILL, ''), a.BILL_NO),

BEGIN_DATE,

END_DATE

from

BOOK_ HIS a

WHERE CORP = v_corp

  and v_sDate >= BEGIN_DATE and v_eDate <= END_DATE

  and not exists(

select 1

from SESSION.TMP_BILL d

WHERE nvl(nullif(a.D_BILL, ''), a.BILL_NO)=d.R_NO

)

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

SESSION.TMP_W_BILL是前序步骤生成的临时表,是由企业贷款本金表D_HIS_P经过条件过滤得到,数据量约244万条。步骤7中,要用TMP_BILL表中的借据号过滤BOOK_HIS表,计算结果保存为临时表,并且要按照CUST_NOR_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_LYY

FROM session.BOOK_HIS_TEMP a

WHERE (

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)

       or

exists(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万条数据)过滤而来。两个表数据量都不是很大。

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

 

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

步骤7中大表BOOK_HIS要和小表做关联过滤。其结果还要在步骤8中和两个小表做过滤。两个步骤的三个小表都可以全内存,因此要考虑对大表建立游标,一次遍历完成条件过滤、三个小表的关联过滤和最后的分组汇总。这样,可以省去中间结果写硬盘的时间和建立索引的时间。

大表BOOK_HIS要按照CORPBEGIN_DATEEND_DATE过滤,可以考虑将大表按照CORP BEGIN_DATEEND_DATE有序存放,这样可以快速过滤,找到需要的数据。

大表BOOK_HIS与小表SESSION.TMP_BILLnot exists关联计算。关联字段是大表两个字段的表达式,要在遍历大表的时候,先计算表达式,然后直接做关联过滤。

大表BOOK_HIS与两个小表custsession.SALE_BILL分别做关联过滤,结果需要去重合并,因此要采取大表一次遍历,分批在内存中计算两种关联过滤,结果合并的方式,仍可以保证只对大表遍历一次。这种算法,我们称为遍历复用。

最后的分组汇总结果也不大,可以采用在内存中向结果集聚合的方式计算。

大表BOOK_HIS78个字段,参与计算的有7个字段;小表参与计算的字段都只有一个。因此适合列存,可以减少硬盘读取的数据量,提高性能。

两个步骤合并之后,对大表一次遍历,可以采用多线程并行计算,充分发挥CPU16核的计算能力,提高计算速度。

 

第三,确定技术选型和实施方案。关系数据库建立在无序集合理论的基础上,没有办法实现大表BOOK_HIS在物理上有序存放

另外,如果将步骤78两个主要SQL合并成一个SQL,会变得更复杂,数据库自动化优化机制很难达到最佳的查询路径。实际测试也证明,多临时表关联性能会急剧下降,所以整个存储过程才被重构拆分为目前的很多个SQL。也就是说,上面的优化方案没有办法在关系数据库里用SQL实现了。

如果不用关系数据库,那将数据外置到文件来自行处理会更方便一些。服务器内存不大,无法装入全部数据,也无法实施全内存计算技术(包括某些被优化过的内存数据库)。

跑批用到的新增数据每天从生产库导出到跑批库,数据本来就要移动,我们可以在移动过程中同时将数据写出到文件,以实现上述的高性能算法。文件系统的IO性能更好,写入到文件会比写入到数据库更快,数据外置在工程上也是可行的。

基于文件,再使用JavaC++等高级语言可以实现上述算法,但编码量过大,实现周期过长,容易出现代码错误隐患,也很难调试和维护。

开源集算器的SPL语言提供上述所有的算法支持,包括高性能列存文件、文件游标、多线程并行、小结果内存分组、游标复用等机制,能够让我们用较少的代码量快速实现这种个性化的计算。

 

第四,实现实施方案。先要将存储过程用到的大表BOOK_HIS导出为 SPL 列存组表文件,第一次导出时间较长,以后每天增量导出、有序归并,时间就较短了。实际测试发现,新增数据137万,有序归并的时间是5分钟。用到的小表可以每天全量导出,也可以采取增量导出方式。

然后编写SPL代码,实现原存储过程的计算需求。对公贷款协议明细存储过程只是跑批任务很多存储过程的一个,和其他存储过程有先后关系。因此,要采取旁路加速的方式,计算结果还要写回数据库。如下图:

..

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

 

 

应用效果

进过几周时间的编程调试,对公贷款协议明细存储过程的优化完成,效果明显。使用相同的硬件,优化前运行时间是2个小时,优化后仅需要10分钟就可以完成计算,性能提高12倍。

在编程难度方面,SPL做了大量封装,提供了丰富的函数,内置了上述优化方案需要的基本算法和存储机制。上面描述的算法比较复杂,但实际编写的代码并不长,和原来的SQL相比少很多,开发效率很高。原存储过程3300多行,用SPL改写后,仅约500SPL语句即可实现,代码量减少了6倍多,这会极大的提升易维护度。

上述步骤78对应的SPL计算代码如下图:

..

后记

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

 

更多相关案例:

我们怎样把W银行预计算固定条件查询优化成实时灵活条件查询

我们怎样把P保险公司2小时的跑批优化成17分钟

我们怎样把T银行贷款跑批任务提速150+

我们怎样把B银行自助分析从5并发提升到100并发

我们怎样把C银行资金头寸报表提速20+

我们怎样把S银行手机账户查询从预先关联变成实时关联

我们怎样把C保险公司团保明细单查询提速2000+

我们怎样把X银行用户画像客群交集计算提速 200+ 倍


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾