The orders table hold about 5 million records and the customers table
500000.
The 'SAN' clause filters about 100000 order records and 5000 customers.
I need to find a group of customers who have not placed and order in a
specified period.

The following query takes 44 seconds to return.

select
c.name, c.surname, c.telephone, c.lastorderdate
from customers c
where c.link not in (select customerlink from orders where recdate >
'01-jan-2004' and recdate < '01-feb-2004' and branchcode = 'SAN')
and c.branchcode = 'SAN'
order by
c.lastorderdate desc,1,2

PLAN SORT ((C INDEX (IDX_CUSTOMERS_BRANCHCODE)))
PLAN (ORDERS INDEX (ORDERS_CUSTOMERLINK,ORDERS_RECDATE))

Does firebird run the IN query for every customer record even when there is
no reference to the left table in the where clause?
Is there no other way to do this other than resorting to this, which returns
in 0.5 seconds?

select
c.name, c.surname, c.telephone, c.lastorderdate, coalesce(
sum(o.ordertotal),0)
from customers c
left outer join orders o on o.customerlink = c.link and o.recdate >
'01-jan-2004' and o.recdate < '01-feb-2004'
where
c.branchcode = 'SAN'
group by
c.name, c.surname, c.telephone, c.lastorderdate
having coalesce(sum(o.ordertotal),0) = 0
order by
c.lastorderdate desc,1,2

PLAN SORT (SORT (JOIN (C INDEX (IDX_CUSTOMERS_BRANCHCODE),O INDEX
(ORDERS_CUSTOMERLINK))))

TIA,
Gary