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?

Answer 1

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

Answer 2

(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.