How to optimize DELETE .. NOT IN .. SUBQUERY in Firebird

Question

I've this kind of delete query:

DELETE 
FROM SLAVE_TABLE
WHERE ITEM_ID NOT IN (SELECT ITEM_ID FROM MASTER_TABLE)

Are there any way to optimize this?

score 4 · Answer 1 · edited May 15 '14 at 14:48

4

(NOT) IN can usually be optimized by using (NOT) EXISTS instead.

DELETE 
FROM SLAVE_TABLE
WHERE NOT EXISTS (SELECT 1 FROM MASTER_TABLE M WHERE M.ITEM_ID = ITEM_ID)

I am not sure what you are trying to do here, but to me this query indicates that you should be using foreign keys to enforce these kind of constraints, not run queries to cleanup the mess afterwards.

edited May 15 '14 at 14:48

Harriv

6,029
6
44
76

answered Sep 16 '12 at 08:00

Mark Rotteveel

100,966
191
140
197

Foreign key with ON DELETE CASCADE rule would do the job automatically. – Andrej Kirejeŭ Sep 16 '12 at 14:55
Foreign key would be optimal of course, but unfortunately it's too late in this case. – Harriv Sep 16 '12 at 19:58
Ok, but after you run this query, make sure that you add such a constraint (eg with ON DELETE CASCADE as suggested by Andrei). – Mark Rotteveel Sep 17 '12 at 17:04

Andrej Kirejeŭ · Accepted Answer · 2012-09-17T08:33:56.817

You can use EXECUTE BLOCK for sequential scanning of detail table and deleting records where no master record is matched.

EXECUTE BLOCK
AS
  DECLARE VARIABLE C CURSOR FOR
    (SELECT d.id
     FROM detail d LEFT JOIN master m
       ON d.master_id = m.id
     WHERE m.id IS NULL);
  DECLARE VARIABLE I INTEGER;
BEGIN
  OPEN C;
  WHILE (1 = 1) DO
  BEGIN
    FETCH C INTO :I;
    IF(ROW_COUNT = 0)THEN
      LEAVE;
    DELETE FROM detail WHERE id = :I;
  END
  CLOSE C;
END

How to optimize DELETE .. NOT IN .. SUBQUERY in Firebird

2 Answers2

Linked