ChrisM@Work (4/10/2013)

---

sqldba_newbie (4/9/2013)

---

In below query , in the exec plan i see nested loop join on TBFE table. This is executed for 25,000 times. I could force a hash join there and reduce the execution to one. Any other thoughts? I have listed total number of records for each table also.

SELECT 1 [Count Admitted],

F.ResCollAm,

F.REA,

PT.PTD,

V.EId,

N.NCReasonDescription ResidualNCR,

fep.RW,

RFCU.displayName RFC,

RLCU.DisplayName ResidualLastWorkedBy,

fep.CoverageCompleted,

FEP.PtC

FROM TBRv V --100938591

INNER JOIN TBRF F --100323642

ON V.Fcde = F.Fcde

AND V.RID = F.RID

AND V.Fcde IN ( @Fcde )

INNER JOIN tbDate D -- 65536

ON D.DateKey = V.AdmitDateKey

AND WeekBeginDate IN ( @DynamicWeek )

INNER JOIN tbType PT -- 9

ON V.Ptype = PT.Ptype

LEFT JOIN TBFe FEP -- 112357833

ON FEP.Fcde = V.Fcde

AND fep.RID = V.RID

LEFT JOIN tbUsers RFCU --22089

ON RFCU.UserID = fep.RFCUID

LEFT JOIN tbUsers RLCU --22089

ON RLCU.UserID = FEP.ResidualLastWorkedByUserID

LEFT JOIN tbNCRCode N --705

ON N.NCReasonCode = F.ResidualNCRCode

AND N.Fcde = V.Fcde

I don't see what's wrong with it - 25,000 inner-loop seeks with successful dynamic partition elimination. Perhaps it's the 35% relative cost which flags it up. I wonder what your memory grant would be if you forced a hash join. Have you tried it?