At 07:59 PM 10/03/2005 -0500, you wrote:

>:: It won't be better and it might be worse. DISTINCT queries
>
>That's what I thought.
>
>:: not. Of course, a correlated subquery is run for every row
>:: in the outer
>:: select. "NOT IN" queries can't use an index, either. So,
>
>Yes, this is what I was worried about. I was hoping that if the sub query
>was static - that it would "store" the results and just reuse, but I take it
>FB does not do that?
>
>:: does not have any of the features of an efficient query, not
>:: matter how you
>:: look at it.
>
>Yes. The problem is my alternative - and what Im doing now. What Im doing
>now is running the select sub query - then walking it manually and for EACH
>row reading a field, and then updating another...
>
>Not sure which is worse. :)
>
>Any suggestions?
>
>:: Any "NOT-ed" existential subquery will be costly but the
>:: least costly might be
>:: ...
>:: from atable A
>:: where not exists (
>:: select 1 from othertable B
>:: where B.z = A.z)
>
>Hmm - whats the not exists? How is that differnet from not in?

The optimizer resolves IN <subquery> predicates to EXISTS <set> anyway.

>What if I
>just did a EXISTS not not a NOT EXISTS? Any diff?

Yes. Different result by 180 degrees.

>:: Another approach is to avoid the row-by-row reprocessing of
>:: the subquery
>:: and instead use a left join to find the non-matches, which
>:: should utilise
>:: an index on B.z, if there is one, for the join search:
>
>I need to do an update - not sure I can do a join and an update? Ive tried
>this before, was not successful. Maybe I just couldn't get the syntax right
>though.

No, it won't be any use for an update. It wasn't clear what you wanted the
set for....

This sort of thing is most efficiently done in an executable SP, since you
won't have to continually requery the whole table and you can take
advantage of indexes to speed things up.

e.g. (guessing wildly about which side of the fence is to get the update)

declare z integer;
declare pk integer;
declare...etc....

for select pk, z, ..etc. from A
into :pk, :z, ..etc....
as cursor cr
do
begin
if (exists (select 1 from B
where z = :z)) then
begin
/* dummy branch - do nothing */
end
else
begin
update A
set....
where current of cr;
end
end

./hb