Geek asks and answers

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

I have a delete request like this:

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

Is there any way to optimize this?

+5
source share
2 answers

You can use EXECUTE BLOCK to sequentially scan the detail table and delete records in which the master record is not mapped.

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
+2
source

(NOT) INusually can be optimized with (NOT) EXISTS.

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

I'm not sure what you are trying to do here, but for me this query indicates that you should use foreign keys to enforce such restrictions, and not run queries to clean up after that.

+3
source

More articles:


All Articles