Svein Erling Tysvær wrote:

> Maybe UNION could help (speedwise)?
>
>

It did about a 33% improvement.

The 3rd segment of the union still produces a natural scan of Deal. It
uses the index on editDate in the first segment (GOOD), it uses the PK
of deal in the second segment. Good (I think).. I don't know why, but
it did not use and index for deal in the 3rd segment. I don't understand
why it wouldn't be a plan very similar to the second segment.

I changed the 3rd segment by moving the where condition into the join
and it then does use an index and I reduced the time another 1/3 from
the original.

> 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'
>
> 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'.
>
>

Hmmm, what you say can be interpreted in different ways.

There are significantly more deal rows (in total) then there are
attachments or deals where either of the last 2 were created after 3/1.
However, there are more attachments and discussion rows then deal rows
all 3 of which were created after 3/1.

I appreciate all the help. I think I can live with the performance at
the present. It isn't snappy, but it isn't unusable at this point either.

I think this has become more of an academic exercise then one of
practicality for me. Perhaps I should just write the SP and be done
with it, but, one must consider the duplicated rows that would be
difficult to eliminate in the SP itself and therefore, always must be
called using Distinct.

Still, I will probably be back one day asking for advice specifying
plans. That question never was addressed. Simple queries are easy
syntactically, more complex queries, particularly with nested selects)
and I can't figure out the syntax.

Thanks to all who replied...
MP