author:skate
time:2010/05/21
做事变通
昨天同事找我,说bi系统的有一个递归树形查询的sql非常慢,已经让使用人员无法忍受
sql如下:
SELECT SYS_CONNECT_BY_PATH(BB.FULL_NAME, '/') PATH, BB.*
FROM (WITH AA AS (SELECT TRUNC(W.END_TIME, 'mm') END_TIME,
O.MERCHANT_ID,
SUM(O.MONEY) MONEY,
SUM(O.BONUS) BOUNS
FROM BI_ORDERJOINTOGETHER O, BI_WARE W
WHERE O.WARE_ID = W.ID
AND O.STATE = 4
GROUP BY TRUNC(W.END_TIME, 'mm'), O.MERCHANT_ID)
SELECT OG.ORG_ID,
OG.FULL_NAME,
OG.PARENT_ID,
TO_CHAR(AA.END_TIME, 'yyyy-mm'),
NVL(AA.MONEY, 0) MONEY,
NVL(AA.BOUNS, 0)
FROM BI_ORGANIZATION OG, AA
WHERE AA.MERCHANT_ID(+) = OG.ORG_ID) BB
START WITH BB.PARENT_ID IS NULL
CONNECT BY PRIOR BB.ORG_ID = BB.PARENT_ID
在分析这个sql之后,发现sql慢的原因是start with的树形递归和函数SYS_CONNECT_BY_PATH,其实主要是
start with,因为他每一level都要遍历所有的节点所以速度非常慢,想半天没有想到什么好的方法来优化
这个sql,但这个还是业务需要的,怎么办呢?
我又从头理顺下了思路,这个是一个bi系统,为业务部门和决策者提供服务,bi系统里的数据比生产数据晚
一天,目前这个sql很慢,又很难优化,那如何为前端用户提供优质的服务呢?为什么不把这个sql的数据事先
准备好,然后平时就直接查询准备好的数据,这样就省去计算排序的时间了,就可以大大提高性能。经过测试
发现,速度发生几个数量级的变化.
通过这个case,可以总结一个经验,条条大路通罗马,有的时候退一步思考会得到意想不到的效果,做人做事都是
一样的,不要钻死角
-----end----