Quote Originally Posted by Magiaus
One more thing if you can explain a little better about how putting multi tables in the whewre clause is less efficiant than using Joins you may change my way of thinking. I know all about indexes btw, but currenly this db is a mess the relation may are may not be set same for index....
From a quick GOOGLE I can see articles pointing out that if you are fully aware of how the engine is going to handle the possible ambiguities of a FROM with multiple tables then use it - otherwise use the JOIN/ON clauses...

My copy of Inside SQL Server 2000 (by Kalen Delaney) is in my office - very much worth reading if you have time... It has some info on this.

I've helped colleagues with slow query issues in the past and discovered that the FROM/TABLES join method sometimes creates many more records in the working recordset that are collapsed later.

This is all vague and can only really be tried on your own data and tables in QA - seeing that execution plan makes all the difference.

Here's an example of how big our JOINS can get.

Code:
FROM Claim_T CL
LEFT JOIN Claim_T CL2 on CL2.ClaimEntry=CL.AssocClaimEntry
LEFT JOIN WelCheck_T WC on WC.CheckNum=CL.CheckNum
LEFT JOIN Master_T MT on MT.MasId=CL.MasId
LEFT JOIN Provider_T PR on PR.ProvId=WC.ProvId
LEFT JOIN Provider_T PR2 on PR2.ProvId=CL.ProvId
LEFT JOIN Master_T MT2 on MT2.MemberSSN=MT.MemberSSN and MT2.Affil=1
LEFT JOIN Addr_T AD on AD.MasId=MT2.MasId and AD.AddrTag = MT2.ResAddrTag
LEFT JOIN BeneType_T BT on BT.BeneType=CL.BeneType
LEFT JOIN ClaimType_T CT on CT.ClaimType=Cl.ClaimType
LEFT JOIN LetterType_T LT1 on LT1.LetterType=CL.LetterType1
LEFT JOIN LetterType_T LT2 on LT2.LetterType=CL.LetterType2
LEFT JOIN LetterType_T LT3 on LT3.LetterType=CL.LetterType3
LEFT JOIN Master_T MT3 on MT3.MasId=IsNull(WC.BenefId,0)
LEFT JOIN Master_T MT4 on MT4.MasId=WC.PayeeID
LEFT JOIN Addr_T AD2 on AD2.MasId=MT2.MasId and AD2.AddrTag = IsNull(MT4.ResAddrTag,0)
WHERE IsNull(PR.SingleChk,'N')<>'Y' AND
CL.AdjDate>=@Adjudication_Date and CL.AdjDate<=@Adjudication_Date and 
...
CLAIM_T table has 3 million rows. ADJDATE is an index. We wanted to allow nothing to come in the way of the fact that we are attaching just one days adjudicated claims (1000 or so).

We always use LEFT JOIN because we have legacy data in our tables that might not always follow full referential integrity rules. It's become a habit because of that fact.