如何解決報表關聯計算中的效能問題

報表開發過程中經常要在報表中完成資料關聯計算，有時為了降低報表製作複雜度，會將關聯關係放到可視的報表模板中完成；而有時則必須在報表中完成關聯，如多資料來源、異構資料來源的情況。在報表中做關聯往往導致報表效率不高，計算過慢，從而引發效能問題。為此，潤乾報表提供了高效能資料關聯方式（需要結合集算器實現），可以顯著提升報表的計算效率。這裡就透過一個常見的多源關聯分片報表例項來說明潤乾報表的實現過程：

報表說明

根據銷售情況等資訊按照時間、地區、銷售人員、產品等維度彙總銷售額，報表樣式如下：

以下為實現過程。

編寫計算指令碼

首先使用集算器編寫指令碼，完成資料關聯，併為報表返回關聯後的結果集。

	A
1	=connect(“demo”)
2	=A1.query(“SELECT 客戶. 地區, 客戶. 城市, 訂單明細. 數量, 訂單明細. 折扣, 訂單明細. 單價, 訂單. 僱員 ID, 訂單. 訂購日期, 訂單明細. 產品 ID FROM 訂單明細, 訂單, 客戶 WHERE 客戶. 客戶 ID = 訂單. 客戶 ID AND 訂單. 訂單 ID = 訂單明細. 訂單 ID and 訂單. 訂購日期 is not null”)
3	=A1.query(“SELECT 類別. 類別 ID, 類別. 類別名稱 FROM 類別”)
4	=A1.query(“SELECT* from 僱員”)
5	=A1.query(“SELECT 產品. 類別 ID, 產品. 產品 ID FROM 產品”)
6	>A5.switch(類別 ID,A3: 類別 ID)
7	>A2.switch(僱員 ID,A4: 僱員 ID)
8	>A2.switch(產品 ID,A5: 產品 ID)
9	=A2.new(year( 訂購日期): 年,month(訂購日期): 月, 地區, 城市, 單價 * 數量: 金額, 僱員 ID. 職務: 職務, 僱員 ID. 僱員 ID: 僱員 ID, 僱員 ID. 姓氏 + 僱員 ID. 名字: 姓名, 產品 ID. 類別 ID. 類別 ID: 類別 ID, 產品 ID. 類別 ID. 類別名稱: 類別名稱 )
10	return A9

A1：連線資料來源；

A2-A5：執行 sql，分別取訂單、產品等庫表資料；

A6-A8：使用 switch 將多表資料完成關聯，關聯結果存入 A2 格中；

A9：根據已關聯結果建立新序表，結果集透過 A10 返回報表。

編制報表

在潤乾報表設計器中新建報表模板後，資料集選擇“集算器”，在資料集編輯視窗指定上述編輯好的 dfx 檔案，完成資料集建立。

編輯報表模板表示式：

不同於報表模板中的低效關聯，集算報表是事先在集算指令碼中完成關聯，報表模板只需從結果集取數，從而獲得更高的效能。

為了對照，我們再按照傳統的報表中實現關聯的方式做一下：

報表中實現關聯

資料集

ds1: SELECT 客戶. 地區, 客戶. 城市, 訂單明細. 數量, 訂單明細. 折扣, 訂單明細. 單價, 訂單. 僱員 ID, 訂單. 訂購日期, 訂單明細. 產品 ID FROM 訂單明細, 訂單, 客戶 WHERE 客戶. 客戶 ID = 訂單. 客戶 ID  AND  訂單. 訂單 ID = 訂單明細. 訂單 ID and 訂單. 訂購日期 is not null

ds2: SELECT 類別. 類別 ID, 類別. 類別名稱 FROM 類別

ds3: SELECT * from 僱員

ds4: SELECT 產品. 類別 ID, 產品. 產品 ID FROM 產品

報表模板

對比效果

本例的源表資料為 40 多萬條，基於同樣的取數 sql，兩種關聯方式的執行時間如下表所示：

可以看到，潤乾報表在處理關聯計算類報表時有明顯的優勢。這是因為在報表中實現關聯只能使用遍歷演算法，針對單條主記錄去尋找關聯的子記錄，因此效率不高；而集算器採用了更高效的 hash 關聯方案，事先將所有子記錄按對應碼 hash 到主記錄上（程式碼中的 switch 函式就利用了 hash 關聯技術），因而能獲得一倍以上的效能提升（如果只算關聯能快出 5-10 倍）。

此外，集算器也非常適合處理異構資料來源的資料關聯，如常見的跨庫計算、檔案和資料庫混合計算等情況。

下附執行日誌和測試機配置。

【附 1】執行日誌

報表中關聯

[2018-09-15 11:32:59] : [INFO]  - 開始運算報表，首先取數……

[2018-09-15 11:32:59] : [DEBUG]  - 下面開始打出 sql

[2018-09-15 11:32:59] : [DEBUG]  - ds1=SELECT 客戶. 地區, 客戶. 城市, 訂單明細. 數量, 訂單明細. 折扣, 訂單明細. 單價, 訂單. 僱員 ID, 訂單. 訂購日期, 訂單明細. 產品 ID FROM 訂單明細, 訂單, 客戶 WHERE 客戶. 客戶 ID = 訂單. 客戶 ID  AND  訂單. 訂單 ID = 訂單明細. 訂單 ID and 訂單. 訂購日期 is not null

[2018-09-15 11:33:35] : [DEBUG]  - 下面開始打出 sql

[2018-09-15 11:33:35] : [DEBUG]  - ds2=SELECT 類別. 類別 ID, 類別. 類別名稱 FROM 類別

[2018-09-15 11:33:35] : [DEBUG]  - 下面開始打出 sql

[2018-09-15 11:33:35] : [DEBUG]  - ds3=SELECT * from 僱員

[2018-09-15 11:33:35] : [DEBUG]  - 下面開始打出 sql

[2018-09-15 11:33:35] : [DEBUG]  - ds4=SELECT 產品. 類別 ID, 產品. 產品 ID FROM 產品

[2018-09-15 11:33:35] : [INFO]  - 取數結束, 開始運算

[2018-09-15 11:34:58] : [INFO]  - 計算結束：

dfx 中關聯

[2018-09-15 11:56:33] : [INFO]  - 開始運算報表，首先取數……

[2018-09-15 11:57:11] : [INFO]  - 取數結束, 開始運算

[2018-09-15 11:57:26] : [INFO]  - 計算結束：

【附 2】測試機配置

測試機型：Dell Inspiron 3420

CPU：Intel Core i5-3210M @2.50GHz *4

RAM：4G

HDD：西數 WDC(500G 5400 轉 / 分)

作業系統：Win7(X64) SP1

JDK：1.6

資料庫：oracle11g R2

潤乾報表版本：2018