Hi,

> I have the following problem:
>
> in my database there are two tables (MOV_ORDERS and
> MOV_ORDERS_POSITIONS).
> I fetch records from both tables and check in my query if the deliver
> date is not null. Something like this:
> select .. from . where (ID=:ID) and (DELDATE is not null)
> The problem is that this check for "not null" needs a very long time
> to get the results. The query is much faster when try this:
> select ... from . where (ID=:ID) and ((DELDATE >= '31.12.1899') and
> (DELDATE <= '31.12.2004'))
>
> Both statements return the same result but the first one needs ten
> times as long as the second one. How come?

Then you've a index on DELDATE and also you've many NULLs in that table,
right?

NULLs are also stored in the index and when you filter on NOT NULL is mostly
unefficient to use a index for this. The "current" behaviour for FB1.5 (and
earlier) is that no index is used if your condition contains NOT NULL. Thus
indeed a BETWEEN (same as higher-and-equal and lower-and-equal condition) is
faster in the situation that you've many NULLs.

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