润乾报表实现计算过程性能优化
当报表出现性能问题,需要对数据源计算进行优化时,控制执行路径是一种有效的方法,但也是阻碍优化的难题。这是由于数据库执行路径不透明,程序员很难甚至无法干预执行路径,进而也就难以提高数据库访问的性能。而对于一般报表工具来说,由于不具备强计算能力,大部分计算仍然要依靠数据库进行,也就导致很多报表优化的效果不甚理想。
不同于一般报表工具,润乾报表在这一点上,由于内置了专门用于数据计算的集算引擎。与数据库执行 SQL 路径不可控相比,集算脚本的执行过程是可控的,开发人员可以通过编写集算脚本完成报表数据源准备,并根据实际情况调整计算执行过程,从而完成报表优化工作。
集算引擎内置了丰富的结构化数据计算的类库,例如连接操作,就包括普通连接 join()、按序号对应连接 pjoin()、叉乘连接 xjoin()等,而针对维表和事实表则提供了更加高效的连接方式 switch(),这样就为开发人员在优化报表时提供了多种选择。此外,由于集算报表支持分步编写,开发人员可以自由决定计算顺序,先算什么后算什么,对一个复杂计算进行合理拆分和整合,复用中间结果,从而通过改变计算顺序来进行优化。
下面是一个针对报表 SQL 数据源优化的实例过程和效果,用户在使用润乾报表(结合集算器实现)时可以参考。
该报表是一张明细表,数据量较大,涉及到的数据库表有几十个之多,数据库表间关联频繁(包括自连接的情况),同时报表中包含多个格间计算表达式(比值和汇总值)。
其中较复杂的数据集 SQL(近 400 行)如下所示:
select t.*
from (select *
from (select syb.org_abbreviation as syb,
max(xmb.org_abbreviation) as xmb,
--省略多个连接、判断、汇总语句
left join losrrr losr on losr.requisition_id =
l.requisition_id
where l.table_type = '1'
and l.requisition_state = '0100500005000000006'
and nvl(l.bsflag, 0) != 1
group by l.requisition_id,
l.note,
--省略多个分组字段
losr.standby_param3,
losr.standby_param6
) a
LEFT JOIN crview ve --视图
ON ve.requisition\_id = a.req\_id
仔细看这个 sql 会发现,首先关联的表比较多,包括很多自关联的情况,其次嵌套了很多子查询,最后又与一个视图进行关联(而视图本身的复杂程度也与上述 SQL 类似)。
实际使用中,该报表查询 4 个月数据所需展现时间为 6 分 42 秒,远远达不到用户要求。显然由于 SQL 比较复杂,数据库执行路径难以控制,所以很难在 SQL 的层面再进行优化。
在使用了润乾报表后,进行优化的过程如下:
1、编写集算脚本
1)拆分原报表数据集 SQL。这个 SQL 慢的原因在于两个子查询之间的 join,那么分别把两个子查询 sql 写到集算脚本中执行,并且在集算脚本中使用 switch 完成关联。
2)消除报表格间运算。将原报表模板中的格间计算(比值和汇总值)的内容全部移到集算脚本中完成,由此减少格子遍历的过程,可以提升性能。
3)将结果集一次返回给报表。在使用集算脚本完成了所有数据准备的工作后,将结果一次性返回给报表工具,报表接收到数据后直接进行展现,不再做类似格间计算这样影响效率的计算。
完整的集算脚本如下:
A | B | C | D | E | F | |
1 | =connect("xcoms2") | |||||
2 | =A1.query(${sql1}) | /sql 通过宏传递 | ||||
3 | =A1.query(${sql2}) | |||||
4 | =A2.switch(REQ_ID,A3:REQUISITION_ID) | |||||
5 | =A4.new(#:SN,SYB, XMB, SUB_ID, ORG_ABB, REC_NOTICE_ORG_ID, XZ_TEAM, XZ_ORG, REQ_CD, REQ_ID, REQ_NOTE, SFLJ, SFYY, SFZL, OPS_RECORD_ID, REQ_TYPE, TASK_GOAL, WELL_NAME, WELL_SORT, WELL_TYPE, BLOCK, PRO_NAME, SGGY, YQZH, OIL_REGION_CODE, YQXL, SRM, BEGIN_TIME, BACK_TIME, DOWN_TIME, SETUP_TIME, IS_FAIL, FINISH_STATUS, WCZT, JFDW, REQ_ZYXM, DESIGN_WELL_DEPTH, NOTE1, YJ, SJ, LEADER, XJZCS, CGCS, HJYS, YQYZ, PLAN_CORE_NUM, GET_CORE_NUM, FIRE_NUM, SEND_NUM, GET_NUM, ELIGIBILITY_NUM, ZYRY, USE_BULLET_NUM, SEND_BULLET_NUM, ZSKM, SKM, JCH, QBQX, YDQX, HGQX, SKD, KM, ZYSJ, ZZYSJ, GZSJ, GGXH, QSLB, REQ_ID.REQUISITION_ID:REQUISITION_ID, REQ_ID.REQUISITION_TYPE:REQUISITION_TYPE, REQ_ID.BACK_DATE:BACK_DATE, REQ_ID.REPORT_BASE_TIME:REPORT_BASE_TIME, REQ_ID.RWD_SUB_ID:RWD_SUB_ID, REQ_ID.JTZ_SUB_ID:JTZ_SUB_ID, REQ_ID.GUN:GUN, REQ_ID.BUM:BUM, REQ_ID.RECEIVE_DATE:RECEIVE_DATE, REQ_ID.JJTJ:JJTJ, REQ_ID.ASSESS_DATE:ASSESS_DATE, REQ_ID.PDTJ:PDTJ, REQ_ID.ZYXM:ZYXM, REQ_ID.CLM:CLM, REQ_ID.JDF:JDF, REQ_ID.JDT:JDT, REQ_ID.SDM:SDM, REQ_ID.JJM:JJM, REQ_ID.JSM:JSM, REQ_ID.JD:JD, REQ_ID.JFDW1:JFDW1, REQ_ID.JFDW2:JFDW2, REQ_ID.JFDW3:JFDW3, REQ_ID.SBCS:SBCS, REQ_ID.FHCS:FHCS, REQ_ID.SYQCS:SYQCS ,if(CGCS==null || CGCS==0 || XJZCS==null || XJZCS==0,0,CGCS/XJZCS*100):PERCENT1, if(CGCS==null || CGCS==0 || (XJZCS-HJYS)==null || (XJZCS-HJYS)==0,0,CGCS/(XJZCS-HJYS)*100):PERCENT2, if(HGQX==null || HGQX==0 || QBQX==null || QBQX==0,0,HGQX/QBQX*100):PERCENT3, if(FHCS==null || FHCS==0 || SYQCS==null || SYQCS==0,0,FHCS/SYQCS*100):PERCENT4, if(FHCS==null || FHCS==0 || SYQCS==null || SYQCS==0,0,FHCS/SYQCS*100):PERCENT5 ) | |||||
6 | // 以下 A7-F12 求汇总值 | |||||
7 | =A5.sum(YJ) | =A5.sum(SJ) | =A5.sum(XJZCS) | =A5.sum(CGCS) | =A5.sum(HJYS) | =A5.sum(YQYZ) |
8 | =if(D7==0 || D7==null || C7==0 || C7==null,0,D7/C7*100) | =if(D7==0 || D7==null || C7-E7==0 || C7-E7==null,0,D7/(C7-E7)*100) | =A5.sum(JDF) | =A5.sum(JDT) | =A5.sum(CLM) | =A5.sum(SDM) |
9 | =A5.sum(JJM) | =A5.sum(JSM) | =A5.sum(PLAN_CORE_NUM) | =A5.sum(GET_CORE_NUM) | =A5.sum(FIRE_NUM) | =A5.sum(SEND_NUM) |
10 | =A5.sum(GET_NUM) | =A5.sum(ELIGIBILITY_NUM) | =A5.sum(ZSKM) | =A5.sum(SKM) | =A5.sum(JCH) | =A5.sum(SRM) |
11 | =A5.sum(QBQX) | =A5.sum(HGQX) | =A5.sum(YDQX) | =if(B11==0 || B11==null || A11==0 || A11==null,0,B11/A11*100) | =if(C11==0 || C11==null || A11==0 || A11==null,0,C11/A11*100) | =A5.sum(SBCS) |
12 | =A5.sum(SYQCS) | =A5.sum(FHCS) | =if(B12==0 || B12==null || A12==0 || A12==null,0,B12/A12*100) | =A5.sum(ZZYSJ) | =A5.sum(ZYSJ) | =A5.sum(GZSJ) |
13 | >A5.insert(0,"合计","","","","","","","","","","","","","","","","","","","","","","","","","", F10,"","","","","","","","","","","", A7, B7,"", C7, D7, E7, F7, C9, D9, E9, F9, A10, B10,"","","", C10, D10, E10, A11, C11, B11,"","", E12,"", F12,"","","","","","","","","","","","","","","", E8, C8, D8, F8, A9, B9,"","","","", F11, B12, A12, A8, B8, D11, E11, C12) | |||||
14 | return A5 |
2、设计报表
在润乾报表中调用上述集算脚本,编辑报表表达式完成报表制作。
优化效果
报表展现要经历两个阶段:1 数据源计算(执行数据集 SQL)阶段,2 报表计算并展现阶段。两个阶段的时间以及总的时间在优化前后的对比如下:
数据集计算 | 报表计算展现 | 总时间 | |
---|---|---|---|
优化前 | 317S | 85S | 402S |
优化后 | 52S | 5S | 57S |
通过上面的过程,该报表展现时间从原来的 6 分 42 秒,锐降到了 57 秒,不到 1 分钟的时间,达到了用户预期的目标。
针对不同的场景,润乾报表都有相应的优化方案,上述计算过程的优化适用于数据库取出数据量不是很大的场景(Oracle JDBC 速度非常慢)。如果数据量很大,还可以使用润乾报表的大数据集功能,请参考相关文档。
更多性能优化相关问题请查看:性能优化相关问题分类导航
对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅
嗯,还不太了解好多乾?