SQL Server is "Executing Query" endlessly-CodePudding

I am trying to run a simple query but it is not returning any results and is showing "Executing Query" endlessly.

This is my code:

select *
from ALERTTRAN_01
where PARENT_ALERT_ID not in (select PARENT_ALERT_ID
                              from nonbulkclosealert)

But when I change the code to

select *
from ALERTTRAN_01
where PARENT_ALERT_ID in (select PARENT_ALERT_ID
                          from nonbulkclosealert)

Note the not in to in, then it is running fine.

Any help is appreciated.

CodePudding user response：

The performance with IN and NOT IN can be a little tricky, because then initially loads all the data within the sub query to the memory before it matches the records. So if there are more rows, it'll drain the performance. So try with EXISTS and NOT EXISTS instead, like this

select *
from ALERTTRAN_01
where not EXISTS (
    select PARENT_ALERT_ID
    from nonbulkclosealert WHERE PARENT_ALERT_ID = ALERTTRAN_01.PARENT_ALERT_ID 
)

Here are a few helpful links that'll explain the advantages of EXISTS over IN

https://www.sqlservercentral.com/blogs/not-exists-vs-not-in

https://www.red-gate.com/hub/product-learning/sql-prompt/consider-using-not-exists-instead-not-subquery