Why is this SQL query with subquery very slow?
my first post here.. sorry about the lack of formatting
I had a performance problem shown below:
WHERE [Column] LIKE (Select [Value] From [Table]) //Dynamic, slow
WHERE [Column] LIKE ('A','B','C') //Hardcoded, fast
WHERE @CSV like CONCAT('%',[Column],'%') //Solution, below
I had tried joining rather than subquerying.
I had also tried a hardcoded CTE.
I had lastly tried a temp table.
None of these standard options worked, and I was not willing to dosp_execute option.
The only solution that worked as:
DECLARE @CSV nvarchar(max) = Select STRING_AGG([Value],',') From [Table];// This yields @CSV = 'A,B,C'...WHERE @CSV LIKE CONCAT('%',[Column],'%')