“不存在”似乎正在減慢插入速度

我正在嘗試執行一個腳本，該腳本一次將超過 2000 萬條記錄批量插入到 10,000 條表中。在執行開始時，它似乎工作正常。儘管一旦插入了許多記錄（270,000 條），腳本就開始顯著變慢。再插入 30,000 條記錄需要 23 小時。我最好的猜測是，隨著新記錄數量的增加，腳本檢查新記錄是否已經存在的部分需要更長的時間。我已經為腳本中使用的表創建了索引，但我需要縮短此腳本的執行時間。任何幫助將非常感激。我的腳本如下。

CREATE NONCLUSTERED INDEX [plan2TMP] ON [dbo].[plan2]
(
      [l_dr_plan1] ASC
)
INCLUDE
(
l_address,
l_provider
)ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [nameTMP] ON [dbo].[name]
(
      [dr_id],
      [nationalid]
)ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [plan1TMP] ON [dbo].[plan1]
(
[dr_id],
[cmt]
)ON [PRIMARY]
GO

DECLARE @BatchSize int = 10000

WHILE 1 = 1
BEGIN

INSERT INTO plan2(
                    l_dr_plan1
                    , l_address
                    , l_provider
                    )
SELECT TOP (@BatchSize)
                    dr1.link
                    ,ad1.link
                    ,dr.link
from plan1 dr1
INNER JOIN name dr ON dr1.dr_id = dr.dr_id
INNER JOIN name2 dr2 on  dr2.nationalid = dr1.cmt
INNER JOIN address1 ad1 ON ad1.dr_id = dr2.dr_id
WHERE NOT EXISTS (
                 SELECT l_plan1
                 FROM plan2
                 WHERE ltrim(rtrim(dr1.link)) + ltrim(rtrim(ad1.link)) + ltrim(rtrim(dr.link)) = ltrim(rtrim(l_dr_plan1)) +ltrim(rtrim(l_address)) +ltrim(rtrim(l_provider))
                 )
AND dr1.cmt <> ''

IF @@ROWCOUNT < @BatchSize
 BREAK
END

源數據還不錯。我可以拔出 ltrim/rtrim 而不會遇到任何問題。我有串聯的原因是我想不出更好的方法來比較我插入的值和已經插入的值。

連結值是每個表中的唯一列（但不是 PK 或 FK）。plan2 表引用plan1、address1 和name 表的連結來連接後端的這些記錄，並在前端將它們一起顯示。

一次插入 10k 的目的是日誌文件變得如此之大，以至於將伺服器記憶體增加到大約 10 mbs。通過批處理，它將以塊的形式送出記錄並防止日誌文件擴展失控。

您現在擁有的程式碼可以保證在每個連續循環上做更多的工作。我會假設目標表一開始是空的，所以SELECT查詢部分只需要返回 10k 行就可以插入 10k 行到plan2. 對於下一個循環，SELECT查詢返回的前 10k 行已經在目標表中，因此它需要返回 20k 行才能將 10k 附加行插入到目標表中。您預計總共插入 2000 萬行。批量大小為 10k 行，您需要執行 2000 個批次。每個批次平均需要從SELECT查詢中讀取 1000 萬行，因此為了完成您的流程，您SELECT需要INSERT生成 200 億行。工作量很大，而且您在此處使用的循環方法是不可避免的。

另一件立即突出的事情是在@BatchSize沒有RECOMPILE提示的情況下使用局部變數。您在TOP語句中使用了它，預設情況下 SQL 伺服器將假定您只需要查詢返回 100 行。RECOMPILE如果沒有提示或其他提示，查詢優化器不知道局部變數的值是什麼。查詢優化器可以根據運算符的存在選擇不同的計劃TOP，並且估計值為 100 時，它可能會選擇一個對您正在嘗試做的事情非常低效的計劃。這是檢查和發布估計的或實際的查詢計劃真正有幫助的地方。

假設查詢優化器選擇plan2對NOT EXISTS. 查詢的那部分將在每個連續循環上做更多的工作，但它比以前更糟。對於第 N 個循環，您需要掃描10000 * (N - 1)行10000 * N次。如果您已經將 270k 行插入到表中，這意味著 N 的值為 28，那麼您可以掃描 10000 * 27 * 10000 * 28 = 75600000000 總行來插入接下來的 10k 行。這很容易需要 7 個小時才能完成。您可能遇到了這個問題，或者您可能遇到了一個完全不同的問題。擺脫@BatchSize變數或使用提示可能會有所幫助。

我對批量插入的方法是盡可能避免在SELECT零件上循環。您是否能夠在插入之前刪除目標表上的所有非聚集索引？是否有具有恢復模型的數據庫SIMPLE，您可以將數據的臨時副本寫入其中？如果那裡有足夠的空間，則 Tempdb 可以工作。您可以利用最少的日誌記錄來跳過循環。可能所需要的只是添加一個TABLOCK暗示。如果您目前的數據庫具有正確的恢復模型，您可能只能插入 2000 萬行。如果沒有，您可能能夠插入到另一個數據庫中並循環該數據的臨時副本以插入到您的目標表中。重要的是，如果您需要循環製作它，以便每個循環花費相同的時間。您不希望每個循環的工作量像目前那樣線性或二次增長。

如果做一個單一的插入不是一個選項，你應該改變你的循環風格。目標是避免從源表中多次處理同一行。如果可能，您還希望按目標表的聚集順序插入。我不知道你的數據模型，所以我不能給出非常具體的建議，但是plan1表有主鍵嗎？plan1表中的行數與您需要插入的行數之間是否存在某種關係plan2？例如，您可以嘗試編寫plan1以 10k 行塊處理表的程式碼。使用正確的索引可以是一個相對有效的查詢。我有一篇博文這涵蓋了一堆不同的循環方法。文章中的範例只是從單個表中讀取數據，但您可以將它們調整為SELECT您擁有的查詢。再次強調這一點，您正在尋找一種在每個循環中執行恆定工作量的策略。

如果出於某種原因您確實需要保留NOT EXISTS以下程式碼，則以下程式碼更為典型，我認為它可以滿足您的需求：

WHERE NOT EXISTS (
   SELECT 1
   FROM plan2
   WHERE dr1.link = plan2.l_dr_plan1
   AND ad1.link = plan2.l_address
   AND dr.link = plan2.l_provider
)

我不清楚問題出在ltrimorrtrim上，但是如果可以的話，刪除這樣的函式總是好的。

引用自：https://dba.stackexchange.com/questions/189585