You wrote:

>Hi,
>
>At September 9, 2004, 05:54, Gary Benade wrote:
>
> >>Martijn Tonies
> >>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
> > PLAN SORT (JOIN (C INDEX (IDX_CUSTOMERS_BRANCHCODE),O INDEX
> > (ORDERS_CUSTOMERLINK,ORDERS_RECDATE)))
> > That query executes in 46 seconds
>
> >> Daniel Rail
> >> 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
> > PLAN SORT ((C INDEX (IDX_CUSTOMERS_BRANCHCODE)))
> > PLAN (ORDERS INDEX (ORDERS_CUSTOMERLINK,ORDERS_RECDATE))
> > This also takes 45 seconds?
>
> > Martijn and Daniel, I think there may be an issue with PLAN chosen. I am
> > going to try some manual plans and see what happens if I cant figure out
> > Lucs idea

At 07:34 AM 9/09/2004 -0300, Daniel Rail wrote:

>I have a few questions:
>
>How many records are returned by this query?
>
>Which version of Firebird are you using?
>
>Also, try the query without the ORDER BY clause, just to see if it's
>not the PLAN SORT that is the bottleneck of your query.

And, after doing that, change the main search clause to:

and (c.branchcode = 'SAN' OR 1=1)

(on the suspicion that you have a horrible long dup chain on that index)

./hb