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?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden