記一次，因表變數導致SQL執行效率變慢

TheBestTxt發表於2021-11-06
變數SQL

場景

　　最近工作中，發現某同步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發現問題得到了完美的解決。

相關文章