Fastest performance with DELETE logic NOT IN nested query

sql, sql-delete, sql-server, sql-server-2008, t-sql

Solution

how about doing it via `JOIN`?

DELETE  a
FROM    [dbo].[I2B2_SRC_VISITS] a
        LEFT JOIN [dbo].[I2B2_SRC_PATIENT] b
            ON a.[PATIENT_ID] = b.[PATIENT_ID]
WHERE   b.[PATIENT_ID] IS NULL

Make sure that column `[PATIENT_ID]` from both tables has key define on them which makes it more faster.

Right. `NOT EXIST` is better one.

DELETE  a 
FROM    [dbo].[I2B2_SRC_VISITS] a 
WHERE   NOT EXISTS
        ( 
            SELECT  1 
            FROM    [dbo].[I2B2_SRC_PATIENT] b
            WHERE   a.[PATIENT_ID] = b.[PATIENT_ID] 
        )

Problem

I'm trying to get the fastest performance for this DELETE (and SELECT) query. Is there a better way to DELETE the records, because this takes over 10 minutes to run? I imagine it has to do it's own sort and merge until it can find the records. ``` SELECT COUNT([VISIT_ID]) FROM [dbo].[I2B2_SRC_VISITS] WHERE [PATIENT_ID] NOT IN ( SELECT [PATIENT_ID] FROM [dbo].[I2B2_SRC_PATIENT] ) DELETE FROM [dbo].[I2B2_SRC_VISITS] WHERE [PATIENT_ID] NOT IN ( SELECT [PATIENT_ID] FROM [dbo].[I2B2_SRC_PATIENT] ) ``` EDIT: I couldn't put the DELETE in front of that query like I did with the SELECT. But this was the end result for the DELETE statement. ``` DELETE FROM [dbo].[I2B2_SRC_VISITS] WHERE [VISIT_ID] IN ( SELECT a.[VISIT_ID] FROM [dbo].[I2B2_SRC_VISITS] a LEFT JOIN [dbo].[I2B2_SRC_PATIENT] b ON a.[PATIENT_ID] = b.[PATIENT_ID] WHERE b.[PATIENT_ID] IS NULL ) ```

Original source

Related problems