Hi,

At September 9, 2004, 04:24, Gary Benade wrote:

> 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?

Yes.

> Is there no other way to do this other than resorting to this, which returns
> in 0.5 seconds?

Try:

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


--
Best regards,
Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)