Hi,

> 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

How about a LEFT JOIN:
select c.name, c.surname, c.telephone, c.lastorderdate, o.customerlink
from customers c left join orders o on (o.customerlink = c.link
and o.recdate > '01-jan-2004' and o.recdate < '01-feb-2004' and o.branchcode
= 'SAN')
where c.branchcode = 'SAN' and o.customerlink is null
order by c.lastorderdate desc,1,2

I'm not sure if this returns the OK result, but give it a try :-)

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com