存储过程实现报表数据源的利弊分析

        在报表项目中，当数据计算较为复杂的时候，报表开发人员可能会考虑是否用存储过程来实现报表数据源准备。这里，我们从几个不同的方面来看一下，用存储过程来实现报表数据源计算的利和弊。

        一、性能

        存储过程的优点性能是最常被提及的。存储过程进行报表数据计算的时候，不需要将数据取出数据库，会获得较高的性能。其主要原因是数据库IO通道（例如：JDBC）效率一向很差，大量数据取出来很费时间。

        即便如此，这个问题还是要深入分析的：

        1、写在存储过程中的SQL语句是预先编译的，因此比外部程序提交的SQL要快一些。但是，很多情况下，报表的数据计算逻辑过于复杂，很难用单个或者多个SQL来实现。程序员需要利用存储过程的过程控制语句来实现。例如，常见的通过循环遍历数据来实现复杂计算的做法。

        这种情况下，存储过程的性能就表现的很差。其原因是大家常常忽略的：存储过程本身的过程控制代码解释执行的速度，要比SQL慢一个数量级，有些语句的执行速度甚至还会低于外部的Java程序。

        2、存储过程中可以写多个SQL来实现分步计算，但是每一步SQL执行的中间结果难以复用，因此可能会一份数据重复复制多次，算多次，降低性能。存储到临时表虽然可以达到复用目的，但会造成外存访问导致性能更差。

        3、存储过程势必增加数据库的计算负载和空间占用，虽然理论上说可以通过数据库扩容来维持性能和容量，但是数据库的扩容成本毕竟比应用服务器高很多。因此，很多项目只好在一段时间内容忍数据库的性能降低。

        二、 编程难度

        存储过程是基于SQL的，所以SQL固有的一些问题，存储过程也没有办法避免：数据无序、缺乏集合、无法引用、分步不彻底。利用存储过程和SQL实现报表数据源计算需求的过程，事实上就是将业务问题翻译成存储过程和SQL语法的过程（类似小学生解应用题，将题目翻译成形式化的四则运算）。而SQL的模型体系很不符合人们的自然思维习惯，造成问题翻译的极大障碍，使得使用存储过程和SQL实现复杂数据计算的编程过程较为困难，所实现的代码也较难读懂、改写。

        存储过程的另一个弊端是不易移植。和SQL相当的标准化不同，存储过程用到的过程控制语法一般是不同厂家的数据库特有的，换了数据库基本上没法执行。如果报表项目需要访问多个不同种类的数据库，更是存储过程无法实现的。

        同时，因为缺乏很好的开发工具，所以存储过程编程和调试相对比较困难。

        三、代码管理

        数据库中的存储过程提供了“包”的概念，对大量存储过程进行归类。但是除此之外，再无其它分类管理办法。而包只支持一层的分类，所以对于数量庞大的存储过程来说，容易造成管理混乱。在这种情况下，应该用“树”这样的多层分类管理代码。

        存储过程另一个特点是比较有争议的：在生产环境下，可以通过直接修改存储过程的方式修改报表的数据计算逻辑，而不用重启服务器。但这个“优点”同时也带来很大的弊端：有人直接就在正式服务器上修改存储过程，而没有经过完整的测试，程序正确性无法保证，代码管理也变得混乱。

        四、系统维护

        存储过程需要编译才能使用，修改报表数据计算算法时要DBA的配合，需要数据库的写权限，增加安全风险。

        存储过程需要预编译，如果带有引用关系的对象发生改变时，受影响的存储过程将需要重新编译，增加维护工作量。

        小结：经过上述分析，我们认为写存储过程来实现报表复杂数据计算总体来讲是弊大于利的。一般情况下不建议这样做，实在因为数据量导致的性能问题需要用，也要尽量把应用范围压到最小。

 

        对于希望由存储过程解决的复杂数据源问题，可以考虑采用润乾公司开发的集算报表来实现，集算报表内置的开发语言集算器（esproc），相比存储过程而言，在多个方面都具备优势：

        在性能方面，集算报表的esproc基于Java，代码解释执行的速度要快于存储过程自身的控制代码。esproc提供并行执行能力，可以充分利用普通计算机和PC服务器来实现分布式计算集群，可获得远远超过存储过程的性能。

        如果业务允许，可以考虑将数据库中的报表相关数据移到文件系统中。esproc的文件访问和计算能力使得集算报表可以将数据文件作为数据源，充分发挥数据库和数据文件各自的优点，在有效降低数据库压力的同时，进一步提高集算报表的性能。

        在编程难度方面，集算报表的esproc解决了SQL固有的问题，更接近人们的自然思维，可以更快速的写出报表数据计算程序，也非常容易读懂、维护。用esProc解决同样问题的代码长度要远远少于存储过程。

        集算报表是跨平台和数据库的，非常容易移植。也可以从多种数据库、文件中取数，统一进行计算。集算报表提供功能强大的esproc集成开发环境（IDE），降低编程工作量，提高代码调试的效率。

        在代码管理方面，集算报表的程序文件（dfx文件）可以在操作系统中形成树形的结构，形成多层分类管理。

        集算报表的程序文件可以和Java文件一样进行基本的配置管理。如果需要的话，可以导出成文本文件进行更细致的版本管理。

        在系统维护方面，集算报表是在应用服务层运行的，程序改动无需数据库权限，不会带来数据安全上的问题。

        集算报表的esproc程序之间是通过函数的方式调用的，只要函数的接口不变，函数内部的变化不会影响报表本身或者其他esproc程序。

        最后，我们通过一个具体的例子，来看一下用集算报表的esproc和oracle的存储过程分别实现相同数据源计算的代码对比：

        某电信产品厂商有一张报表，主要目的是分析优势产品的销售额、销量、环比等指标，其中优势产品的定义是”在每个州的销量均在前10名的产品”，数据主要存储在stateSales table，其数据结构如下：

        Oracle的存储过程：

01	createorreplacepackagesalesPkg
02	as
03		typesalesCurisrefcursor;
04	end;
05	CREATEORREPLACEPROCEDUREtopPro(io_cursorOUTsalesPkg.salesCur)  
06	is
07	varSqlvarchar2(2000);
08	tb_countinteger;
09	BEGIN
10	selectcount(*) intotb_countfromdba_tableswheretable_name='TOPPROTMP';
11	iftb_count=0then
12	strCreate:='CREATE GLOBAL TEMPORARY TABLE TOPPROTMP ( 
stateTmp NUMBER not null, 
productTmp varchar2(10)  not null,
amountTmp NUMBER not null
              ) 
              ON COMMIT PRESERVE ROWS'; 
13	executeimmediatestrCreate;
14	endif;
15	executeimmediate'truncate table TOPPROTMP';
16	insertintoTOPPROTMP(stateTmp,productTmp,amountTmp) 
selectstate,product,amountfromstateSales a 
wherenot(
         (a.state,a.product) in (
selectstate,productfromstateSalesgroupbystate,producthavingcount(*) >1
         ) 
androwidnotin (
selectmin(rowid) fromstateSalesgroupbystate,producthavingcount(*)>1
         )
       )
orderbystate,product;
17	OPENio_cursorfor
18	SELECTproductTmpFROM (
SELECTstateTmp,productTmp,amountTmp,rankorder
FROM (SELECTstateTmp,productTmp,amountTmp,RANK() OVER(PARTITIONBYstateTmpORDERBYamountTmpDESC) rankorder
FROM TOPPROTMP
         )  
WHERErankorder<=10orderbystateTmp
    )
GROUPBYproductTmp
HAVINGCOUNT(*)=(SELECTCOUNT(DISTINCTstateTmp ) FROM TOPPROTMP);
END;

        esProc的写法：

        集算报表可以通过集算器数据集方便的接收A5的内容，直接展现到报表中。