--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

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

Thanks Adam and Svein for the answers. I tried the changes you
suggested, but it kept taking the same 20 seconds. But when I defined
the T2.CODREF as an index, as Svein said, it started to run
instantaneously. The only problem is that on the subselect, in truth I
have to make a JOIN with a third table in order to add another filter.
The join is between two indexed fields, but when I add this JOIN, the
query takes more than 20 seconds again. I simplified the SQL here
because the main problem is, as Adam said, it executes the subselect
for each record, and I was wondering if there was another way to do
that. I didn't want to use a stored procedure, because I'll have to
make one for each SQL like this, and I'll need many of them. Do you
think there is a way to force the subselect to execute only once or
something like this in order to make it run faster?

Thanks again,

Luciano Enzweiler.