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

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.

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