Query - Nested Loop Join

  • 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

  • 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.

  • 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 (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

  • 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.

  • 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

  • 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.

  • 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

  • And please post the rest of the information requested.

  • 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.

  • 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"

  • 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

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

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