如何优化这个sql delete语句

I have 3 tables, the first one, table1, has as primary key the id column, the second table (table2) has a column table1_id that refer as foreign key to the table1.id, the third table (table3) has, as table2, a column table1_id that refer as foreign key to table1.id.

我有3个表,第一个,table1,作为主键的id列,第二个表(table2)有一个列table1_id,作为table1.id的外键引用,第三个表(table3)有,作为table2 ,一个列table1_id,它作为table1.id的外键引用。

I have to delete from table1 all the rows where table1.id is not in table2.table1_id and not in table3.table1_id

我必须从table1中删除table1.id不在table2.table1_id中而不在table3.table1_id中的所有行

now i am using this query:

现在我正在使用此查询:

DELETE FROM table1
WHERE  table1.id IN (SELECT table1.id
                     FROM   (table2
                             RIGHT OUTER JOIN table1
                               ON table2.table1_id = table1.id)
                            LEFT OUTER JOIN table3
                              ON table3.table1_id = table1.id
                     WHERE  table2.table1_id IS NULL
                            AND table3.table1_id IS NULL);  

but it is very slow, it takes a lot of time, there are some better approach to this delete statement?

但它很慢,需要花费很多时间,还有一些更好的方法来删除这个删除语句?

If this can help i can assume that table2 has more data that table3.

如果这可以帮助我可以假设table2有更多数据表table3。

The database i am using is Apache Derby.

我使用的数据库是Apache Derby。

Thanks for the help.

谢谢您的帮助。

4 个解决方案

#1

Assuming you got the obvious covered (indices created for table1.id, table2.table1_id and table3.table1_id), you don't need to perform full outer joins just to test if a key is in another table, you can use subqueries and exists() -- or not exists() in your case.

假设您有明显的覆盖(为table1.id,table2.table1_id和table3.table1_id创建的索引),您不需要执行完全外连接只是为了测试一个键是否在另一个表中,您可以使用子查询并且存在() - 或者不存在()在你的情况下。

And since you're only testing for existence, you can use the following pattern:

由于您只是测试存在,您可以使用以下模式:

where not exists ( select top 1 1 from... where... )

#2

DELETE     table1
FROM       table1
LEFT JOIN  table2 ON table1.id = table2.table1_id
LEFT JOIN  table3 ON table1.id = table3.table1_id
WHERE table2.table1_id IS NULL
  AND table3.table1_id IS NULL

#3

Do you know how many rows you are deleting? I agree with @Blindy, that not exists would probably be better in your case if Derby supports it (I don't know Derby so I can't say for sure). However, if there are a lot of records being deleted, you might want to do this in batches. Deleting a 10,000,000 records is going to take a long time no matter how efficent the query is. Deleting them in a loop that does 1000 at a time is often better for the database as it won't take a table lock and lock out users while the whole process is done. Again I don't know Derby, so I don't know if this is true of Derby, but it certainly would help a large delete in most databases I am familiar with.

你知道你要删除多少行?我同意@Blindy,如果Derby支持它,那么在你的情况下不存在可能会更好(我不知道Derby所以我不能肯定地说)。但是,如果有大量记录被删除,您可能希望批量执行此操作。无论查询多么有效,删除10,000,000条记录都需要很长时间。在一次循环中删除它们的次数通常对于数据库来说更好,因为它不会占用表锁并在整个过程完成时锁定用户。我再也不知道Derby,所以我不知道Derby是否属实,但它肯定会帮助我熟悉的大多数数据库中的大型删除。

#4

DELETE from table1
WHERE 
   table1_id NOT IN (SELECT table1_id FROM table2)
   AND
   table1_id NOT IN (SELECT table1_id FROM table3)