Ann W. Harrison wrote:

> Err, _not_ quite. In fact the key is the "_not_". Since the
> beginning of (its) InterBase / Firebird have _not_ used
> indexes with _NOT_ conditions. Whether (or _not_) that's
> appropriate with expressions like NOT NULL is debatable.
> These are indexable:
>
> where x.a = 1
> where x.a > 10
> where x.a <= 10
> where x.a IS NULL
> where x.a between 1 and 10
>
> These are _not_
>
> where x.a NOT = 1
> where x.a NOT > 10
> where x.a NOT <= 10
> where x.a IS NOT NULL
> where x.a NOT between 1 and 10

Thanks for the crystal clear explanation Ann!

<rant>
This is yet another example of all those peculiarities you have to be
aware of when using Firebird.

When I write a query without really thinking hard about how to construct
it, what indices I *must* have, etc., to get *decent* performance, I
usually get *decent* results with SQL Server. With Firebird on the other
hand, I often get ridiculously bad performance.

Sure, the queries, indices etc. in Firbird can be tuned to match SQL
Server in most cases, and even outperform it from time to time. But why,
oh why, do I always have to spend so much time tinkering with the
Firebird queries to get "decent or better" performance?

I know all that about tradeoffs and that Firebird has its main focus on
handling thousands of concurrent users without causing locking problems,
but does this really *have* to imply that the query optimizer has to
fail to make the right decisions in so many situations?
</rant>

Thank you,
Kjell
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64