SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query - Nested Loop Join


Query - Nested Loop Join

Author
Message
curious_sqldba
curious_sqldba
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2950 Visits: 3637
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






Attachments
Test.sqlplan (9 views, 212.00 KB)
sharky
sharky
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 441
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
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39970 Visits: 38564
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
curious_sqldba
curious_sqldba
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2950 Visits: 3637
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
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39970 Visits: 38564
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16472 Visits: 19557
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
Exploring Recursive CTEs by Example Dwain Camps
curious_sqldba
curious_sqldba
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2950 Visits: 3637
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
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16472 Visits: 19557
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
Exploring Recursive CTEs by Example Dwain Camps
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39970 Visits: 38564
And please post the rest of the information requested.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
durayakar
durayakar
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 0
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search