最近在搞一个ERP系统里面的报表,这个报表中的大列是统计性的,比如出货率,采购百分比等等,这就意味着不能简单的连接某几个表,取几个基础表的数据就能获得报表的数据,而是对几个表进行连接,然后再计算,或者更不幸的,经过一轮计算后,得出的结果再去另外的表的数据进行计算。而我的报表中,需要一轮计算生成的字段大概占40%,需要连接,计算然后再计算的,大概有50%,而能直接获得的,只有两三个列。
起初,我是直接写查询语句,企图通过一个查询就搞掂。但由于逻辑太复杂,语句中充斥着大量的case when语句不说,里面还有N层的子查询,非常难看懂。而且有些列就根本无法得到。
最初查询的时候,大概10-20秒钟。其实对于报表这类统计性非常强的数据来说,10来20秒算是可以容忍,毕竟报表不是经常用,导出的时候就忍忍呗。但是不久后又一个问题出现了:服务器内存消耗严重。估计是查报表的时候,临时表太多了,逐渐出现了超时问题。数据量少的话,使用临时表存放并没有什么不妥,但是如果数据量一多,内存占用就飙升了,如果导致了sql server太多的页交换的话,还是得不偿失的。
很一个多两个星期的时间里,我都还是主要通过寻求增加和优化索引,优化sql语句的方式来进行优化,但无奈,某些业务需求导致某些计算无法使用索引(主要是二次计算太多,我曾经甚至想过给临时表添加索引)。
最后,实时的查询并不能解决问题的情况下,我选择另一种方式:使用预先计算的方式来在空闲的时间做好报表的统计工作,然后放到一个表中,如果领导需要看报表,系统就去查询那个表,而不是实时的去查询基础表计算数据。因为正如我所说,报表中大多是统计性的数据,而且对于实时性要求不是很高,完全可以在服务器空闲先“偷偷”完成报表生成,
在供人查询使用。我具体的是:建立一个作业,每天凌晨1点和中午13点的时候运行报表统计工作,存放在一个名叫“预统计表”的表中,并且这个表的结构和报表结构完全一下,尽量达到“select * from table”就可以得到报表的地步。然后通过分页,最终的查询速度不是一般的高。
原本,我是不太喜欢这样的方式:既不那么美观,又增加了大量的冗余数据。而且如果一开始就抱着这种思路来提高性能,往往会降低数据库设计的范式,这无异于是一种“旁门左道”。但是,在这期间,我google了不少博客,不少人都赞同一点:严格遵循范式的数据库设计,并不一定就是好的设计。往往降低范式,适当增加冗余度,提高查询性能,也是一种除sql server本身提供的优化策略之外一种比较好的优化手段。