The way Firebird works (at least 1.5, I don't know much about Firebird 2.x), EXISTS <subselect> or IN <subselect> has to be executed for each row - I assume it is since the subselect in theory can be connected to the outer select giving various results for each iteration. As long as there is no constant to compare things to, the outermost table cannot avoid using NATURAL.

In your case, the only way to make things faster, is to write a stored procedure that first does the inner select (in a FOR SELECT loop), and then try to run an update for each iteration of the loop.

Sorry,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of nilsboedeker
Sent: 25. april 2008 07:46
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Optimze Query

Hi

i try:

> the In clause with Exists clause
>
> update article a set a.enabled=1
> where exists (select 1 from ARTICLE_CONTENTTREE_LINK al
> INNER JOIN SP_CT_VIDLIST_WITHROOT(104017) s ON
> (al.TREEID = s.RTREEID) where al.articleid=a.relid)
>
> regards
>
> Alexandre

No Index used...

Plan:
PLAN JOIN ((SP_CT_VIDLIST NATURAL)
PLAN (CONTENTTREE INDEX (RDB$PRIMARY2)), AL INDEX (RDB$PRIMARY52))
PLAN (A NATURAL)

Adapted plan:
PLAN JOIN ((SP_CT_VIDLIST NATURAL)
PLAN (CONTENTTREE INDEX (PK_CONTENTTREE)), AL
INDEXPK_ARTICLE_CONTENTTREE_LINK52))
PLAN (A NATURAL)
11 record(s) was(were) updated in ARTICLE

any more ideas or do you need more information?

with best regards

Nils