SQL Server->> Conditional filtering practices-in (Value1,value2,...) Performance comparison with inner JOIN string_split ()

In a comma-stitched string, the element string passed to the in clause includes more than 1400 elements

The two approaches were

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,44 5100,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,44 5100,450100,451000,450800,450300,451100,450200,450900,450500,450400,450600,460100,510100,... ', ', ') T on E.sspfcityid = T.value

The comparison shows that if the IN clause uses a hash match aggregation operator, the inner join uses distinct SORT.

In contrast to the IO statistics, it can be found that the in sentence method has many workfile generated IO

(128478 rows affected)

Table ' worktable '. Scan count 0, logical read 0 times, physical read 0 times, read 577 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Workfile '. Scan Count 70, logical read 2,424 times, physical read 172 times, read 2,268 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Employee '. Scan Count 9, logical read 4,559 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Verifyprocess '. Scan Count 9, logical read 3,136 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

And with inner join, there is workfile generated IO

(128478 rows affected)
Table ' worktable '. Scan count 0, logical read 0 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Workfile '. Scan count 0, logical read 0 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Employee '. Scan Count 9, logical read 4,559 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.
Table ' Verifyprocess '. Scan Count 9, logical read 3,136 times, physical read 0 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.

The performance of this example looks like the total time overhead difference is not obvious, because the number of connected tables is small, and if the number of tables connected is more, the entire execution plan may be another matter, and the drawbacks of the in sentence appear.

SQL Server->> Conditional filtering practices-in (Value1,value2,...) Performance comparison with inner JOIN string_split ()