一次因表變量導致SQL執行效率變慢的實戰記錄

Posted on by

場景

最近工作中，發現某同步JOB在執行中經常拋出SQL執行超時的問題，查看日志發現每次SQL執行的時間都是線性增長的，循環執行50次以後執行時間甚至超過瞭5分鐘

JOB執行流程分析

首先，對於JOB流程進行分析，查看是否是JOB設計上的問題

通過對流程的分析，發現每次獲取的需要同步的數據最多隻有一萬條，不存在大數據寫入導致超時的問題。

那麼在對獲取詳細信息這個過程進行分析，發現關聯的表中最多的數據已經上億瞭，可能是這裡導致瞭整體SQL執行變慢的原因。這裡能算可疑點一。

再接著往下一個流程看與表B對比重復數據時，隨著循環執行表B的數據會越來越多，那麼會不會這裡是導致循環執行下執行時間稱線性增長的主要原因呢。

逐一排除問題

之前我們通過分析JOB執行流程，發現瞭兩個可疑點，那麼現在具體分析SQL的問題

CREATE TABLE #TableTemp (
        字段A int null,
        字段B int null,
        字段C int null
    )
 
    INSERT INTO #TableTemp(
        字段A,
        字段B
    )SELECT
        a.字段A,
        字段B
    FROM ServerA.dbo.TableB a WITH(NOLOCK)
    LEFT JOIN dbo.TableA b WITH(NOLOCK) a.Id = b.Id
 
 
 
    UPDATE a
    SET a.字段C = b.字段D
    FROM #TableTemp a
    LEFT JOIN dbo.TableC b WITH(NOLOCK) ON a.字段A =b.id
 
 
    INSERT INTO dbo.目標TableA(
        字段A,
        字段B
    )
    SELECT
        字段A,
        字段B
    FROM #TableTemp WITH(NOLOCK)
 
    INSERT INTO dbo.目標TableB(
        字段A,
        字段B,
        字段C
    )
    SELECT DISTINCT        
        a.字段A,
        a.字段B,
        a.字段C
    FROM #TableTemp a WITH(NOLOCK)
    LEFT JOIN dbo.目標TableB b ON a.字段A = b.字段A AND a.字段B = b.字段B
    WHERE a.PK IS NULL

先來查看可疑點一，是不是這裡出瞭問題。因為表TableC數據已經是幾億的量，但單獨將該SQL執行發現，因為索引的存在發現執行並不是特別慢，所以可以排除掉該問題

那麼來看看可疑點二呢

INSERT INTO dbo.目標TableB(
        字段A,
        字段B,
        字段C
    )
    SELECT DISTINCT        
        a.字段A,
        a.字段B,
        a.字段C
    FROM #TableTemp a WITH(NOLOCK)
    LEFT JOIN dbo.目標TableB b ON a.字段A = b.字段A AND a.字段B = b.字段B
    WHERE a.PK IS NULL

可以看到該SQL插入的同時還查詢瞭自身是否存在條件下相同的數據，查看表目標TableB發現，該表沒有主鍵也沒有索引，再通過DBA那邊提供的SQL分析發現，這句SQL對於dbo.目標TableB進行瞭全表掃描，再加上插入的1W條數據，相當於對於dbo.目標TableB全表掃描瞭1w次，隨著循環的執行該表數據越來越多，執行時間也就越來越長，看來這裡就是導致執行時間線性增長的主要原因瞭。

解決問題

根據上面問題的排除，我們已經得知問題的關鍵所在就是進行瞭1w次的全表掃描，導致瞭SQL執行時間過長，那麼解決問題的關鍵所在就是避免這麼多次的全表掃描。那麼最直接的解決方法，就是建立索引避免全表掃描

1.通過使用臨時表代替表變量

先來看看，表變量與臨時表的區別，可以看到表變量是無法使用索引的，所以我們使用索引避免全表掃描的話必須要代替掉表變量，然後在臨時表的字段A上我們創建索引

2.修改目標TableB的寫入邏輯

現有寫入邏輯會先判斷是否在目標TableB中是否存在，不存在時則寫入表中，保持業務的情況下，我們稍微修改下邏輯，再寫入之前先排除掉與目標TableB中的數據，將剩餘數據寫入表中，就能避免循環1W次的目標TableB表查詢瞭

通過這兩處修改後，再執行該JOB發現問題得到瞭完美的解決。

總結

到此這篇關於因表變量導致SQL執行效率變慢的文章就介紹到這瞭,更多相關表變量導致SQL執行變慢內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：