TSQL 與 SSIS 查找
我試圖為 SSIS 查找組件找到一些性能良好的 Tsql 等效項。特別是對於事實表載入。
在幾個不同的查詢模式之後,大多數 obv 表現最好:
INSERT INTO Fact WITH (TABLOCK) SELECT F.Attribute1, ... d1.DimAId, d2.DimBId, d3.DimCId, ... F.Amount ... from Staging.dbo.GeneralLedger F WITH(NOLOCK) LEFT OUTER JOIN DimA1 d1 WITH(NOLOCK) on d1.AId = F.AId LEFT OUTER JOIN DimB d2 WITH(NOLOCK) on d2.BId = F.BId LEFT OUTER JOIN DimC d3 WITH(NOLOCK) on d3.CId = F.CId
性能方面,我用 500 萬行和 9 個維度的查找對此進行了測試。
SSIS:1m14s TSQL:1m0s
如果我在更大的數據集(+100M 行)上選擇 TSQL 而不是 SSIS,這將是什麼影響目前我在 200GB RAM 伺服器上進行了測試,因此將所有內容都放入記憶體中沒有問題。
但是,我想如果此查詢將在記憶體較少的機器上使用,它將開始溢出到磁碟並嚴重損害性能。SSIS 會在記憶體方面更有效率。我對這個假設是否正確?
我想到的另一個想法是使用游標循環僅使用 tsql 執行此操作,例如在所有數據載入之前的 1M 行。通過這樣做,您將有更少的機會獲得記憶體並開始浪費到磁碟。這是一種可行的方法還是看到你任何其他方法?
PS:我完全知道 SSIS 是載入事實表的方式。但是,讓我們假設我們只想要 TSQL。PS2:問題也在 stackoverflow https://stackoverflow.com/questions/26638590/tsql-vs-ssis-lookup上發布。然而,沒有真正的開創性回應。
將大型數據集批處理為較小的數據集,例如 100,000 行或 1,000,000 行,將使負載執行得比 One Big Insert 更好。當然,SSIS 也是如此,因為它是批量插入的。
您在範例中顯示的時間差異很小。這並沒有給大大提高速度帶來太大希望,但它確實鼓勵您知道您可以使用其他方法進行導入。
編輯包括在下面。
如果您願意先提取到 .csv 文件,或許可以使用 BCP 從視圖中提取到 .csv 文件。然後您可以使用
BULK INSERT
將該文件載入到您的數據庫中:
BULK INSERT
描述:http: //msdn.microsoft.com/en-us/library/ms188365.aspx如果您想在 TSQL 中進行全部導入,那麼您可以使用該
BULK INSERT
命令。當您查看它的參數時,您會看到它包含KILOBYTES_PER_BATCH
並ROWS_PER_BATCH
作為控制批量大小的方法。由於 和 的速度,
BCP
以及對批量大小BULK INSERT
的控制,我相信這將是使用起來更快的方法之一。但它確實需要中間的 .csv 文件。其他問題:您的數據是來自同一台伺服器,還是來自另一台伺服器?如果是同一台伺服器,那麼訪問是相當東的。如果另一個伺服器並且您不想要中間 .csv 文件,您可以創建一個連結伺服器來查詢來自其他伺服器的數據。
但是,正如您所指出的,這意味著您必須自己管理批次。這類似於您所描述的“游標循環”,儘管它可能不需要游標,只是一個選擇要複製的下一個“n”行的循環。如果數據在另一台伺服器上,這種方法的成本會更高。
沒有適用於所有情況的答案。不過一般來說…
如果查找列表很小並且您可以記憶體它(或使用記憶體數據源),則在 SSIS 中執行此操作不會造成太大的性能損失。如果您想將 50 個位置程式碼的列表交叉引用到城市名稱,那就去吧。很高興在一個地方看到螢幕上的所有過程,而不是埋在 sql 語句中。
在大多數情況下,TSQL 的性能會更好,因為它對數據了解最多,而且查詢優化器總是比你更聰明。如果所有數據都在一個 DB 中,您可以在 sql 查詢源中隱藏很多複雜性。
如果數據分佈在不同的系統中,中間立場是從每個系統進行 SSIS 合併連接。試圖在 RDBMS 級別做到這一點是瘋狂的。不過,始終在源查詢中進行排序。SSIS 排序幾乎總是一個壞主意。