开源SPL提速保险公司团保明细单查询2000+倍

【摘要】
只用了一天的时间,就把明细查询提速 2000 多倍,真的很香!点击了解开源 SPL 提速保险公司团保明细单查询 2000+ 倍

问题描述

C保险公司业务系统中,团体保险明细查询速度很慢。查询时输入保单号,要返回团体保单包含的所有被保险人的信息。较小的保单,包含1万个被保险人,返回页面需要等待7.5分钟。较大的保单,包含100万被保险人,返回页面等待了4个小时没有出来。

团体保险明细比较大,分两个数据库保存。每个团体保单的数据,在两个库中都有可能出现。数据库是OracleSQL语句共163行,如下图:

..

分析解决

面对性能问题,需要仔细分析数据和计算的特征,定位性能关键点,通过改变数据的存储方式和计算方法逐步优化。

第一步,确认需求前提。团体保险明细查询是应用系统中的一个功能,需要查询最新数据。如果采用ETL定时将数据导出计算的方式,不能满足这个要求。因此,还是要想办法从数据库取数、库外计算,来优化性能。

第二步,了解业务需求特征。团体保险明细数据存放在两个数据库db1db2,每个数据库都有两个表m1m2。这四个表在查询时要合并查询结果,我们统一称为团体保险明细表。

四个团体保险明细表有所不同,但是都可以查询出主要字段:保单号、保险成员号、批改次数、业务编号1、业务编号2、业务标志,还有姓名、性别年龄等个人信息

“批改”是针对保险合同的调整,系统将调整后的最新保险明细也保存在团体保险明细表中,不会修改原保险明细,保留轨迹。在数据中通过“批改次数”字段体现。查询时,要查询批改次数最大的一次,也就是最新的数据。

明细数据中还有一部分是无效数据。要看业务编号1和业务标志连接成的字符串是否在无效集合中。无效集合是指:同一个保单号的数据中,批改次数小于9,并且业务标志为D或者U时,业务编号2和字母A连接成的字符串形成的集合。如果业务编号1和业务标志连接成的字符串出现在无效集合中,这条记录就是无效的记录,要舍弃掉。

第三步,梳理研究计算过程。SQL虽然比较长,但是可以分成几个部分。第一部分是两个数据库的4个团体保险明细表,各自按照保单号查询需要的数据,再用union合并在一起。第二部分是条件过滤,包括去掉无效数据和另外几个简单的条件。第三部分是用窗口函数row_number() OVER(PARTITION BY 保险成员号 ORDER BY 批改次数 desc),查找批改次数最大的明细记录。

第一部分单独执行时,返回的结果数据量是几万到几百万,全部返回的时间比较长。如果用数据库JDBC游标的话,很快就能返回部分数据,比如几秒就可以返回几千条。

第二部分,单独从数据库中取得无效集合只需要几秒,而且返回结果数据量不大,可以全内存。

但是,第一部分和第二部分合并执行的时候,速度就变得很慢,即使是游标方式分批返回,也还是很慢。如果再加上第三部分,就更慢了。

第四步,设计呈现方案。根据SQL分段执行的情况,确定采用流式大报表的方式实现提速,原理如下图:

..

从数据库取数和呈现采用两个异步线程,取数线程发出 SQL 后不断取出数据经过复杂计算后,缓存到本地。再由呈现线程从本地缓存中获取数据进行显示。这样,已经取出并缓存的数据就能快速呈现,不再有等待感。

第五步,设计计算过程优化方案。我们考虑将取数和计算分三段实现。

第一段,上面说的第一部分SQL加上按照保险成员号和批改次数降序排序之后,用数据库JDBC游标依然能够快速分批取出部分数据。加上排序,可以在分批取出数据时,保证一个保险成员的数据相邻取出,在后续第三段中,就能够快速找到批改次数最大的最新数据。

第二段,我们将这个保单的无效集合一次性取出到内存中,对第一段分批取出的数据进行过滤,计算出符合条件的有效明细。无效数据并不多,不会过滤掉太多的明细数据。

第三段,根据被保险人号是否改变,判断是不是一个被保险人的第一条数据。因为明细数据按照被保险人和批改次数有序,所以当被保险人号改变的时候,第一条数据就是当前被保险人批改次数的最大值。这样就起到了,和上面说到的窗口函数一样的作用。

由于每个保险成员的数据量都不大,一般是最多十几条数据(对应几次到十几次批改),而且无效数据并不多。所以第一部分分批取出的数据量不需要很多,就可以向前端批量返回数据了。这是流式大报表能够快速展现的必要条件。

第六步,设计代码实现方案。使用延迟游标的方法实现上述三个分段。延迟游标的原理是,先依次定义三个分段的游标计算,定义的时候并不真的执行计算,而是在三个分段都定义好之后再执行。延迟计算的好处是可以一次遍历完成三个分段计算,不必生成中间结果占用空间,可以把查询结果分批提交给前端去展现。

第三段游标计算比较复杂,需要用程序游标来实现。原理如下图:

..

程序游标要做到被调用的时候,边计算边返回结果,这样才能达到流式大报表的要求。

 

实际效果

根据计算特征拟定了优化方案后,需要选择合适的工具来实现计算和展现的性能优化。直接使用Java当然可以实现,但编码量过大,实现周期过长,容易出现代码错误隐患,也很难调试和维护。而开源的集算器SPL语言提供上述所有的算法支持,包括延迟游标、游标有序分段取出、程序游标等机制,能够让我们用较少的代码量快速实现这种个性化的计算。前端呈现需要支持流式大报表机制的报表工具,我们选择了润乾报表来实现。

仅仅经过1天时间的编程、调试和测试,就完成了性能优化的验证,而且查询的响应速度非常快。较小的保单,包含1万个被保险人,原来返回页面需要等待7.5分钟,优化后的报表首页只需要3秒即可展现出来。较大的保单,包含100万被保险人,原来返回页面等待了4个小时没有出来,优化后的报表首页仅7秒即可展现出来,响应速度提高了2000倍还多。

在编程难度方面,SPL做了大量封装,提供了丰富的函数,内置了上述优化方案需要的基本算法和存储机制。实际编写的代码很短,开发效率很高。上述取数的三段代码只有这么几行:

..

后记

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

对于本例中的报表呈现,还需要有能支持流式呈现的报表工具,这方面润乾报表有独特的优势,不需要全部取出数据就可以开始呈现,也不依赖于数据库分页机制(这种方法可能造成数据不一致)就可以支持高速前后翻页。这样才能获得业务用户的良好体验。

正在为 SQL 性能优化头疼的同学们,可以和我们一起探讨:http://www.raqsoft.com.cn/wx/Query-run-batch-ad.html

 

更多相关案例:

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

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

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

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

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

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

我们怎样把L银行贷款协议跑批速度提高 10+ 倍

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

以下是广告时间

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



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