SQL NOT IN or NOT EXISTS statement inefficient

Background

I have two tables – one is updated each day with new data (failed deadline) and one is continually added and deleted from dependent on the other (live table).

The way it works is that if the persons allocated work appears in the ‘failed deadline’ table, then add to the ‘live’ table. If the persons allocated work is then not on the ‘failed deadline’ table the next day, remove from the ‘live’ table.

The two biggest issues here are that whilst the ID number of the work is an easy check, it also has to check to see if the work is still allocated to the same employee, so that if it changes hands it doesn’t count against them.

The problem I’m facing is that the delete portion of the code seems to run over 3,000 rows and return about 300 just fine, but this SQL statement is taking over 4 minutes and 30 seconds to execute. What is the most efficient way of writing this statement? My gut instinct is telling me that the two statements are different and combining them is the issue.

DELETE FROM [LiveTable]
WHERE [WorkID] NOT IN (SELECT [WorkID]
                       FROM [FailedDeadline])
   OR [LiveTable].[EmployeeID] <> (SELECT [EmployeeID]
                                   FROM [FailedDeadline] as fd
                                   WHERE [LiveTable].[WorkID] = fd.[WorkID]);

Answer

Well I am not sure by how much this will lessen the execution time but rewriting the above query as follows might help.

DELETE LT
FROM [LiveTable]  LT
LEFT JOIN [FailedDeadLine] FD1 ON LT.WorkID=FD1.WorkID
LEFT JOIN [FailedDeadline] FD2 ON LT.WorkID=FD2.WorkID 
WHERE (FD1.WorkID IS NULL OR LT.EmployeeID<>FD2.EmployeeID)

Advertisement

Answer