BI 前端实践 3:较大数据量的 SQL 多维分析

实践目标

         体验较大计算量和较大结果集SQL在多维分析中的响应表现,用缓存方式提高响应速度;多维分析的报表中展示大量明细数据时,用大报表技术避免耗用过多内存、甚至内存溢出。

大计算量SQL做多维分析

下面这个SQL查询出平均工资最高的五万员工,需要大表employees30万条)、salaries284万条)关联、做分组汇总、最后按平均工资排序:

1png

         页面在60秒之后才显示出结果:

2png

         把性别拖到左表头上,统计这五万人中男、女性员工各有多少,这个动作仍然需要等待60秒才会显示结果,原因是每次分析动作,都会基于原始SQL拼一个更大的嵌套SQL去数据库执行,原始SQL慢就决定了每个分析动作都会慢。

3png

对大计算量SQL结果缓存,避免频繁执行

         把上面这个大计算量的SQL,换成Tablib属性方式设置,就会对SQL结果集进行缓存,第一次展示页面时,因为要去数据库查询仍然会慢,但之后再做各种多维分析动作,就立即响应,达到了对SQL结果集缓存、复用的效果:

         ..

 

对大结果集SQL缓存

         尝试对284万条数据的salaries整表数据做缓存方式的多维分析:

..

         成功缓存后,拖拽字段做多维分析动作时,每个动作还是要等十多秒,在WEB-INF\files\data目录下能看到缓存的99M大小的txt文件,十多秒的时间主要就耗在加载这个txt文件上:

..

用二进制代替txt格式缓存

         JS变量guideConf.dataFileType=”binary”指定以润乾二进制文件缓存:

..

         能看到缓存文件变成了.btx,文件大小也从99M缩小到48M了,而再进行多维分析时,响应速度也从十几秒变成了1秒,由此看出,btx格式文件虽然大小只缩小了1倍,但加载速度却提高了十倍。

..

 

 

用大报表展示大结果集明细

         salaries表的284万条数据,在多维分析时如果展示成明细,会因为生成的报表太大,报错无法计算:

5png

         这种情况设置成用大报表进行展示,session.setAttribute(“isBig”,1)时,服务器会用游标方式加载数据,同时要用JS变量guideConf.reportPage把显示报表的页面设置成专门的大报表JSPshowOlapBigReport.jsp:

..

         大报表会比较及时的显示出结果,头部的加载条数和页数会根据加载进度自动持续增加:

4png