Hi Maya!

If my select returned several rows, I would expect that to mean that there are several records in customercommunications with the same (max) date for a particular customer. The difference between that and your sql, is that I hoped my sql to be able to return more data from the latest communication, not just the date. Sorry to hear that it took so long to run, I think I play to little with Firebird 2.x at home. Though it would be interesting to see the plan.

Nice to see that you got your adapted SQL to work. Did you try your original sql to see whether the speed improvement was due to improvements in Firebird/that you ran locally or if it was due to the sql change to include WITH? If you just want to know max(c.communicationdate) from the table, then I'd be surprised if anything could be much faster than what Helen suggested (modified to return only one customer, of course).

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Maya Opperman
Sent: 5. november 2008 07:13
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Possible to have an index across 2 tables?

Hi Set,

OK, I've finally got everything sorted for testing the WITH idea, and here are the results:

I'm not quite sure what your query was attempting to do, but have adapted it as follows:

WITH INSTEMP(INCIDENTID) AS
(SELECT I.ID FROM customerincidents I WHERE I.CUSTOMERID = :ICustomerID)
SELECT max(C.communicationdate)
FROM customercommunications C
JOIN INSTEMP IT ON C.INCIDENTID = IT.INCIDENTID

PLAN JOIN (IT I INDEX (CUSTOMERINCIDENTS_IDX1), C INDEX (CUSTOMERCOMMUNICATIONS_IDX2))

Takes 63 milliseconds to run (local connection though!)
Still reads exactly the same number of records as my FB 1.5 solution though (ie. all incidents and comms for that customer)

PS. Your original query returned more than one record, and took several seconds to run

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: 04 November 2008 12:07 PM
To: 'firebird-support@yahoogroups.com'
Subject: RE: [firebird-support] Possible to have an index across 2 tables?

Another option could be (if interested mainly in Comms and one particular customer):

WITH INSTEMP(INCIDENTID) AS
(SELECT I.ID FROM INCIDENTS I WHERE I.CUSTOMER_NAME = :NAME)

SELECT C.<Whatever>
FROM COMMS C
JOIN INSTEMP IT ON C.INCIDENTID = IT.INCIDENTID
WHERE NOT EXISTS(
SELECT NULL FROM COMMS C2
JOIN INSTEMP IT2 ON C2.INCIDENTID = IT2.INCIDENTID
WHERE C.xDATE < C2.xDATE)

Now, I'm still on Firebird 1.5 and WITH is only available in Firebird 2.1 and above (I think). If the optimizer choose a plan similar to:

PLAN JOIN(IT2 I INDEX(IDX_CUSTOMERNAME), C2 INDEX(INCIDENTID))
PLAN JOIN(IT I INDEX(IDX_CUSTOMERNAME), C INDEX(INCIDENTID))

then this could be reasonably fast. Unfortunately, I have no idea whether the optimizer in Fb 2.1 or 2.5 choose something like this or not. It would be nice if you tried and reported back.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: 4. november 2008 09:17
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Possible to have an index across 2 tables?

At 06:53 PM 4/11/2008, you wrote:

>Hi Set,
>
>>I don't quite grasp your structure, so it is difficult to give sound
>advice. How is COMMUNICATIONS and INCIDENT defined, what is their
>>relationship and approximately how many records are there within each
>table and for each customer?
>Just answered in reply to Helen's mail, but in short:
>
>Table Records
>Customers 15000 PK ID
>Incidents 150000 PK ID, CustomerID references Customers.ID
>Comms 1500000 PK ID, IncidentID references Incidents.ID
>
>Need to extract max date in Comms per Customer

So what you said before isn't quite correct. You have a 1:1 relationship between an Incident and a Customer, and a 1:1 relationship between a Comm and an Incident. That won't let your comms records get confused by customer.

select
i.CustomerID,
(select cus.Customer_name from Customers cus
where cus.CustomerID = i.CustomerID) as Customer,
max(c.xDate) Last_Comm_Date
from Incidents i
join Comms.c
on c.IncidentID = i.Incident_id
group by 1,2

./heLen