At 07:12 AM 23/10/2007, you wrote:

>Hi Set,
>
>> I don't (yet) agree with Adam and don't think there's any point in
>> changing to a NOT EXISTS ('yet' indicating that I don't use Fb 2.0
>> myself, and that he might have knowledge that I have missed).
>
>Check the Firebird 2 release notes printed page number 102.
>
>[quote]
>Performance
>
>The following changes should be noted as possible sources of
>performance loss:
>
>Existence Predicates NOT IN and ALL May Be Slow
>
>Firebird and, before that, InterBase, have produced incorrect results
>for the logical existence predicates ALL and NOT IN for many years.
>That problem has bee corrected in Firebird 2.0, but the change means
>that indexes on the inner tables cannot be used and performance may be
>slow compared to the same query's performance in V.1.5. "Inner tables"
>are the tables used in the subquery argument inside an ALL or NOT
>IN expression.
>
>Note
>
>NOT EXISTS is approximately equivalent to NOT IN and will allow
>Firebird to use indexes.
>
>[/quote] (sic)

Certainly that change is applicable to NOT IN (<subquery>) predicates. NOT IN(<list_of_constants>) never could use an index and still doesn't.

Even though Richard thinks he has resolved the performance problem by indexing the zipcode field(s), it doesn't address a worse one: the use of floating-point values as join criteria or, indeed, any equivalence search such as the pivot in what he refers to as "dimensional data". Such keys need to be stored as fixed types to avoid a (quite unknown) number of false negatives.

./heLen