I guess it is the third segment that makes this still slow. What I
expected (for that segment) was a plan like

PLAN(JOIN(JOIN(A INDEX(<CreateDateIndex>), D(<PKIndex>)), C(<Deal_IDIndex>))

Of course it can choose a non-optimal plan, so try changing one line and
test the speed again:

join Attachment A on A.Deal_ID+0=D.ID

This change virtually forces the optimizer to put A before D in your
plan (having D before A in the plan prevents the PK of D from being used).

HTH,
Set

Mitch Peek wrote:

> 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