Mitch,

> After Anderson's post, I thought about replacing with a union. At

least

> it could use the index I think would help. Perhaps a Stored procedure
> might be even better.

My own review of you original post suggests:

1 - Do you have an index on Deal EditDate? (This could helps get rid of
the NATURAL scan)

2 - You should create a DESCENDING compound index in Discussion,
combining Deal_ID + ID). The optimizer should choose this index to
resolve the MAX() criteria.

3 - You should create compound indexes on Attachments and Discussions
(combining DealID + CreateDate). The optimizer should choose these
indexes over the current approach of trying to create a bit map by
combining the separate indexes (FK_DISCUSSION_1/IDX_DISCUSSION_2 and
FK_ATTACHMENT_2/IDX_ATTACHMENT_1).

4 - In addition to this, if you are running v2.0+, you might want to try
using the DERIVED Table syntax for you query. The query would be:

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 (
SELECT
D2.deal_id, Max(D2.ID)
FROM
discussion D2
GROUP BY 1)
) C2
LEFT Deal D ON d.id = C2.deal_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' ))

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

The DERIVED Table should help with this.

> I think the problem is more with fetches from unnecessary Discussion
> rows (perhaps not to the client, that confuses me somewhat, but
> certainly ibexpert shows many row fetches that I know are unnecessary
> and do not make it to the final result).

The Row Fetches are being executed within the engine.


Sean