Alternate way of Not exists in SQL

Question

I using the below Query which took long time to execute since it got 2 million records

DELETE  
AC 
FROM 
SGT_APPLICATION_CHECKLIST AC
INNER JOIN SGT_APPLICATION_CHECKLIST_REF CR ON        AC.CHECKLIST_REF_ID   =      CR.CHECKLIST_REF_ID 
INNER JOIN SGT_APPLICATION APP ON APP.APPLICATION_ID = AC.APPLICATION_ID
WHERE 
NOT EXISTS
(
SELECT 1 FROM SGT_APPLICATION_CHECKLIST SAC
WHERE SAC.APPLICATION_ID = AC.APPLICATION_ID AND
APP.APP_CATEGORY_VALUE = CR.APP_CATEGORY_VALUE
AND APP.APP_SUB_CATEGORY_VALUE = CR.APP_SUB_CATEGORY_VALUE
)

So What is the alternate way of instead "not exists"

1 - define slow. 2- 2 million records to be deleted could be "slow" for many reasons like triggers, cascades, large data, indexes being updated, etc. Have you looked at any of this? 3 - by 2 million do you mean 2 million to filter from and delete some or 2 million being deleted total? 4 - Before trying to rewrite the query, have you looked at the query plan to see why it might be slow? Once you have that you should include it. — Igor, Jun 27 '16 at 19:48
You probably just need the appropriate indexes on the tables. — Gordon Linoff, Jun 27 '16 at 19:49
Convert inner query to `LEFT JOIN` s with `IS NULL` ? Though modern query optimizers would probably try something like this by themselves. — PM 77-1, Jun 27 '16 at 19:49

score 0 · Answer 1 · edited May 23 '17 at 10:28

You can try a LEFT JOIN instead.

--DELETE AC 
SELECT * -- So you can quickly copy&paste to try. :)
FROM 
SGT_APPLICATION_CHECKLIST AC
INNER JOIN SGT_APPLICATION_CHECKLIST_REF CR ON AC.CHECKLIST_REF_ID = CR.CHECKLIST_REF_ID 
INNER JOIN SGT_APPLICATION APP ON APP.APPLICATION_ID = AC.APPLICATION_ID AND APP.APP_CATEGORY_VALUE = CR.APP_CATEGORY_VALUE AND APP.APP_SUB_CATEGORY_VALUE = CR.APP_SUB_CATEGORY_VALUE
LEFT JOIN SGT_APPLICATION_CHECKLIST SAC ON SAC.APPLICATION_ID = AC.APPLICATION_ID 
WHERE SAC.APPLICATION_ID IS NULL

Although theoretically NOT EXISTS seems to be the best option.

But why did you put those conditions between APP and CR inside the subquery? I moved them to the corresponding INNER JOIN, but you have to verify that works.

score 0 · Answer 2 · answered Jun 27 '16 at 19:53

I bet a left outer join will be more performant than Exists in this case.

DELETE  
    AC 
    FROM 
    SGT_APPLICATION_CHECKLIST AC
    INNER JOIN SGT_APPLICATION_CHECKLIST_REF CR ON        AC.CHECKLIST_REF_ID   =      CR.CHECKLIST_REF_ID 
    INNER JOIN SGT_APPLICATION APP ON APP.APPLICATION_ID = AC.APPLICATION_ID
    LEFT OUTER JOIN SGT_APPLICATION_CHECKLIST CR2 ON CR2.APPLICATION_ID = AC.APPLICATION_ID AND APP.APP_CATEGORY_VALUE = CR2.APP_CATEGORY_VALUE AND APP.APP_SUB_CATEGORY_VALUE = CR2.APP_SUB_CATEGORY_VALUE
    WHERE 
    NOT CR2.APPLICATION_ID IS NULL

score 0 · Answer 3 · answered Jun 27 '16 at 19:55

First of all move conditions APP.APP_CATEGORY_VALUE = CR.APP_CATEGORY_VALUE and APP.APP_SUB_CATEGORY_VALUE = CR.APP_SUB_CATEGORY_VALUE out of subquery because they have nothing to do with that subquery. You can join on many parameteres.

Join .... on A = B AND C = D

Now, about your question, assuming you have cleaned your subquery then do it like that

select/delete .... 
FROM 
SGT_APPLICATION_CHECKLIST AC
INNER JOIN SGT_APPLICATION_CHECKLIST_REF CR ON        AC.CHECKLIST_REF_ID   =      CR.CHECKLIST_REF_ID 
INNER JOIN SGT_APPLICATION APP ON APP.APPLICATION_ID = AC.APPLICATION_ID
.... other joins and changes i mentioned, etc...
LEFT JOIN SGT_APPLICATION_CHECKLIST as SAC 
ON SAC.APPLICATION_ID = AC.APPLICATION_ID
where SAC.APPLICATION_ID IS NOT NULL

to get only the records that joined with your SAC.

Alternate way of Not exists in SQL

3 Answers3