Maybe UNION could help (speedwise)?

Select D.ID, Cast(D.CreateDate as Date) as OrigDate,
Cast(D.Editdate as Date) as Lastedited,
D.EditUser, D.StockName, D.Status, D.Buyer, D.Bidamt ,
Cast(c.Createdate as Date) as LastComment, C.Author,
D.StockNumber, D.SR
from Deal D
left join Discussion C on C.Deal_Id=D.Id
and C.Id=(Select Max(C2.ID) from discussion C2
where C2.deal_id=d.id)
where D.editDate >= '3/1/2008'
union
Select distinct D.ID, Cast(D.CreateDate as Date) as OrigDate,
Cast(D.Editdate as Date) as Lastedited,
D.EditUser, D.StockName, D.Status, D.Buyer, D.Bidamt ,
Cast(c.Createdate as Date) as LastComment, C.Author,
D.StockNumber, D.SR
from Deal D
join discussion DS on DS.Deal_ID=D.ID
left join Discussion C on C.Deal_Id=D.Id
and C.Id=(Select Max(C2.ID) from discussion C2
where C2.deal_id=d.id)
where DS.CreateDate >= '3/1/2008'
union
Select distinct D.ID, Cast(D.CreateDate as Date) as OrigDate,
Cast(D.Editdate as Date) as Lastedited,
D.EditUser, D.StockName, D.Status, D.Buyer, D.Bidamt ,
Cast(c.Createdate as Date) as LastComment, C.Author,
D.StockNumber, D.SR
from Deal D
join Attachment A on A.Deal_ID=D.ID
left join Discussion C on C.Deal_Id=D.Id
and C.Id=(Select Max(C2.ID) from discussion C2
where C2.deal_id=d.id)
where A.CreateDate >= '3/1/2008'

(Actually, I've never tried using DISTINCT with UNION statements, just hope it works - for all I know, it might even be redundant)

For this to be worth trying, there must be considerably more rows in Deal than there are records in Discussion or Attachment with CreateDate >= '3/1/2008'.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Mitch Peek
Sent: 12. mars 2008 22:48
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Plan Syntax

Query

Select D.ID, Cast(D.CreateDate as Date) as OrigDate, Cast(D.Editdate as
Date) as Lastedited,
D.EditUser, D.StockName, D.Status, D.Buyer, D.Bidamt ,
Cast(c.Createdate as Date) as LastComment, C.Author,
D.StockNumber, D.SR
from Deal D
left join Discussion C on C.Deal_Id=D.Id
and C.Id=(Select Max(C2.ID) from discussion C2
where C2.deal_id=d.id)
Where
D.editDate >= '3/1/2008'

OR
(Exists ( select 1
from discussion DS
where DS.Deal_ID=D.ID
AND DS.CreateDate >= '3/1/2008' ))
OR
(Exists ( Select 1
from Attachment A
where A.Deal_ID=D.ID
AND A.CreateDate >= '3/1/2008' ))


IbExpert shows the following

Plan
PLAN (C2 INDEX (FK_DISCUSSION_1))
PLAN (DS INDEX (FK_DISCUSSION_1,IDX_DISCUSSION_2))
PLAN (A INDEX (FK_ATTACHMENT_2,IDX_ATTACHMENT_1))
PLAN JOIN (D NATURAL,C INDEX (PK_DISCUSSION))

Adapted Plan
PLAN (C2 INDEX (FK_DISCUSSION_1)) PLAN (DS INDEX
(FK_DISCUSSION_1,IDX_DISCUSSION_2)) PLAN (A INDEX
(FK_ATTACHMENT_2,IDX_ATTACHMENT_1)) PLAN JOIN (D NATURAL,C INDEX
(PK_DISCUSSION))

If I change [D Natural] to [D INDEX (IDX_DEAL_1)] the performance would
increase dramatically. However, I can't get the syntax correct.
Can anyone help please?

TIA
MP