Why is this SQL query with subquery very slow? Why is this SQL query with subquery very slow? sql sql

Why is this SQL query with subquery very slow?


The inner query is run seperatly for every row of your table over and over again.

As there is no reference to the outer query in the inner query, I suggest you split those two queries and just insert the results of the inner query in the WHERE clause.


select b.*from transaction_batch binner join (    select max(id) as id    from transaction_batch    where status_id in (1, 2)    group by status_id) bm on b.id = bm.id


my first post here.. sorry about the lack of formatting

I had a performance problem shown below:

90sec: WHERE [Column] LIKE (Select [Value] From [Table]) //Dynamic, slow

1sec: WHERE [Column] LIKE ('A','B','C') //Hardcoded, fast

1sec: 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],'%')