Query - Nested Loop Join

Question

Query - Nested Loop Join

curious_sqldba

SSC-Dedicated

Points: 36494
More actions
April 9, 2013 at 5:50 pm

#394554

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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

sharky Default port Points: 1494 More actions · Answer 1

Nested loop joinis not a bad thing, and will be more effective than a hash join. A hash join will require an in memory creation of a hash table which will be very ineffective. As a rule I will never change a join recommended by the query engine. If the statistcs are correct it will almost allways be the correct choice.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

To really help, read the second article I reference below in my signature block regarding asking for help on performance issues. Follow the instructions in that article on what you need to post and how to do it.

There really isn't enough information in your post to really help you.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 3

Lynn Pettis (4/9/2013)
To really help, read the second article I reference below in my signature block regarding asking for help on performance issues. Follow the instructions in that article on what you need to post and how to do it.
There really isn't enough information in your post to really help you.

Actual Exec plan is attached. Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

sqldba_newbie (4/9/2013)
Lynn Pettis (4/9/2013)
To really help, read the second article I reference below in my signature block regarding asking for help on performance issues. Follow the instructions in that article on what you need to post and how to do it.
There really isn't enough information in your post to really help you.
Actual Exec plan is attached. Thanks

Part of what we need. Still need the DDL for the for the tables (and possibly the views) and the indexes defined on them.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 6

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?

25000 inner loops is the issue. I could easily force it to use hash and make it perform better. I think the order in which the joins are done is bad. I am looking for some assistance in changing the order.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

sqldba_newbie (4/10/2013)
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?
25000 inner loops is the issue. I could easily force it to use hash and make it perform better. I think the order in which the joins are done is bad. I am looking for some assistance in changing the order.

Can you force a hash join and post the actual plan, please? I'd be very interested to see it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

And please post the rest of the information requested.

durayakar Newbie Points: 7 More actions · Answer 9

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

I think there is some room for improvement in the above section.

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 10

durayakar (4/12/2013)
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
I think there is some room for improvement in the above section.

Can you please tell me what you "think"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

curious_sqldba (4/10/2013)
25000 inner loops is the issue.

Why?

I could easily force it to use hash and make it perform better.

Have you tested that and seen that forcing a hash join improves performance in all cases?

I think the order in which the joins are done is bad. I am looking for some assistance in changing the order.

Order of joins won't affect the type of join chosen, unless you go forcing join types that is. Are your stats correct and your row estimates accuate?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass