Egan伊根

好久沒有刷存在感了......今天來分享一下這一天半玩下來的心得~!

「短文」開始：

問題是程式撈取DB資料時拋出  「System.Data.SqlClient.SqlException: 已超過連接逾時的設定。在作業完成之前超過逾時等待的時間，或者是伺服器未回應。」

問題追查的結果則是因為資料量多達數千萬筆且查詢條件使用了「LIKE」來JOIN 他表，此篇僅為筆記一下，希望能解救其他人的一整天～

首先先來了解一下表格，共有三張資料表（其實B_XX共有7張，為避免SQL Script不易讀所以只留下2張），如下表

Table Name	資料量	備註
A	6000000	沒有任何Index
B01	60000000	沒有任何Index
B02	40000000	沒有任何Index
#TempTable	5000	沒有任何Index

參考以下SQL Script，可以看得出在欄位SID使用了「SELECT IN 」，這樣的情況下查詢的效能其實是很差的，接著我們再看看第５行， B01一張表INNER JOIN了暫存表 #TempTable ，INNER JOIN的條件則使用了「LIKE　T.Mb　＋％」，其實就是要比對S.Mb為T.Mb開頭的資料，但資料表B01的資料量有6000萬筆，在沒有INDEX的情況下程式執行這段SQL通常就是Time out了（測試有INDEX好像也會Time out  XDD）。

SELECT DISTINCT MB FROM A 
WHERE SID 
IN( 
SELECT S.SID FROM B1 AS S INNER JOIN #TempTable AS T 
ON S.Mb LIKE T.Mb +'%'
WHERE (S.STARTDATE >= @STARTDATE AND S.STARTDATE < @ENDDATE)
UNION 
SELECT S.SID FROM DSUMSCR_201608 AS S INNER JOIN #TempTable AS T 
ON S.Mb LIKE T.Mb +'%'
WHERE (S.STARTDATE >= @STARTDATE AND S.STARTDATE < @ENDDATE)

其實「LIKE　T.Mb　＋％」只是要比對「S.Mb為T.Mb開頭的資料」，假設暫存表#TempTable內的資料長度只有兩種，分別是9、10

這樣的情況下其實可以調整成以下方式．使用 SUBSTRING(S.Mb,1,10) = T.Mb、 SUBSTRING(S.Mb,1,9) = T.Mb 的方式來代替「LIKE　T.Mb　＋％」做資料開頭比對，這樣查詢下來的效能差異可以高達數十倍......

【實測結果為以「LIKE　Mb　＋％」查詢耗時超過10分鐘且資料還沒查詢出來，而改用SUBSTRING則只要3秒鐘】

WITH  WHAS_10 AS ( 
SELECT S.SID FROM B1 AS S INNER JOIN #TempTable AS T ON SUBSTRING(S.Mb,1,10) = T.Mb  
WHERE ( (S.STARTDATE >= @STARTDATE AND S.STARTDATE < @ENDDATE)) 
UNION 
SELECT S.SID FROM B2 AS S INNER JOIN #TempTable AS T ON SUBSTRING(S.Mb,1,10) = T.Mb  
WHERE ( (S.STARTDATE >= @STARTDATE AND S.STARTDATE < @ENDDATE)) 
), 
WHAS_9 AS ( 
SELECT S.SID FROM B1 AS S INNER JOIN #TempTable AS T ON SUBSTRING(S.Mb,1,9) = T.Mb  
WHERE ( (S.STARTDATE >= @STARTDATE AND S.STARTDATE < @ENDDATE)) 
UNION 
SELECT S.SID FROM B2 AS S INNER JOIN #TempTable AS T ON SUBSTRING(S.Mb,1,9) = T.Mb  
WHERE ( (S.STARTDATE >= @STARTDATE AND S.STARTDATE < @ENDDATE)) 
)
SELECT DISTINCT MB FROM A WHERE SID 
IN( 
SELECT WHAS_10.SID  FROM  WHAS_10 
UNION 
SELECT WHAS_9.SID  FROM  WHAS_9
)

 

egan2608@gmail.com