--- In firebird-support@yahoogroups.com, "Adam" wrote:

> --- In firebird-support@yahoogroups.com, "Maxisoft - Luciano
> Enzweiler" <enzweiler@s...> wrote:
> >
> > Hi.
> >
> > I made an update SQL but it takes too much time to execute, and
> > there seem to be no reason for that.
> >
> > The update is like this one:
> >
> > The field T1.CODTB is the Primary Key of TABLE1 and all the other
> > fields are integers, but none is indexed. Both tables have about
> > 2000 records.
> >
> > update TABLE1 T1 set T1.MARCA = 0 where (T1.MARCA = 1) and
> > (T1.CODTB not in (select T2.CODREF from TABLE2 T2
> > where (T2.CODREF is not null) and (T2.FILTRO = 1)))
>
> Your select example contains a fixed list, the update uses a query
> in the in statement, which is executed for every record.
>
> try the following, it may help.
>
> update TABLE1 T1 set
> T1.MARCA = 0
> where
> (T1.MARCA = 1) and
> not Exists(
> select T2.CODREF
> from TABLE2 T2
> where (T2.CODREF is not null
> and T1.CODTB <> T2.CODREF ) and (T2.FILTRO = 1))
> )

I think this should be:

update TABLE1 T1 set
T1.MARCA = 0
where
(T1.MARCA = 1) and
not Exists(
select *
from TABLE2 T2
where T1.CODTB = T2.CODREF and T2.FILTRO = 1)
)

i.e. change <> to =

I also removed the 'is not null' since null never matches the equality
test anyway, and didn't bother to 'subselect' any particular field
since the EXISTS part only returns true or false. The one index that
could help this query - if my assumption that CODREF is more selective
than FILTRO is correct - is an index on T2.CODREF.

HTH,
Set