問題描述
項目里有些報表出來的速度特別慢，儘管對潤乾報表和Oracle資料庫做了很多優化，效果還是不理想，這些報表普遍數據量比較大，涉及到的資料庫表多（幾十張）、表間關聯頻繁（還有自連接），報表裡也有多個匯總、比值等計算。
以其中一個明細報表為例，它的SQL如下：
(select *
from (select syb.org_abbn as syb,
max(xmb.org_abbn) as xmb,
sub.org_subjection_id as sub_id,
oi.org_abbn as org_abb,
rm.rec_notice_org_id,
rm.synergic_team as xz_team,
xzdw.coding_name as xz_org,
l.requisition_cd as req_cd,
l.requisition_id as req_id,
l.note as req_note,
nvl(decode(l.ops_content6,
2000200012,
'否',
2000200011,
'是'),
'') as sflj,
--太長了，省略大部分select子句
fromlcr l
left join lcrrm on rm.requisition_id =
l.master_bill_id
andrm.table_type = '0'
andnvl(rm.bsflag, 0) != 1
left join cos sub on l.org_id = sub.org_id
andnvl(sub.bsflag, 0) != 1
left join coioi on oi.org_id = sub.org_id
andnvl(oi.bsflag, 0) != 1
--太長了，省略大部分jion
wherel.table_type = '1'
andl.requisition_state = '0101020304'
andnvl(l.bsflag, 0) != 1
andto_char(l.back_date, 'yyyy-MM-dd') between '2012-01-01' and
'2012-04-25'
group by l.requisition_id,
l.note,
l.requisition_type,
sub.org_subjection_id,
syb.org_abbreviation,
rm.rec_notice_org_id,
oi.org_abbreviation,
--太長了，省略大部分group by 欄位
) a--主查詢a
LEFT JOIN crviewve--視圖ve
ON ve.requisition_id = a.req_id
這個sql里關聯的表很多，嵌套多層子查詢，最後又與一個視圖進行關聯（視圖也很複雜）。該報表查詢4個月的數據，計算時間為6分42秒，太慢了遠遠達不到用戶要求。
我們對這個報表做過幾次優化，但sql複雜度較高，基本沒有優化空間，而且由於是實時查詢，所以無法採用事先計算建立中間表的方法。後對這個報表進行監控發現，數據集SQL執行需要5分鐘，計算展現需要1分多鐘，數據集SQL慢的原因是其中兩個子查詢（主查詢a和視圖ve）做join的效率極低。
所以優化思路定為——優化數據集取數，改善SQL的join效率低的情況，順便優化報表計算展現。
解決過程
我們是用集算器（報錶廠家的東西）解決這個問題：
1、拆分原報表數據集SQL
分別把兩個子查詢sql寫到集算器，並用switch完成關聯（Switch是它的函數，比較新穎，能將外鍵指向的記錄直接當成本記錄的屬性，也支持join，視不同情況使用）。
2、消除報表格間運算
將原報表模板中的比值和匯總值這些全部移到集算器中做，少了單元格遍歷，報表計算速度也能提高。
3、將結果集一次返回給報表
完成所有數據準備後，把計算結果一次性返回給報表工具，報表接收到數據源後直接進行展現（不再做其他如格間計算類的影響效率的計算）。
解決效果
該報表展現時間從原來的6分42秒銳降到57秒，優化效果非常明顯，超出了用戶預期。其他有類似問題的報表也準備採用這個思路。
問題小結
主查詢a和視圖ve分別在Oracle跑時只需要10到40秒，但二者做jion卻需要好幾分鐘，原因在於Oracle在完全自動制定查詢計劃的時候，並不是每次都能找到合理的方法（人工干預比較費勁）。集算器能提升性能是因為ve是a的維表，可以用特別的switch方法。由人來決定複雜查詢的路徑，結合Oracle的基礎查詢語句，速度就顯著提升了。
留言列表
