[SQL][Performance]一個大量資料轉換時的效能問題案例分享

[SQL][Performance]一個大量資料轉換時的效能問題案例分享

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

 

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

image

 

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

 

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

image

 

但在這樣的處理中,前面的過程還算快速,幾乎沒有花到多少的時間。可是在後面的每次判斷和處理,平均一筆資料要 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 費用的問題,因此才沒有使用。