At 09:39 PM 4/11/2008, you wrote:

>----------------
>As it looks for my DB:
>
>select
> i.CustomerID,
> (select cus.companyname from Customers cus
> where cus.ID = i.CustomerID) as Customer,
> max(c.communicationdate) Last_Comm_Date
>from customerincidents i
> join customercommunications c
> on c.IncidentID = i.id
>where (i.customerid = :ICustomerID)
>group by 1,2
>
>----------------
>My current query I'm using:
>
>select first 1 * /*Note * just here for test
>purposes - will be replacing)
>from customerincidents ci
> left outer join customercommunications cc
> on cc.incidentid = ci.id
>where (ci.customerID = :ICustomerID)
>order by cc.communicationdate desc, cc.id desc
>
>----------------
>
>Using IBExpert's performance analysis tool, both queries are reading
>through all comms and all incidents for my selected customer.
>I'm using one of my more talkative customers, who have 329 incidents and
>2458 comms and it's pretty quick 782 mille-seconds). I'm just worried
>that I'm going to cause speed issues sooner or later by reading through
>so many records.
>
>If I had the customer id in the comms table, this query could be reduced
>to only reading one record.

Have you ever heard of a WHERE clause? If you apply your query to the whole table, when you only one one record, of course the whole table will be traversed! And using select first * will tell you nothing about how long a searched query will take. The outer join has no use here; and using WHERE for JOIN criteria is wrong syntax. yoInclude a search clause and put indexes on your search keys and you will shock yourself.
select (whatever)
from customerincidents ci
left outer join customercommunications cc <-- outer join wasteful and useless here
on cc.incidentid = ci.id
where (ci.customerID = :ICustomerID)
order by cc.communicationdate desc, cc.id desc

>PS. I'm using FB 1.5. AFAIK using FB 2 won't affect index use at all
>(because there's still no index FB2 can use either)
>
>
>------------------------------------
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>Visit http://www.firebirdsql.org and click the Resources item
>on the main (top) menu. Try Knowledgebase and FAQ links !
>
>Also search the knowledgebases at http://www.ibphoenix.com
>
>++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>Yahoo! Groups Links
>
>
>