At 04:20 PM 20/05/2009, you wrote:

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

You don't show the query you used with NOT EXISTS, but it should be:

DELETE FROM ST_TR_FIFO stf
WHERE NOT EXISTS (
select 1 from ST_TR st
where st.TRANSNO = stf.TRANSNO)

It will be faster, but it's still a bad way to target records in a biggish table.

A more efficient way to do this will be in an executable stored procedure looping through a cursor and doing a positioned delete, viz.

create procedure delete_my_stuff as
declare variable stf_transno BigInt = 0; -- or integer, or whatever it is
declare variable is_present smallint;
begin
for select transno from ST_TR_FIFO
for update
into :stf_transno
as cursor acursor do
begin
is_present = 0;
if (exists (select 1 from ST_TR
where TRANSNO = :stf_transno)) then
is_present = 1;
if (is_present = 0) then
delete from ST_TR_FIFO
where current of acursor;
end
end

NB The above (original) syntax works in all Fb versions. In v.2 and above, there's an enhanced cursor syntax that also supports positioned deletes and updates.

./hb