大数据交叉报表解决案例(方案)

软硬件环境

OS:win7

Cpu:8 核

集算报表:1120 安装版

Jvm:1G

数据库:oracle11g

客户无法解决的问题:

有一个交叉汇总报表,其实格式很简单,行列各一个统计维度。但后台业务表的数据有 175 万条,且还要与其他表(大概在 7w 条左右)做 join,如果由 sql 来处理,可以想象到会慢到什么程度,关键受各种条件影响,能否查出数据都是问题。

注:ACCORECEIVE 表 175w 条数据

目前,测试 birt 需 5 分钟,借助各种中间表与视图。报表友商无法出表。

要求:能做出该报表在 web 展现,且重要的是速度要快,另外,数据(目前大概是 5 年数据)是实时增加的。

客户报表格式及目前所用 sql:

报表格式:

Sql:

select LOCATIONS.loupan loupan,

       LOCATIONS.LPORDERNUM,

       nvl(ACCORECEIVE.RECEIVABLEAMOUNT, 0) yingshou,

       chargeproct.Description CHARPNAME,

       chargeproct.ordernum chordernum

  from ACCORECEIVE,V\_LOCATION\_LP\_LG\_DY LOCATIONS,chargeproct

   where ACCORECEIVE.Org\_Id = LOCATIONS.Org\_Id

   and ACCORECEIVE.Sub\_Org\_Id = LOCATIONS.Sub\_Org\_Id

   and ACCORECEIVE.Fk_Locationid = LOCATIONS.Locationid

   and ACCORECEIVE.Fk_Chargeproctid = chargeproct.chargeproctid(+)

   and ACCORECEIVE.Wf_Status not in('作废')

问题分析及解决方案

常规模式下,大数据要出交叉报表几乎很难,这里受 sql 效率慢、jvm 等的影响,一次如果把所有数据全部取出则必然极大可能内存溢出。另外,大数据表再有 join,即便能取,那取数速度上肯定也无法保证(sql join 的效率低),上面 sql 中能体现出所有问题。

解决方案:

1、为避免一次性取数内存溢出,可采用集算器游标 cursor 取数;  –cursor

2、去除不需要字段及 join 字段。分析后发现,客户实际不需要 org_id、sub_org_id 的关联;

3、取数后可根据客户所出报表对应做数据处理,这里可 groups 处理一次分组汇总;–替代报表表达式 group

4、为摆脱 sql join 效率低问题,可将 join 放在集算器内处理,这里 ACCORECEIVE 与 V_LOCATION_LP_LG_DY 表(query 即可,数据不大)分开取数;  –switch 连接

注:集算器中测试了两表 sql 中 join,时间大概需 5 分钟。

5、结合客户报表格式及所用的数据库表,可将上面 sql 中 chargeproct 表放到报表 sql 取数,因其仅体现显示值作用,且仅几十条数据。

集算脚本:

注:代码有每一步的作用说明

结论

润乾能出表且速度最快,客户联系人是非常满意的。对比:

1、友商:无法出表,包含不做 join 仅单查业务表数据。

2、Birt:客户说 4、5 分钟出表,虽无法验证,但个人有点怀疑;

3、润乾:12s(多次测试)左右,(取数 + 报表展现)。–因数据处理在集算器已完成,所以报表几乎无计算,报表计算及生成 html(大概是 20 行 +35 列的单元格)基本不占用时间。

更多大数据相关问题请查看:性能优化相关问题分类导航
* 数据量大计算复杂时,数据预处理该怎么做
* 海量历史数据分析,占用大量数据库资源—看库外计算怎样解决这个难题
* 多库取数的性能优化方案
* 如何解决报表关联计算中的性能问题
* 如何提高报表的取数性能