SQL Server ->> 條件式篩選做法之 -- IN(VALUE1,VALUE2,...)與INNER JOIN STRING_SPLIT()效能對比

標籤：img   串連   inner   例子   process   統計   com   alt   操作符   

在以逗號拼接而成的字串，傳入給IN字句的元素字串中包涵了1400多個元素

兩種做法分別為 

AND e.ssPfCityId IN (
SELECT
CAST(value AS INT)
FROM STRING_SPLIT(‘110000,310000,120000,210100,210200,210400,210800,211200,350100,350500,350200,350800,350700,350900,441200,441300,440500,445100,450100,451000,450800,450300,451100,450200,450900,450500,450400,450600,460100,510100,...‘
,‘,‘)
)

 

INNER JOIN (SELECT DISTINCT CAST(value AS INT) AS VALUE FROM STRING_SPLIT(‘110000,310000,120000,210100,210200,210400,210800,211200,350100,350500,350200,350800,350700,350900,441200,441300,440500,445100,450100,451000,450800,450300,451100,450200,450900,450500,450400,450600,460100,510100,...‘,‘,‘) T) T ON e.ssPfCityId = T.VALUE

 

 

對比看出如果用IN字句會用一個HASH MATCH的彙總操作符，而用INNER JOIN則用DISTINCT SORT。

 

 

而如果對比IO統計資料可以發現IN字句的做法多出了許多Workfile產生的IO

(128478 行受影響)

表 ‘Worktable‘。掃描計數 0，邏輯讀取 0 次，物理讀取 0 次，預讀 577 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 ‘Workfile‘。掃描計數 70，邏輯讀取 2424 次，物理讀取 172 次，預讀 2268 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 ‘employee‘。掃描計數 9，邏輯讀取 4559 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 ‘verifyProcess‘。掃描計數 9，邏輯讀取 3136 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

 

而用INNER JOIN則麼有Workfile產生的IO

(128478 行受影響)
表 ‘Worktable‘。掃描計數 0，邏輯讀取 0 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 ‘Workfile‘。掃描計數 0，邏輯讀取 0 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 ‘employee‘。掃描計數 9，邏輯讀取 4559 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 ‘verifyProcess‘。掃描計數 9，邏輯讀取 3136 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

 

這個例子的效能看上去總時間開銷差別並不是很明顯，因為串連的表數量少，而如果串連的表數量多起來，可能整個執行計畫會是另一回事，那個時候IN字句的弊端就顯現了。

SQL Server ->> 條件式篩選做法之 -- IN(VALUE1,VALUE2,...)與INNER JOIN STRING_SPLIT()效能對比