最佳化SQL執行路徑提高報表效能，sql執行路徑報表

最佳化SQL執行路徑提高報表效能，sql執行路徑報表

         報表出現效能問題需要對資料來源計算進行最佳化時，執行路徑難以確定從而被幹預是阻礙報表最佳化的難題之一。由於資料庫執行路徑對開發人員不透明，報表最佳化需要指定執行路徑時，程式員會很難甚至無法幹預。而一般報表工具不具備強計算能力，大部分計算仍然要依靠資料庫進行，這就導致很多報表最佳化效果不理想。

         不同於一般報表工具，潤乾集算報表內建了專門用於資料計算的集算引擎，開發人員可以通過編寫集算指令碼完成報表資料來源準備。與資料庫執行SQL路徑不可控相比，集算指令碼的執行過程是可控的，開發人員可根據實際情況編寫或更改計算執行過程，完成報表最佳化工作。

        集算引擎中內建了豐富的用於結構化資料計算的類庫，如串連操作在集算引擎中就包括普通串連的join()、按序號對應串連的pjoin()、叉乘串連的xjoin()，針對維表和事實表串連提供了更加高效的串連方式switch()，這樣就為開發人員在最佳化報表時提供了多種選擇。此外，由於集算報表支援分步編寫，開發人員可以自由決定計算順序，先算什麼後算什麼，甚至對一個複雜計算進行合理拆分和整合，適合需要改變計算順序來進行最佳化的情境。

         下面是一個針對報表資料來源SQL最佳化的執行個體過程和效果，使用者在使用集算報表時可以參考。

         該報表是一張明細表，資料量較大，涉及到的資料庫表有幾十個之多，資料庫表間關聯頻繁（包括自串連的情況）；報表中包含多個格間計算運算式（比值和匯總值）。

         其中較複雜的資料集SQL（近400行）如下：

select t.*

 from (select *

         from (select syb.org_abbreviation as syb,

                      max(xmb.org_abbreviation) as xmb,

                  --省略多個串連、判斷、匯總語句

                  left join losrrr losr onlosr.requisition_id =

                  l.requisition_id

                 where l.table_type = '1'

                   and l.requisition_state ='0100500005000000006'

                   and nvl(l.bsflag, 0) != 1

                 group by l.requisition_id,

                          l.note,

                          --省略多個分組欄位

                          losr.standby_param3,

                          losr.standby_param6

                ) a

         LEFT JOIN crview ve  --視圖

           ON ve.requisition_id = a.req_id

         仔細看這個sql會發現，首先關聯的表比較多，其中包括很多自關聯的情況；嵌套了很多子查詢；最後又與一個視圖進行關聯（視圖複雜程度與上述SQL類似）。該報表查詢4個月資料展現時間6分42秒，遠遠達不到使用者要求。由於SQL比較複雜，資料庫執行路徑難以控制，所以很難在SQL的層面再進行最佳化。

 

         使用集算報表進行最佳化的過程如下：

1、編寫集算指令碼

         首先，拆分原報表資料集SQL。這個SQL慢的原因在於兩個子查詢之間的join，那麼分別把兩個子查詢sql寫到集算指令碼中執行，並且在集算指令碼中使用switch完成關聯。

         其次，消除報表格間運算。將原報表範本中的格間計算（比值和匯總值）的內容全部移到集算指令碼中完成，這樣報表少了格子遍曆的過程，可以提升效能。

         最後，將結果集一次返回給報表。通過集算指令碼完成了所有資料準備的工作後，將結果一次性返回給報表工具，報表接收到資料來源後直接進行展現（不再做其他如格間計算類的影響效率的計算）。

         完整的集算指令碼如下：

         2、在集算報表中調用上述集算指令碼，編輯報表運算式完成報表製作。

 

最佳化效果

         報表展現要經曆的兩步：1資料來源計算（執行資料集SQL）階段，2 報表計算並展現階段，報表最佳化前後各階段與總時間對比見下表：

         通過上面的過程，該報表展現時間從原來的6分42秒，銳降到了57秒，不到1分鐘的時間，達到了使用者預期的目標。

 

         使用集算報表進行報表最佳化適用於從資料庫取出資料量不是很大的情境（由於Oracle JDBC速度非常慢），通過使用這種可控的過程最佳化手段迅速提升報表效能。