Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Query - Nested Loop Join Expand / Collapse
Author
Message
Posted Tuesday, April 9, 2013 5:50 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 1,274, Visits: 2,936
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







  Post Attachments 
Test.sqlplan (6 views, 212.27 KB)
Post #1440615
Posted Tuesday, April 9, 2013 6:08 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:44 AM
Points: 66, Visits: 437
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.
Post #1440618
Posted Tuesday, April 9, 2013 6:10 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:01 PM
Points: 22,980, Visits: 31,456
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.



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)
Post #1440619
Posted Tuesday, April 9, 2013 7:01 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 1,274, Visits: 2,936
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
Post #1440623
Posted Tuesday, April 9, 2013 10:36 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:01 PM
Points: 22,980, Visits: 31,456
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.




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)
Post #1440642
Posted Wednesday, April 10, 2013 3:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:53 AM
Points: 7,099, Visits: 13,445
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
Post #1440705
Posted Wednesday, April 10, 2013 6:51 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 1,274, Visits: 2,936
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.
Post #1440778
Posted Wednesday, April 10, 2013 6:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:53 AM
Points: 7,099, Visits: 13,445
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
Post #1440780
Posted Wednesday, April 10, 2013 7:19 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:01 PM
Points: 22,980, Visits: 31,456
And please post the rest of the information requested.



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)
Post #1440797
Posted Friday, April 12, 2013 7:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 12, 2013 7:55 AM
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.
Post #1441714
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse