Hi again, Mitch.

I may be out of my mind, but a thought struck me that may work if you
use Firebird 2.0 (I'm still on 1.5, and I've actually never tried any
real query on 2.0).

Select D.Somecolumn, C.SomeColumn
from
(select E.ID from Deal E
where E.EditDate >= '12/1/2006'
union
select DS.Deal_ID from Discussion DS
where DS.CreateDate >= '12/1/2006'
union
select A.Deal_ID from Attachment A
where A.CreateDate >= '12/1/2006') as DT (ID)
join Deal D on DT.ID = D.ID
left join Discussion C on C.Deal_ID=D.ID
and not exists(select * from Discussion C2
where C2.Deal_ID=D.ID
and C2.ID > C.ID)
Order by 2

Now, this is how I hope derived tables work, only trying it will reveal
if it does or not. If it works, it should be possible to use indexes for
all tables (date indexes for the unioned tables and various indexes on
ID-columns for the other tables).

HTH,
Set

Mitchell Peek wrote:

> >If this truly is the statement you want, then I'm sorry to say that
> >there is no way to avoid NATURAL on D (at least I do not know of any way
> >to do so without creating a stored procedure).
>
> Hmmmm Stored Procedure is certainly not out of the question. That
> would be certainly be a plausible approach.
>
> >However, if there are
> >considerably less records with correct Deal_ID in discussion or
> >attachment than there are records created after 1 December, then you may
> >observe a noticeable speed increase if you eliminate the index for
> >CreateDate, e.g. like this:
>
> >Select D.Somecolumn, C.SomeColumn
> >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 >= '12/1/2006'
> >OR (Exists ( select 1 from discussion DS where DS.Deal_ID=D.ID AND
> >DS.CreateDate+0 >= '12/1/2006' ))
> >or (Exists ( Select 1 from Attachment A where A.Deal_ID=D.ID AND
> >A.CreateDate+0 >= '12/1/2006'))
> >Order by 2
>
> >(In my daily work, one of the most common sources of frustration is when
> >I do an ad-hoc query, makes a minor modification and then runs it
> >without checking the plan carefully enough. Often, a query I expected to
> >take a couple of seconds all of a sudden takes minutes due to use of an
> >additional not-too-selective index.)
>
>
> And herein lies the rub, At this point, there is almost NO data to speak
> of. I am trying to prepare for when there will be. I could simply
> eliminate the index on CreateDate... if indeed it slows the query. You
> first point is well taken... there could be fewer rows per deal_id then
> those created after certain time, although, I expect no considerably
> fewer. Actually, the time frame should small in this query, perhaps no
> more then a few days, and the number of rows created in that time frame
> should be less then a few hundred.
>
> Perhaps this will never become an issue, but the index on DEAL is the
> most important. Therefore, the Store procedure approach is probably the
> safer alternative.
>
> I will explore that.
>
> Thanks to all!
> Mitch