润乾报表实现计算过程性能优化

当报表出现性能问题,需要对数据源计算进行优化时,控制执行路径是一种有效的方法,但也是阻碍优化的难题。这是由于数据库执行路径不透明,程序员很难甚至无法干预执行路径,进而也就难以提高数据库访问的性能。而对于一般报表工具来说,由于不具备强计算能力,大部分计算仍然要依靠数据库进行,也就导致很多报表优化的效果不甚理想。

不同于一般报表工具,润乾报表在这一点上,由于内置了专门用于数据计算的集算引擎。与数据库执行 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 完成关联。

imagepng

2)消除报表格间运算。将原报表模板中的格间计算(比值和汇总值)的内容全部移到集算脚本中完成,由此减少格子遍历的过程,可以提升性能。

imagepng

3)将结果集一次返回给报表。在使用集算脚本完成了所有数据准备的工作后,将结果一次性返回给报表工具,报表接收到数据后直接进行展现,不再做类似格间计算这样影响效率的计算。

imagepng

完整的集算脚本如下:


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 速度非常慢)。如果数据量很大,还可以使用润乾报表的大数据集功能,请参考相关文档。

更多性能优化相关问题请查看:性能优化相关问题分类导航

以下是广告时间

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



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