开源SPL提速银行贷款协议跑批10+倍
【摘要】
L 银行贷款协议存储过程执行需要 2 个小时，严重影响整个跑批任务！如何破解？点击了解开源 SPL 提速银行贷款协议跑批 10+ 倍
问题描述
L银行跑批任务包含很多存储过程，其中“对公贷款协议明细”存储过程运行时间2个小时，是整个跑批任务的瓶颈，亟需优化。
从这个存储过程运行日志可以看出，对公贷款协议明细共有48个步骤，每个步骤包括几个主要SQL和一些辅助SQL组成，共有3300行。从下图可以看出各个步骤执行的时间长度：
解决办法
首先，深入了解数据和计算特征。对公贷款协议明细存储过程的48个步骤比较复杂，但每个步骤基本上都是三部曲：1、执行主要SQL；2、结果保存为临时表；3、为临时表建索引。执行主要SQL的时候，一般会用到前序步骤生成的临时表。
我们以耗时最长的步骤7、8为例来具体看看数据和计算特征。步骤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个字段，主键是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_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万条数据）过滤而来。两个表数据量都不是很大。
跑批服务器是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是个很好的选择，它有足够的算法底层支持，代码能做到很简洁，还提供了友好的可视化调试机制，能有效提高开发效率，以及降低维护成本。
更多相关案例：
英文版