优化SQL执行路径提高报表性能

         报表出现性能问题需要对数据源计算进行优化时，执行路径难以确定从而被干预是阻碍报表优化的难题之一。由于数据库执行路径对开发人员不透明，报表优化需要指定执行路径时，程序员会很难甚至无法干预。而一般报表工具不具备强计算能力，大部分计算仍然要依靠数据库进行，这就导致很多报表优化效果不理想。

         不同于一般报表工具，润乾集算报表内置了专门用于数据计算的集算引擎，开发人员可以通过编写集算脚本完成报表数据源准备。与数据库执行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 onlosr.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、编写集算脚本

         首先，拆分原报表数据集SQL。这个SQL慢的原因在于两个子查询之间的join，那么分别把两个子查询sql写到集算脚本中执行，并且在集算脚本中使用switch完成关联。

         其次，消除报表格间运算。将原报表模板中的格间计算（比值和汇总值）的内容全部移到集算脚本中完成，这样报表少了格子遍历的过程，可以提升性能。

         最后，将结果集一次返回给报表。通过集算脚本完成了所有数据准备的工作后，将结果一次性返回给报表工具，报表接收到数据源后直接进行展现（不再做其他如格间计算类的影响效率的计算）。

         完整的集算脚本如下：

         2、在集算报表中调用上述集算脚本，编辑报表表达式完成报表制作。

优化效果

         报表展现要经历的两步：1数据源计算（执行数据集SQL）阶段，2 报表计算并展现阶段，报表优化前后各阶段与总时间对比见下表：

         通过上面的过程，该报表展现时间从原来的6分42秒，锐降到了57秒，不到1分钟的时间，达到了用户预期的目标。

         使用集算报表进行报表优化适用于从数据库取出数据量不是很大的场景（由于Oracle JDBC速度非常慢），通过使用这种可控的过程优化手段迅速提升报表性能。