Why is this subquery SQL query so slow?

Question

I have this query:

select * from transaction_batch where id IN ( select MAX(id) as id from transaction_batch where status_id IN (1,2) group by status_id ); 

The internal query runs very quickly (less than 0.1 seconds) to get two identifiers, one for status 1, one for state 2, then it selects based on the primary key to be indexed. The explanation request says that he is looking for 135k lines using only one, and I cannot understand for life why this is so slow.

Answer 1

 select b.* from transaction_batch b inner 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 

Answer 2

An internal query is executed separately for each row of the table again and again.

Since there is no reference to an external query in the inner query, I suggest you separate these two queries and simply insert the results of the inner query into the WHERE .

Answer 3

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

I had a performance issue as shown below:

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

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

1sec: WHERE @CSV like CONCAT('%',[Column],'%') // Solution below

I tried to join, not request.

I also tried using CTE in hard code.

I finally tried the temporary table.

None of these standard options worked, and I did not want to use the dosp_execute option.

The only solution that worked like:

 DECLARE @CSV nvarchar(max) = Select STRING_AGG([Value],',') From [Table]; // This yields @CSV = 'A,B,C' ... WHERE @CSV LIKE CONCAT('%',[Column],'%')