Subject Re: [firebird-support] not null in statement very slow
Author Arno Brinkman

> I have the following problem:
> in my database there are two tables (MOV_ORDERS and
> 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,

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.

Arno Brinkman

Firebird open source database (based on IB-OE) with many SQL-99 features :

Support list for Interbase and Firebird users :

Nederlandse firebird nieuwsgroep :