Hi,

> I have a query
>
> Select .....
> Where z not in
> ( select .....)
>
> There are a few duplicates in the sub query, but not many. I know a
> disctinct is slower, but in this caes it would possibely speed up the first
> select? Which would be a "general" better strategy. To add distinct to the
> subquery or no?

Adding DISTINCT into the sub-query doesn't speed up the query, in fact it will slow down the query
(how much is depending on if an index can be used or not). Internally the IN predicate is managed
the same as an EXISTS predicate.

SELECT
*
FROM
TableZ z
WHERE
NOT EXISTS(SELECT 1 FROM TableY y WHERE z.Z = y.Y)

The exists returns immediatly when it evaluates to true, thus it doesn't check on duplicates. When a
DISTINCT is added the sub-query resultset needs first to be sorted which is more expensive.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81





--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 266.6.4 - Release Date: 7-3-2005