| Subject | How to optimize my DELETE using subquery? |
|---|---|
| Author | Chau Chee Yang |
| Post date | 2009-05-20T06:20:18Z |
Hi,
I have 2 tables
1. ST_TR (Row count: 155,810)
2. ST_TR_FIFO (Row count: 6,963,241)
When I execute
DELETE FROM ST_TR_FIFO
WHERE TRANSNO NOT IN (SELECT TRANSNO FROM ST_TR)
It takes 4 minutes to finish the query. Both tables has index of the fields that is necessary.
I know using "NOT IN" is slow in Firebird 2.1. But is there any better way to make the query run faster? I also try "NOT EXIST" but it doesn't help much.
Thank you very much.
I have 2 tables
1. ST_TR (Row count: 155,810)
2. ST_TR_FIFO (Row count: 6,963,241)
When I execute
DELETE FROM ST_TR_FIFO
WHERE TRANSNO NOT IN (SELECT TRANSNO FROM ST_TR)
It takes 4 minutes to finish the query. Both tables has index of the fields that is necessary.
I know using "NOT IN" is slow in Firebird 2.1. But is there any better way to make the query run faster? I also try "NOT EXIST" but it doesn't help much.
Thank you very much.