您的位置：首页 > 报表 > 中间表方案解决大数据量报表性能问题

中间表方案解决大数据量报表性能问题

需求背景

*****项目中有多个报表涉及到对商品销量的统计，其数据来源于pos销售明细表，该表会根据多个门店的商品销售情况实时的插入数据，目前该表数据量已达千万数据量，而且每天还以几万数据量在增加。报表中比较常见统计指标是报表的日销售，几日销售量，指定日期段销售量，通过遍历明细表（pos销售明细表），分组汇总的方法获取销售量统计值，使得报表展现效率非常慢。

解决方案

经分析得出，影响报表性能和展现效率的主要因素在于报表查询基于一张数据量非常大明细表，该表为明细表，统计pos销售的每一笔商品销售记录，目前数据量上千万。为解决大数据量的报表性能问题，考虑使用中间表进行预处理。通过设计中间表，统计每日商品销售汇总数据。每天凌晨运行程序处理截止到昨天的数据，让用户在白天查询的是已经生成好的截止到昨天的统计数据，通过中间表和定时调度解决性能瓶颈。

明细表数据结构：
明细表：pos_item_of, pos_mas_of
其中pos_mas_of记录pos销售单的基本信息，如pos销售时间（create_date）,pos销售单号（pos_no），pos销售单类型（mas_code）等；pos_item_of记录每一笔pos销售单的商品销售明细信息，如商品编码（stk_c）,销售门店（wh_c）,销售数量（UOM_QTY）等。

中间表设计：
因为报表的统计指标通常是日销售量，几日销售量，指定日期段的销售量，故以日为单位统计每种商品的销售量情况。设计表如下：
表名BI_SPRXS

列名	类型	主键	为空	说明
XL_ID	NUMBER	Y（自增）	N	Id
XL_SPBM	NVARCHAR2(100)	N	Y	商品编码
XL_XL	NUMBER	N	Y	商品销售量
XL_XSE	NUMBER	N	Y	商品销售额
XL_CB	NUMBER	N	Y	商品销售成本
XL_CREATE_DATE	DATE	N	Y	销售日期

中间表设计完成后需要统计汇总明细表数据并填充中间表。另外因为对销量的查询和筛选通常是基于商品编码和销售日期的，故可建立基于它们的索引。中间表建立完成后，关于商品销量的统计可以不再依赖明细表，通过查询中间表获得。

性能差别比较：

1)数据量对比：
明细表数据量：目前有数据18202118，每天以数万条的数据量在增加。
中间表数据量：目前有数据2068641，每天以数千条的数据量在增加。
中间表使得数据量降低了近10倍，这样在执行sql检索时能够明细提升sql的执行速度。

2)Sql执行速度对比：

以取所有商品5日销售额为例说明基于明细表和基于中间表查询带来的速度差异。
基于明细表的执行以下sql，可以获得所有商品的5日销量。
select a.stk_c, nvl(b.xl,0) xl from stk_mas a
left join(
select t1.stk_c,sum(t1.UOM_QTY * DECODE(t2.MAS_CODE, ‘POSOFF’, 1, -1) ) as xl
from pos_item_of t1, pos_mas_of t2 where t1.pos_no=t2.pos_no and t1.mas_code=t2.mas_code and t1.mas_no = t2.mas_no and t2.create_date<trunc(sysdate) and t2.create_date>trunc(sysdate)-5 group by t1.stk_c ) b
on a.stk_c=b.stk_c

sql执行用时：00:25:12

基于中间表的执行以下sql，也可以获得所有商品的5日销量。
select a.stk_c, nvl(b.xl,0) xl from stk_mas a
left join(
select t.xl_spbm, sum(t.xl_xl) as xl from bi_sprxs t where t.xl_create_date<trunc(sysdate) and t.xl_create_date>=trunc(sysdate)-5 group by t.xl_spbm) b
on a.stk_c=b.xl_spbm

sql执行用时：00:00:08

比较基于两个表sql查询可以得出，基于中间表完成5日销售统计比基于明细表完成5日销量统计要快，对资源的占用也比较少。因为要列出所有商品的销售额(无论是否有销售额)所以与stk_mas表的左连接是必须的。我们只比较后面的部分，第一种方式先要做两个表的内连接，然后进行数据筛选，因为pos_item_of表数据量非常大，约为中间表的10倍，连接操作耗时耗资源。后一种方式不需要做两个表的内连接，中间表数据量相对较少，并且建立了XL_CREATE_DATE上的索引，所以sql执行效率高。通过sql执行对比可证明中间表解决方案可以提升报表查询速度。

3)报表展现速度对比：
以年度销售成本明细汇总表为例，比较基于明细表和中间表完成的报表其展现速度的差异。
两张报表最终展现样式一致，如下图

基于明细表完成该报表展现，用时2分16秒。基于中间表完成报表演示，仅用时30秒。

问题扩展延伸

1. 中间表建立规则

采用商品日销量中间表，能够很好的解决某张报表或者某类涉及到商品日销售指标查询的报表的性能问题。但是统计指标发生变化时，比如要统计各个门店的日销售情况，这张中间表就无能为力了。经验证中间表方案能够提升报表性能，可采用类似方式建立其他的中间表，避免统计指标基于大数量业务数据库表查询统计。除了对pos销售明细进行商品日销售额统计，在其他报表中还可能会用到其他统计指标。可以把pos销售额作为量度，将销售时间（年，月，日三个层次），商品（商品类别，商品条目两个层次），门店作为维度，根据统计指标使用的频度去建立中间表。
例：建立门店月销售中间表，表结构如下：

列名	类型	主键	为空	说明
XL_ID	NUMBER	Y（自增）	N	Id
XL_MD	NVARCHAR2(100)	N	Y	门店
XL_XL	NUMBER	N	Y	门店销售量
XL_XSE	NUMBER	N	Y	门店销售额
XL_CB	NUMBER	N	Y	门店销售成本
XL_MONTH	NVARCHAR2(10)	N	Y	销售月份（yyyy-MM）

每月月初执行脚本，统计明细表中截止到上月底的数据，填充该表。
例：建立商品类别月销售中间表，表结构如下：

列名	类型	主键	为空	说明
XL_ID	NUMBER	Y（自增）	N	Id
XL_SPLB	NVARCHAR2(10)	N	Y	商品类别
XL_XL	NUMBER	N	Y	类别销售量
XL_XSE	NUMBER	N	Y	类别销售额
XL_CB	NUMBER	N	Y	类别销售成本
XL_MONTH	NVARCHAR2(10)	N	Y	销售月份（yyyy-MM）

每月月初执行脚本，统计明细表中截止到上月底的数据，填充该表。
例：建立商品门店日销售中间表，该表统计商品在各门店的每日销售情况。表结构如下：

列名	类型	主键	为空	说明
XL_ID	NUMBER	Y（自增）	N	Id
XL_SPBM	NVARCHAR2(10)	N	Y	商品
XL_MD	NVARCHAR2(50)	N	Y	门店
XL_XL	NUMBER	N	Y	销售量
XL_XSE	NUMBER	N	Y	销售额
XL_CB	NUMBER	N	Y	销售成本
XL_CREATE_DATE	DATE	N	Y	销售时间

每日凌晨执行脚本，统计明细表中截止到昨日的数据，填充该表。
这里举例说明了中间表建立的基本思路和方法，但并不是说需要针对每个维度每个层次都建立和维护这样的中间表，比如根据商品类别月销售中间表很容易就能统计出商品类别的年销售额，就没有必要去建立商品类别年销售中间表了。

2.历史数据管理及归档

    随着时间推移，日销售数据的积累会使中间表数据量上升，大量历史数据的存在会影响查询速度，再次造成报表性能瓶颈。控制中间表的数据量，定期归档历史数据至分区表可以规避该问题。
比如以年为单位进行数据归档，保证中间表的数据量不会一直上升。当年内的商品销售数据可以较快的查询得出。归档的数据表可以视为“仓库表”，数据按照时间区间进行分区，如2008年的数据和2009年的数据分别存放在不同的分区1和分区2中。
     利用数据分区技术可以改善查询性能。大数据量表的数据量一般来说是跟时间成正比的，时间越久,数据量越大。在设计阶段首先要考虑这些大表的时效性。在一定的时间区间,数据的访问频度比较大，超过这个区间，数据的访问频度极小。超过这个时间区间的数据可以认为是历史数据，数据访问的可能性不大。通过分区可以消除很大的数据区间，不用访问这些数据。如果我们要查询历史数据中2009年的商品销售情况就可以不必考虑分区1，加快查询处理速度。对于长时间不使用的历史数据，如十年前的数据，可考虑使得该分区离线，或存放至备份库中。
    从中间数据表到归档分区表的数据提取是一个周期加载的过程：
    A．首先是去除归档分区表的老数据，删除最旧的销售年度数据，将其删除或者备份到其他库中。
    建立一个空表与“仓库分区表“中最老的分区交换，这时候分区表中最老的数据被老化删除。空表交换后慢成满表里面存储的是最老分区的数据，可以将其导出实现归档。
    B．再次是新数据加载关联。
    在“仓库分区表”增加新的分区，将中间表中去年的数据填充得到一个满表，再将新创建的分区和满表交换。这样去年的数据就会归档变成“仓库分区表”中的一个分区。
    通过以上的方式可以有效控制中间表的数量级，并有效保存历史数据，并保证历史数据的查询性能。

实现步骤：
1. 建立中间表—商品日销售表（BI_SPRXS），建表语句见文章最后附录。
2. 根据已有pos销售明细表数据（历史数据）进行销售汇总统计，并填充至中间表（BI_SPRXS）。执行sql见文章最后附录。
3. pos销售明细表到中间表的数据定时抽取和填充
实现每日零时把昨日的pos销售记录，统计汇总，并抽取填充至中间表中。可通过以下三种方式实现。

方式一：利用oracle的DBMS_JOB来调度作业
DBMS_JOB是向用户提供的一个Oracle PL/SQL包。可以向作业队列提交一个作业，并在指定时间运行这个作业。
a. 建立sql过程，该过程完成任务，统计昨日商品的日销售量，销售额，销售成本，并把统计结果插入到中间表BI_SPRXS。
create or replace procedure insertSPRXS as
cursor xl_cur is
SELECT 0,t1.stk_c, sum(t1.UOM_QTY * DECODE(t2.MAS_CODE, ‘POSOFF’, 1, -1)) as XL,sum(t1.UOM_QTY * DECODE(t2.MAS_CODE, ‘POSOFF’, 1, -1)*t1.sales_price) as XSE,sum(t1.UOM_QTY * DECODE(t2.MAS_CODE, ‘POSOFF’, 1, -1)*t3.std_cost) as CHB, to_date(t2.create_date,’DD-MON-YY’) as create_date from POS_ITEM_OF t1,Pos_Mas_Of t2,stk_mas t3 where t1.stk_c=t3.stk_c and t1.pos_no=t2.pos_no and t1.mas_code=t2.mas_code and t1.mas_no = t2.mas_no and t2.create_date like sysdate-1 group by t1.stk_c,to_date(t2.create_date,’DD-MON-YY’);
xl_record bi_sprxs%rowtype;
begin
open xl_cur;
LOOP
     fetch xl_cur into xl_record;
     exit when xl_cur%notfound;
     insert into bi_sprxs(xl_spbm,xl_xl,XL_XSE,XL_CB,XL_CREATE_DATE) values (xl_record.xl_spbm,xl_record.xl_xl,xl_record.xl_xse,xl_record.xl_cb,xl_record.xl_create_date);
END LOOP;
commit;
end ;
b. 提交作业，从明日零时开始以日为间隔执行insertSPRXS。
variable v_job number
begin
DBMS_JOB.submit(:v_job,’insertSPRXS;’, trunc(sysdate+1),’sysdate+2′);
commit;
end;
通过DBA_JOBS视图可查看队列中作业
select job,next_date,what,interval from DBA_JOBS;
JOB   NEXT_DATE       WHAT INTERVAL
———————————————————————————————————-
2    22-FEB-11   EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS(); sysdate + 1/(24*60)
23     23-FEB-11       insertSPRXS;                               sysdate+1
从上面的查询结果得出，insertSPRXS将会在2011年2月23日凌晨被调度执行，并且执行的时间间隔是一天。

方式二：填报表+调度器实现定时抽取填充
a.设计填报表，该填报表为商品日销售统计表，取数源于pos销售明细表。更新属性为更新中间数据表。
填报表设计如下：
数据集定义：
SELECT t1.stk_c, sum(t1.UOM_QTY * DECODE(t2.MAS_CODE, ‘POSOFF’, 1, -1)) as XL,sum(t1.UOM_QTY * DECODE(t2.MAS_CODE, ‘POSOFF’, 1, -1)*t1.sales_price) as XSE,sum(t1.UOM_QTY * DECODE(t2.MAS_CODE, ‘POSOFF’, 1, -1)*t3.std_cost) as CHB, to_date(t1.create_date,’DD-MON-YY’) from POS_ITEM_OF t1,Pos_Mas_Of t2,stk_mas t3 where t1.stk_c=t3.stk_c and t1.pos_no=t2.pos_no and t1.mas_code=t2.mas_code and t1.mas_no = t2.mas_no and t1.create_date like sysdate-1 group by t1.stk_c,to_date(t1.create_date,’DD-MON-YY’);
报表模板设计：

更新属性定义：

b.调度器建立每日报表任务。

注意：这里不需要将报表执行结果进行输出或推送，设置调度对象文件即可。

方式三：集算文件+调度器实现定时抽取填充
a．集算器文件

b. 调度器建立每日集算器任务

通过以上三种方式均可达到类ETL的效果，完成数据的定时抽取填充。巧妙组合应用润乾的产品，通过建立中间表，定时调度的方法对提升报表性能有很大帮助。

小结
中间表解决方案可以帮助用户有效合理规划数据，使得报表访问脱离业务库中的大数据量明细表，既能有效避免报表访问对业务数据库造成的负担，又能有效解决大数据量报表性能问题。

附录：
1.中间表—商品日销售表BI_SPRXS，建表语句
– Create table
create table BI_SPRXS
(
XL_ID          NUMBER not null,
XL_SPBM        NVARCHAR2(100),
XL_XL          NUMBER,
XL_XSE         NUMBER,
XL_CB          NUMBER,
XL_CREATE_DATE DATE
)
tablespace WFBI
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);
– Create/Recreate primary, unique and foreign key constraints
alter table BI_SPRXS
add constraint SPRXL_PK_NO primary key (XL_ID)
using index
tablespace WFBI
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);
通过序列和触发器，实现主键自增：
create sequence sprxs_SEQ
minvalue 1        maxvalue 999999999999999999999999999
start with 1        increment by 1
cache 20
order;

CREATE OR REPLACE TRIGGER “SPRXS_ID_TRIGGER”
BEFORE INSERT ON wanfang.bi_sprxs FOR EACH ROW
declare next_checkup_no number;
begin select sprxs_SEQ.nextval into next_checkup_no from dual;
:NEW.XL_ID := next_checkup_no;
end;

2.明细表历史数据到中间表数据填充
declare
cursor xl_cur is
SELECT 0,t1.stk_c, sum(t1.UOM_QTY * DECODE(t2.MAS_CODE, ‘POSOFF’, 1, -1)) as XL,sum(t1.UOM_QTY * DECODE(t2.MAS_CODE, ‘POSOFF’, 1, -1)*t1.sales_price) as XSE,sum(t1.UOM_QTY * DECODE(t2.MAS_CODE, ‘POSOFF’, 1, -1)*t3.std_cost) as CHB, to_date(t1.create_date,’DD-MON-YY’) as create_date from POS_ITEM_OF t1,Pos_Mas_Of t2,stk_mas t3 where t1.stk_c=t3.stk_c and t1.pos_no=t2.pos_no and t1.mas_code=t2.mas_code and t1.mas_no = t2.mas_no group by t1.stk_c,to_date(t1.create_date,’DD-MON-YY’);

xl_record bi_sprxs%rowtype;

begin
open xl_cur;
LOOP
     fetch xl_cur into xl_record;
     exit when xl_cur%notfound;
     insert into bi_sprxs(xl_spbm,xl_xl,XL_XSE,XL_CB,XL_CREATE_DATE) values (xl_record.xl_spbm,xl_record.xl_xl,xl_record.xl_xse,xl_record.xl_cb,xl_record.xl_create_date);
END LOOP;
commit;
end;

本文标签：数据平台报表性能

文章信息

类别：报表
时间：2012年08月14日

申请试用

中间表方案解决大数据量报表性能问题

文章信息

更多文章

知识库

推荐内容