五餅二魚工作室

前一陣子有個朋友忽然 E-Mail 來一個很特別的問題，他們在公司內有一些不同的系統，也會有不同的營業點，各自都會有自己的資料各自存放在許多種類型的資料庫當中。最近為了能夠有一些不同的突破，因此考慮將這一些資料如何來做彙整分析。

看起來是一個很單純的 ETL ( Extract-Transform-Load ) 的處理，整個會由 ETL 的主機上透過應用程式，將資料從 Source 主機中取出處理之後，再存放到 Target 主機上。在處理過程中，他們採用的一套 Open Source 的 ETL 工具，剛開始在測試過都還是很順利的，但當開始壓力測試的時候，就發現當轉入約 10000 資料，就要花費 1hr 的時間，這樣的狀況實在是很不能接受，因此來詢問看看是否有任何可以改善的方向。

原本我們朝向硬體設備的角度去查看，但看起來應該也不像，因為這三個角色目前都是使用虛擬化的 VM， Host 的硬體效能不錯，網路也都還能有 Gigabit 的速度，因此初步排除硬體的影響。而接著我們往軟體環境來看，在這三台主機上都有安裝防毒程式，似乎也有點小幅度的影響到效能，但看起來只是每次命令差不多會有 50~70 ms 的影響，好像又不是那麼嚴重，因此我們決定看一下他們在 ETL 上的設定。

看起來 ETL 的設計是蠻單純的，主要就是 ETL 會從 Source 主機取出一個範圍的資料，接下來就會一筆一筆的下指令到 Target 主機上判斷是否有存在，如果有就更新部分的欄位，沒有的話則新增該筆資料到 Target 的環境中。

但在這樣的處理中，前面的過程還算快速，幾乎沒有花到多少的時間。可是在後面的每次判斷和處理，平均一筆資料要 150~250ms 的時間，看起來還算 OK，但是要是再乘上資料的筆數，那時間就耗用的算是很大了。因此看起來問題應該是卡在在這個 ETL 的處理過程是採用單一筆資料的處理而非一次一個批量的處理，才導致耗用那麼長的時間。

既然知道問題原因了，那接下來該如何處理呢 ? 我先詢問一下一開始的設計人員，從他們那裏知道，由於 Source 和 Target 電腦都是不同的系統，因此沒有辦法直接下指令來處理；也有考慮使用 SQL Server 的 Link Server 的方式來串接，但因為有些處理又需要 ETL 上先做判斷和處理過，因此沒有辦法直接把 Source 和 Target 用 Link Server 來串接，所以才會使用目前的方式。

了解這些原因和限制條件之後，我們就先做一個實驗，測試 ETL 一次將 10000 筆資料全部寫入到 Target 的資料庫內，這樣大約需要 2 ~ 3 秒的時間。看起來真的比一筆一筆寫入有效率太多了。因此我們調整原本的處理流程，ETL 把資料從 Source 取出並做處理後，直接把資料寫到 Target 的一個暫時 Table 內，接的再使用 Merge 的指令來做更新。這樣看起來似乎多繞了一點路，但這樣的效果 10000 筆資料就全部處理完成只需要 5 秒鐘的速度，跟原本一筆一筆的處理，效能提升就非常的多了。如果把測試資料的筆數放大數倍之後，效果依然是非常的不錯。

那甚麼樣的資料庫可以使用 Merge 指令呢 ?  Merge 指令目前屬於 SQL:2003 的標準規範內，因此許多的資料庫都有提供支援。SQL Server 2008 之後就開始支援 Merge 的指令，而 Oracle 9i 之後的版本，也有支援這樣的語法。以 SQL Server 為例，語法架構如下：

[ WITH <common_table_expression> [,...n] ]
MERGE 
    [ TOP ( expression ) [ PERCENT ] ] 
    [ INTO ] <target_table> [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias ]
    USING <table_source> 
    ON <merge_search_condition>
    [ WHEN MATCHED [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
        THEN <merge_not_matched> ]
    [ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
        THEN <merge_matched> ] [ ...n ]
    [ <output_clause> ]
    [ OPTION ( <query_hint> [ ,...n ] ) ]    
;

跟其他的資料庫有一點差異，SQL Server 再使用的時候，除了有多支援 CTE ( COMMON TABLE EXPRESSION ) 的語法，另外也還多支援 WHEN NOT MATCHED BY SOURCE，可以用在當目的端存在有資料但是來源端並沒有資料，是否要刪除目的端的資料。至於該指令該如何使用，網路上有需多相關的文章，有興趣的朋友可以參考看以下的一些文章：

SQL Server 2008 T-SQL 新語法介紹 - Merge (效能改善)

SQL - 使用 MERGE

PS. 如果 ETL 的處理是採用 SSIS 的話，則可以考慮配合「緩時變維度」（Slowly Changing Dimensions, SCD）來做處理，也可以有不錯的效果，但當時考量到使用 SSIS 會衍生出其他 License 費用的問題，因此才沒有使用。