存储过程性能低怎么破?
报表应用中实现数据源计算经常会使用存储过程,但同时也带来多方面的问题。首先,存储过程的包只提供一层分类,无法用树形结构组织,容易造成代码管理混乱。而有些程序员更是直接在现场在线修改存储过程,也不利于代码管理。其次,升级存储过程时需要数据库的写权限,容易对数据安全造成影响。另外,由于 SQL 固有的一些问题(数据无序、缺乏集合、无法引用、分步不彻底,等等),使得存储过程的编程也比较困难。
很多情况下选择存储过程是为了提高性能,但实际效果并不尽如人意。这主要是因为报表数据的计算一般都比较复杂,很难用 SQL 直接完成,而是需要通过循环遍历等代码逻辑来控制完成。在这种逻辑中,动辄进行遍历的 SQL 语句往往会被多次执行,从而使得计算要比 SQL 还慢一个数量级,甚至有些语句的执行速度比外部的 Java 程序还要低。
除此之外,还有一个问题,存储过程是存储在数据库内的,而报表工具的模板一般是文件形式,两者要一起配合工作才能完成报表,但这种分开存储的方式实际上很容易导致版本不一致,从而增加管理难度。例如,某报表的模板可能已经被删除了,但对应的存储过程还未删除。又或者数据库开发者修改了存储过程,却没有通知报表开发者。
为了避免使用存储过程导致的上述弊端,可以采用润乾报表及其内置的集算引擎来完成复杂的数据源计算。两种解决方案的系统结构的对比如下图所示:
可以看到,润乾报表的报表文件(.rpx)和集算脚本(.dfx)是独立的文件,因此可以使用文件系统的树形目录来统一管理,而某些简单脚本还可以直接写进 rpx。这样,两者很容易保持一致,方便管理,降低了维护成本。而对于升级过程,润乾报表是通过替换这两种文件来完成的,也可以避免在线修改运行环境。同时,集算脚本解决了前面所说的 SQL 的各种固有问题,编程思维更加自然,比存储过程更加容易。
从性能角度来看,集算引擎提供了并行计算能力,可以充分发挥服务器多 CPU 多核的效能,也可以连接集算服务器集群,实现多机并行,因此能够在许多情况下获得超过存储过程的性能。
当然,从上图中也可以看到,某些情况下存储过程还是需要的。这是因为涉及数据量大的库内运算时,用存储过程会更快。事实上我们的目标不是完全替代存储过程,而是利用润乾报表尽量减少存储过程的使用,提高综合效率和性能。
下面,我们就通过具体的例子,来看一下润乾报表是如何减少存储过程的。
某网络平台需要监测一定周期内的用户状况,为运营部门出具日报、周报、月报、年报等报表,每类报表都需要进行本期与上期、上上期数据的比较,涉及数据较为杂乱。这里就以日报作为例子(月报年报只是统计周期不同)看一下。报表格式如下:
报表分为两部分,上半部分为用户明细数据(本期、上期、上上期在线时长均不为空的用户),而由于用户较多,报表只显示按本期在线时长排序的前十名和后十名用户;报表下半部分为本期数据与上期、上上期的比较结果(允许本期、上期、上上期在线时长为空)。
数据来自于两个数据表:
create table T\_DW\_ZX\_ACCOUNT\_STATUS_DAY
(
LOGTIME DATE,--日志时间
USERID NUMBER(12),--用户号
ACCOUNT VARCHAR2(50),--账号
ONLINETIME NUMBER(8),--在线时间
PAY NUMBER(11),
EXPEND NUMBER(11),
TOP_LEVEL NUMBER(4) );
create table T\_DW\_ZX\_VALID\_ACCOUNT
(
USERID NUMBER,--用户编号
FIRST\_LOGOUT\_TIME DATE,--第一次登出时间
STANDARD\_7D\_TIME DATE,
STANDARD\_14D\_TIME DATE,
ACCOUNT VARCHAR2(50)
);
首先,看一下存储过程的实现方式(为了说明方便,将其分成颜色不同的四部分):
CREATE OR REPLACE PACKAGE BODY CURSPKG AS
PROCEDURE sp_query_user_status_day(data_date IN varchar2,
top10 OUT T_CURSOR,
last10 in out t_cursor,
var1 out number,
var2 out number,
var3 out number,
var4 out number,
var5 out number,
var6 out number) IS
V_CURSOR1 T_CURSOR; --top10
V_CURSOR2 T_CURSOR; --last10
V_CURSOR T_CURSOR; --temp
table v_ttime date;
temp_num number;
v_valid_user_conti_act1 number;
v_valid_user_back1 number;
v_valid_user_conti_act_lost1 number;
v_valid_user_active_lost1 number;
v_valid_user_add_lost1 number;
v_valid_user_back_lost1 number;
BEGIN
v_ttime := to_date(data_date, 'yyyy-mm-dd');
--for temp table
select count(1) into temp_num from account_status_day_temp;
if temp_num > 0 then
delete from account_status_day_temp; --delete first
end if;
insert into account_status_day_temp
select *
from (select v.userid, v.first_logout_time
from t_dw_zx_valid_account v
where v.standard_7d_time is not null) a,
(select userid, sum(onlinetime) onlinetime, max(account)
from t_dw_zx_account_status_day
where logtime >= v_ttime and logtime < v_ttime + 1
group by userid
having max(account) is not null) b,
(select userid, sum(onlinetime) onlinetime, max(account)
from t_dw_zx_account_status_day
where logtime >= v_ttime - 1
and logtime < v_ttime
group by userid
having max(account) is not null) c,
(select userid, sum(onlinetime) onlinetime, max(account)
from t_dw_zx_account_status_day
where logtime >= v_ttime - 1 - 1
and logtime < v_ttime - 1
group by userid
having max(account) is not null) d
where a.userid = b.userid(+) and a.userid = c.userid(+);
commit;
--top 10
open V_CURSOR1 for
select *
from (select rownum,
a.auserid userid,
a.first_logout_time,
a.bonlinetime current_onlinetime,
a.conlinetime last_onlinetime,
a.donlinetime last_last_onlinetime
from account_status_day_temp a order by bonlinetime desc)
where rownum < 11;
--last 10
open V_CURSOR2 for
select *
from (select rownum,
a.auserid userid,
a.first_logout_time,
a.bonlinetime current_onlinetime,
a.conlinetime last_onlinetime,
a.donlinetime last_last_onlinetime
from account_status_day_temp a
order by bonlinetime asc)
where rownum < 11;
top10 := V_CURSOR1;
last10 := V_CURSOR2;
--total
select
valid_user_conti_act
, valid_user_back
, valid_user_conti_act_lost
, valid_user_active_lost
, valid_user_add_lost
, valid_user_back_lost
into
v_valid_user_conti_act1
, v_valid_user_back1
, v_valid_user_conti_act_lost1
, v_valid_user_active_lost1
, v_valid_user_add_lost1
, v_valid_user_back_lost1
from
(select count(case when buserid is not null and cuserid is not null then 1 else null end) valid_user_conti_act
, count(case when cuserid is null and buserid is not null and first_logout_time < v_ttime-1 then 1 else null end)
, count(case when cuserid is not null and buserid is null then 1 else null end) valid_user_active_lost
, count(case when duserid is not null and cuserid is not null and buserid is null then 1 else null end) valid_user_conti_act_lost
, count(case when duserid is null and cuserid is not null and first_logout_time < v_ttime-1 and buserid is null then 1 else null end) valid_user_back_lost
, count(case when buserid is null and first_logout_time >= v_ttime-1 and first_logout_time < v_ttime then 1 else null end) valid_user_add_lost from account_status_day_temp);
var1 := v_valid_user_conti_act1;
var2 := v_valid_user_back1;
var3 := v_valid_user_conti_act_lost1;
var4 := v_valid_user_active_lost1;
var5 := v_valid_user_add_lost1;
var6 := v_valid_user_back_lost1;
END sp_query_user_status_day;
END CURSPKG;
该存储过程是为日报表服务的,主要计算用户当期和历史时期的比较情况,其中包括明细数据前十名和后十名,用户新增与流失统计等。
第一部分(for temp table 部分):根据用户明细和状态表过滤汇总数据,按用户统计本期、上期、上上期的情况;中间结果存入临时表(避免重复计算),供后续计算使用。
第二部分(top10 部分):根据第一部分的计算结果排序,取前十名,结果以游标返回;
第三部分(last 10 部分):与前项类似,倒序排序,获得最后十名,结果以游标返回;
第四部分(total 部分):根据第一部分计算结果完成对各项综合统计指标计算,结果以六个输出参数返回。
该存储过程综合考虑了报表工具计算能力不足的因素,将尽量多的计算都放到存储过程中完成,这点是值得肯定的。但其中使用了大量的复杂 sql,以及多结果集的输出方式(游标),这又增加了编程难度。
如果用润乾报表来实现这个需求,首先要编写以下集算器脚本:
A | |
---|---|
1 | =connect(“ora”) |
2 | =A1.query(“select userid,first_logout_time,standard_7d_time from t_dw_zx_valid_account where standard_7d_time is not null”) |
3 | =A1.query(“select userid, sum(onlinetime) onlinetime, max(account) account from t_dw_zx_account_status_day where to_char(logtime,‘yyyy-MM-dd’)=”+"‘“+string(data_date)+”’ “+” group by userid having max(account) is not null") |
4 | =A1.query(“select userid, sum(onlinetime) onlinetime, max(account) account from t_dw_zx_account_status_day where to_char(logtime,‘yyyy-MM-dd’)=”+"‘“+string(data_date-1)+”’ “+” group by userid having max(account) is not null") |
5 | =A1.query(“select userid, sum(onlinetime) onlinetime, max(account) account from t_dw_zx_account_status_day where to_char(logtime,‘yyyy-MM-dd’)=”+"‘“+string(data_date-2)+”’ “+” group by userid having max(account) is not null") |
6 | =A1.query(“select userid,onlinetime,account,logtime from t_dw_zx_account_status_day where to_char(logtime,‘yyyy-MM-dd’)=”+“’”+string(data_date-2)+“’”) |
7 | =join@1(A2:a,USERID;A3:b,USERID;A4:c,USERID;A5:d,USERID) |
8 | =A7.new(#:no,a.USERID:userid,a.FIRST_LOGOUT_TIME:first_logout_time,b.ONLINETIME:current_time,c.ONLINETIME:last_time,c.ONLINETIME:last_last_time) |
9 | =A8.to(10) |
10 | =A8.to(A7.len()-10+1,) |
11 | =A6.count@b(d!=null && c!=null) |
12 | =A6.count@b(b==null && c!=null && d!=null) |
13 | =A6.count@b(a.FIRST_LOGOUT_TIME>data_date-1 && a.FIRST_LOGOUT_TIME<data_date) |
14 | =A6.count@b(b==null && a.FIRST_LOGOUT_TIME>data_date-1 && a.FIRST_LOGOUT_TIME<data_date) |
15 | =A6.count@b(d==null && c!=null && a.FIRST_LOGOUT_TIME<data_date-1) |
16 | =A6.count@b(b==null && c!=null && d==null && a.FIRST_LOGOUT_TIME<data_date-1) |
17 | =new(A11:num1,A12:num2,A13:num3,A14:num4,A15:num5,A16:num6) |
18 | >A1.close() |
19 | result A9,A10,A17 |
代码说明:
A1:连接配置好的 oracle 数据库。
A2-A6:从数据库按照条件和分组汇总、取数。其中的 A3、A4、A5 的 group 虽然也可以放到集算脚本中实现,但脚本中还是利用了 sql 的 group,这里的原则是简单运算尽量还是让数据库去做,这样可以让取出数据量变少,从而节省 JDBC 的传输时间;而对于复杂的过程性计算才适合放到集算脚本中做,从而发挥数据库和集算脚本各自的优势。
A7:将以上结果集进行关联。
A8:根据 A7 建立新序表,用于读取前后十名记录。
A9-A10:通过序号分别取前后十名记录。
A11-A17:计算汇总值。
A19:将前十名、后十名记录以及汇总值分别以不同结果集返回给润乾报表。
集算器脚本编写之后,保存为 test.dfx,在润乾报表中新增集算器数据集进行调用:
润乾报表接收集算器脚本返回的三个结果集,其中”test.dfx”为集算器脚本名称。
下一步,要按照需求绘制报表模板文件,即可完成报表设计:
posted on 2019-05-28 10:29 IBelieve002 阅读(397) 评论(0) 编辑 收藏 举报