David David - 26 days ago 7
SQL Question

Delete Where Exists

delete from VA_demo_setup_NCSC_temp
where exists
(select *
from VA_demo_setup_enrolled va
where VA_demo_setup_NCSC_temp.student_id = va.student_id
and VA_demo_setup_NCSC_temp.academic_period = va.academic_period);


I’ve seen a couple of similar posts to this but I haven’t been able to decipher why this particular query takes hours. I am trying to remove the records where id and period are in both datasets.

Answer

It would be easier to explain the poor performance if we had sight of your table structures and indices. However your query can be re-written as below. you might find it performs better this way and this is the more standard approach to deleting data in the manner you require.

DELETE          vt
FROM            VA_demo_setup_NCSC_temp vt
    INNER JOIN  VA_demo_setup_enrolled va ON vt.student_id = va.student_id AND vt.academic_period = va.academic_period;

Perhaps you have a primary key on the VA_demo_setup_NCSC_temp table that consists of a UNIQUEIDENTIFIER? (In which case the re-written query wouldn't change the performance)