Okay, this ought to be interesting :)

This is going to take more than one pass, methinks, but let's see how it goes.

First, why doesn't it start the plan at mWebAuthattrib? Is there not an index that starts with AuthAttr_Category? Is this not selective enough? If not, what % of the table does each represent.

On that thought - what are the average numeric relations between the tables? M:W 1:8, etc.

The fact that the main column is called work_id everwhere but AI has me a litle concerned about the design, but that's a separate issue.

Another thought - this query looks significantly different than the first one. Why? If you change the WHERE clause at all, the plan can change.

Oops, gotta run, but try this:

SELECT
---M.Mat_ID,
---M.Mat_Res_ID,
---W.Work_Description,
---M.Mat_Amount,
---MC.MatCat_Rate---*---AAR.AuthAttr_Amount,
---M.Mat_Date,
---M.Mat_Amount---*---MC.MatCat_Rate---*---AAR.AuthAttr_Amount,
---R.Res_Name,
---AAC.AuthAttr_Amount
FROM
---mWebMatrix------------M---,
---mWebWork---------------W---,
---mWebAuthAttrib---------AAR,
---mWebAuthAttrib---------AAC,
---mWebRes---------------R---,
---mWebMatrixCategory---MC---,
---mWebAttribInt---------AI
WHERE
---AAR.AuthAttr_Category---=---------153 ------------------------AND

---AAC.AuthAttr_Category---=---------152------------------------AND
---AAC.AuthAttr_Work_ID------=---------AAR.AuthAttr_Work_ID---------AND
---AAC.AuthAttr_Res_ID------=---------AAR.AuthAttr_Res_ID---------AND

---M.Mat_Work_ID------------=---------AAR.Mat_Work_ID------------AND
---M.Mat_Work_ID------------=---------AAC.Mat_Work_ID------------AND
---M.Mat_Res_ID ------------=---------AAR.AuthAttr_Res_ID---------AND
---M.Mat_Res_ID ------------=---------AAC.AuthAttr_Res_ID---------AND
---M.Mat_Date---------------BETWEEN---AAR.AuthAttr_Start_Date------AND
---------------------------AND------AAR.AuthAttr_Finish_Date---AND
---M.Mat_Date---------------BETWEEN---AAC.AuthAttr_Start_Date------AND
---------------------------AND------AAC.AuthAttr_Finish_Date---AND

---W.Work_ID---------------=---------AAR.AuthAttr_Work_ID---------AND
---W.Work_ID---------------=---------AAC.AuthAttr_Work_ID---------AND
---W.Work_ID---------------=---------M.Mat_Work_ID---------------AND

---AI.AtInt_Ent_ID---------=---------AAR.AuthAttr_Work_ID---------AND
---AI.AtInt_Ent_ID---------=---------AAC.AuthAttr_Work_ID---------AND
---AI.AtInt_Ent_ID---------=---------M.Mat_Work_ID---------------AND
---AI.AtInt_Ent_ID---------=---------W.Work_ID------------------AND

---R.Res_ID------------------=---------AAR.AuthAttr_Res_ID---------AND
---R.Res_ID------------------=---------AAC.AuthAttr_Res_ID---------AND
---R.Res_ID------------------=---------M.Mat_Res_ID---------------AND

---M.Mat_Category------------=---------MC.MatCat_ID

First, change '---' to a 3-char tab and everything will line up wonderfully :)

My first rule of optimization is 'join everything to everything' as silly as it sounds, it solves the majority of the problems I come across. I don't have time to go into why, but check my logic to make sure I got all the joins right and then let me know if the plan changes. Also fill me in on the other questions I asked, if you would.

Thanks

- Chris