儲存過程效能低的解決方法之一

報表應用中實現資料來源計算經常會使用儲存過程，但同時也帶來多方面的問題。首先，儲存過程的包只提供一層分類，無法用樹形結構組織，容易造成程式碼管理混亂。而有些程式設計師更是直接在現場線上修改儲存過程，也不利於程式碼管理。其次，升級儲存過程時需要資料庫的寫許可權，容易對資料安全造成影響。另外，由於 SQL 固有的一些問題（資料無序、缺乏集合、無法引用、分步不徹底，等等），使得儲存過程的程式設計也比較困難。

很多情況下選擇儲存過程是為了提高效能，但實際效果並不盡如人意。這主要是因為報表資料的計算一般都比較複雜，很難用 SQL 直接完成，而是需要透過迴圈遍歷等程式碼邏輯來控制完成。在這種邏輯中，動輒進行遍歷的 SQL 語句往往會被多次執行，從而使得計算要比 SQL 還慢一個數量級，甚至有些語句的執行速度比外部的 Java 程式還要低。

除此之外，還有一個問題，儲存過程是儲存在資料庫內的，而報表工具的模板一般是檔案形式，兩者要一起配合工作才能完成報表，但這種分開儲存的方式實際上很容易導致版本不一致，從而增加管理難度。例如，某報表的模板可能已經被刪除了，但對應的儲存過程還未刪除。又或者資料庫開發者修改了儲存過程，卻沒有通知報表開發者。

為了避免使用儲存過程導致的上述弊端，可以採用潤乾報表及其內建的集算引擎來完成複雜的資料來源計算。兩種解決方案的系統結構的對比如下圖所示：

可以看到，潤乾報表的報表檔案（.rpx）和集算指令碼（.dfx）是獨立的檔案，因此可以使用檔案系統的樹形目錄來統一管理，而某些簡單指令碼還可以直接寫進 rpx。這樣，兩者很容易保持一致，方便管理，降低了維護成本。而對於升級過程，潤乾報表是透過替換這兩種檔案來完成的，也可以避免線上修改執行環境。同時，集算指令碼解決了前面所說的 SQL 的各種固有問題，程式設計思維更加自然，比儲存過程更加容易。

從效能角度來看，集算引擎提供了平行計算能力，可以充分發揮伺服器多 CPU 多核的效能，也可以連線集算伺服器叢集，實現多機並行，因此能夠在許多情況下獲得超過儲存過程的效能。

當然，從上圖中也可以看到，某些情況下儲存過程還是需要的。這是因為涉及資料量大的庫內運算時，用儲存過程會更快。事實上我們的目標不是完全替代儲存過程，而是利用潤乾報表儘量減少儲存過程的使用，提高綜合效率和效能。

下面，我們就透過具體的例子，來看一下潤乾報表是如何減少儲存過程的。

某網路平臺需要監測一定週期內的使用者狀況，為運營部門出具日報、週報、月報、年報等報表，每類報表都需要進行本期與上期、上上期資料的比較，涉及資料較為雜亂。這裡就以日報作為例子（月報年報只是統計週期不同）看一下。報表格式如下：

報表分為兩部分，上半部分為使用者明細資料（本期、上期、上上期線上時長均不為空的使用者），而由於使用者較多，報表只顯示按本期線上時長排序的前十名和後十名使用者；報表下半部分為本期資料與上期、上上期的比較結果（允許本期、上期、上上期線上時長為空）。

資料來自於兩個資料表：

create table T\_DW\_ZX\_ACCOUNT\_STATUS_DAY
(
  LOGTIME    DATE,--日誌時間
  USERID     NUMBER(12),--使用者號
  ACCOUNT    VARCHAR2(50),--賬號
  ONLINETIME NUMBER(8),--線上時間
  PAY        NUMBER(11),
  EXPEND     NUMBER(11),
  TOP_LEVEL  NUMBER(4)
);
create table T\_DW\_ZX\_VALID\_ACCOUNT
(
  USERID            NUMBER,--使用者編號
  FIRST\_LOGOUT\_TIME DATE,--第一次登出時間
  STANDARD\_7D\_TIME  DATE,
  STANDARD\_14D\_TIME DATE,
  ACCOUNT           VARCHAR2(50)
);

首先，看一下儲存過程的實現方式（為了說明方便，將其分成顏色不同的四部分）：

CREATE OR REPLACE PACKAGE BODY CURSPKG AS
  PROCEDURE sp_query_user_status_day(data_date IN varchar2,
                                     top10     OUT T_CURSOR,
                                     last10    in out t_cursor,
                                     var1      out number,
                                     var2      out number,
                                     var3      out number,
                                     var4      out number,
                                     var5      out number,
                                     var6      out number) IS
    V_CURSOR1 T_CURSOR; --top10
    V_CURSOR2 T_CURSOR; --last10
    V_CURSOR  T_CURSOR; --temp table
    v_ttime   date;
    temp_num  number;
    
    v_valid_user_conti_act1      number;          
    v_valid_user_back1           number;          
    v_valid_user_conti_act_lost1 number;           
    v_valid_user_active_lost1    number;          
    v_valid_user_add_lost1       number;           
    v_valid_user_back_lost1      number;          
  
  BEGIN
    v_ttime := to_date(data_date, 'yyyy-mm-dd');
  
    --for temp table
    select count(1) into temp_num from account_status_day_temp;
    if temp_num > 0 then
      delete from account_status_day_temp;    --delete first
    end if;
  
    insert into account_status_day_temp
      select *
        from (select v.userid, v.first_logout_time
                from t_dw_zx_valid_account v
               where v.standard_7d_time is not null) a,
             (select userid, sum(onlinetime) onlinetime, max(account)                                                                                                                               
                from t_dw_zx_account_status_day
               where logtime >= v_ttime
                 and logtime < v_ttime + 1
               group by userid
              having max(account) is not null) b,
             (select userid, sum(onlinetime) onlinetime, max(account)                                                                                                                               
                from t_dw_zx_account_status_day
               where logtime >= v_ttime - 1
                 and logtime < v_ttime
               group by userid
              having max(account) is not null) c,
             (select userid, sum(onlinetime) onlinetime, max(account)                                                                                                                           
                from t_dw_zx_account_status_day
               where logtime >= v_ttime - 1 - 1
                 and logtime < v_ttime - 1
               group by userid
              having max(account) is not null) d
       where a.userid = b.userid(+)
         and a.userid = c.userid(+);
 
     commit;
     
     --top 10
    open V_CURSOR1 for
      select *
        from (select rownum,
                     a.auserid userid,
                     a.first_logout_time,
                     a.bonlinetime current_onlinetime,
                     a.conlinetime last_onlinetime,
                     a.donlinetime last_last_onlinetime
                from account_status_day_temp a
               order by bonlinetime desc)
       where rownum < 11;
       
      --last 10
      open V_CURSOR2 for
      select *
        from (select rownum,
                     a.auserid userid,
                     a.first_logout_time,
                     a.bonlinetime current_onlinetime,
                     a.conlinetime last_onlinetime,
                     a.donlinetime last_last_onlinetime
                from account_status_day_temp a
               order by bonlinetime asc)
       where rownum < 11;
       
       top10 := V_CURSOR1;
       last10 := V_CURSOR2;
  
    --total
   select
            valid_user_conti_act        
          , valid_user_back             
          , valid_user_conti_act_lost   
          , valid_user_active_lost      
          , valid_user_add_lost         
          , valid_user_back_lost       
    into
            v_valid_user_conti_act1
          , v_valid_user_back1
          , v_valid_user_conti_act_lost1
          , v_valid_user_active_lost1
          , v_valid_user_add_lost1
          , v_valid_user_back_lost1
   from
   (select  count(case when buserid is not null and cuserid is not null then 1 else null end) valid_user_conti_act  
           , count(case when cuserid is null and buserid is not null and first_logout_time  <  v_ttime-1 then 1 else null end) 
           , count(case when cuserid is not null and buserid is null then 1 else null end)  valid_user_active_lost  
           , count(case when duserid is not null and cuserid is not null and buserid is null then 1 else null end) valid_user_conti_act_lost    
           , count(case when duserid is null and cuserid is not null and first_logout_time < v_ttime-1 and buserid is null then 1 else null end) valid_user_back_lost    
           , count(case when buserid is null and first_logout_time  >= v_ttime-1 and  first_logout_time < v_ttime then 1 else null end)  valid_user_add_lost
    from account_status_day_temp);
    
         var1 := v_valid_user_conti_act1;
         var2 := v_valid_user_back1;
         var3 := v_valid_user_conti_act_lost1;
         var4 := v_valid_user_active_lost1;
         var5 := v_valid_user_add_lost1;
         var6 := v_valid_user_back_lost1;
    
  END sp_query_user_status_day;
END CURSPKG;

該儲存過程是為日報表服務的，主要計算使用者當期和歷史時期的比較情況，其中包括明細資料前十名和後十名，使用者新增與流失統計等。

第一部分（for temp table 部分）：根據使用者明細和狀態表過濾彙總資料，按使用者統計本期、上期、上上期的情況；中間結果存入臨時表（避免重複計算），供後續計算使用。

第二部分（top10 部分）：根據第一部分的計算結果排序，取前十名，結果以遊標返回；

第三部分（last 10 部分）：與前項類似，倒序排序，獲得最後十名，結果以遊標返回；

第四部分（total 部分）：根據第一部分計算結果完成對各項綜合統計指標計算，結果以六個輸出引數返回。

該儲存過程綜合考慮了報表工具計算能力不足的因素，將盡量多的計算都放到儲存過程中完成，這點是值得肯定的。但其中使用了大量的複雜 sql，以及多結果集的輸出方式（遊標），這又增加了程式設計難度。

如果用潤乾報表來實現這個需求，首先要編寫以下集算器指令碼：

	A
1	=connect(“ora”)
2	=A1.query(“select userid,first_logout_time,standard_7d_time from t_dw_zx_valid_account where standard_7d_time is not null”)
3	=A1.query(“select userid, sum(onlinetime) onlinetime, max(account) account from t_dw_zx_account_status_day where to_char(logtime,‘yyyy-MM-dd’)=”+"‘“+string(data_date)+”’ “+”  group by userid  having max(account) is not null")
4	=A1.query(“select userid, sum(onlinetime) onlinetime, max(account) account from t_dw_zx_account_status_day where to_char(logtime,‘yyyy-MM-dd’)=”+"‘“+string(data_date-1)+”’ “+”  group by userid  having max(account) is not null")
5	=A1.query(“select userid, sum(onlinetime) onlinetime, max(account) account from t_dw_zx_account_status_day where to_char(logtime,‘yyyy-MM-dd’)=”+"‘“+string(data_date-2)+”’ “+”  group by userid  having max(account) is not null")
6	=A1.query(“select userid,onlinetime,account,logtime from t_dw_zx_account_status_day where to_char(logtime,‘yyyy-MM-dd’)=”+“’”+string(data_date-2)+“’”)
7	=join@1(A2:a,USERID;A3:b,USERID;A4:c,USERID;A5:d,USERID)
8	=A7.new(#:no,a.USERID:userid,a.FIRST_LOGOUT_TIME:first_logout_time,b.ONLINETIME:current_time,c.ONLINETIME:last_time,c.ONLINETIME:last_last_time)
9	=A8.to(10)
10	=A8.to(A7.len()-10+1,)
11	=A6.count@b(d!=null && c!=null)
12	=A6.count@b(b==null && c!=null && d!=null)
13	=A6.count@b(a.FIRST_LOGOUT_TIME>data_date-1  && a.FIRST_LOGOUT_TIME<data_date)
14	=A6.count@b(b==null && a.FIRST_LOGOUT_TIME>data_date-1  && a.FIRST_LOGOUT_TIME<data_date)
15	=A6.count@b(d==null && c!=null && a.FIRST_LOGOUT_TIME<data_date-1)
16	=A6.count@b(b==null && c!=null && d==null && a.FIRST_LOGOUT_TIME<data_date-1)
17	=new(A11:num1,A12:num2,A13:num3,A14:num4,A15:num5,A16:num6)
18	>A1.close()
19	result A9,A10,A17

程式碼說明：

A1：連線配置好的 oracle 資料庫。

A2-A6：從資料庫按照條件和分組彙總、取數。其中的 A3、A4、A5 的 group 雖然也可以放到集算指令碼中實現，但指令碼中還是利用了 sql 的 group，這裡的原則是簡單運算儘量還是讓資料庫去做，這樣可以讓取出資料量變少，從而節省 JDBC 的傳輸時間；而對於複雜的過程性計算才適合放到集算指令碼中做，從而發揮資料庫和集算指令碼各自的優勢。

A7：將以上結果集進行關聯。

A8：根據 A7 建立新序表，用於讀取前後十名記錄。

A9-A10：透過序號分別取前後十名記錄。

A11-A17：計算彙總值。

A19：將前十名、後十名記錄以及彙總值分別以不同結果集返回給潤乾報表。

集算器指令碼編寫之後，儲存為 test.dfx，在潤乾報表中新增集算器資料集進行呼叫：

潤乾報表接收集算器指令碼返回的三個結果集，其中”test.dfx”為集算器指令碼名稱。

下一步，要按照需求繪製報表模板檔案，即可完成報表設計：