"No Join Predicate" Warning Message

  • I was troubleshooting an issue in production and stumbled upon an issue I have not seen before. I found a view that is joining 6 tables to a base table (1 inner join and 5 outer joins). The last outer join is causing a "No Join Predicate" warning in the "Nested Loop", yet there is obviously a join predicate in the code. Here's an example of the FROM clause in the view (table names and field names have been replaced)

    FROM

    dbo.TableS s

    INNER JOIN dbo.TableC c ON s.SID = c.SID

    LEFT OUTER JOIN DMDB.dbo.vw_1 u1 ON u1.UID = s.DCBUID

    LEFT OUTER JOIN DMDB.dbo.vw_2 u2 ON u2.UID = s.FBUID

    LEFT OUTER JOIN dbo.TableSM sm ON sm.LSID = s.SID

    LEFT OUTER JOIN DMDB.dbo.TableDIPs dips ON dips.DID = sm.RDID

    LEFT OUTER JOIN dbo.TableCMI cmi ON cmi.DID = s.SD

    The last line

    LEFT OUTER JOIN dbo.TableCMI cmi ON cmi.DID = s.SD

    is the issue.

    After adding an index to fix a scan on TableCMI and still seeing the same issue, I did some reading online and found that this case is often caused by using functions inside of views. None of the views being referenced are complicated or contain functions and the FROM clause above is pretty straight forward.

    Just on a whim I moved the last line to the third line like this...

    FROM

    dbo.TableS s

    INNER JOIN dbo.TableC c ON s.SID = c.SID

    LEFT OUTER JOIN dbo.TableCMI cmi ON cmi.DID = s.SD

    LEFT OUTER JOIN DMDB.dbo.vw_1 u1 ON u1.UID = s.DCBUID

    LEFT OUTER JOIN DMDB.dbo.vw_2 u2 ON u2.UID = s.FBUID

    LEFT OUTER JOIN dbo.TableSM sm ON sm.LSID = s.SID

    LEFT OUTER JOIN DMDB.dbo.TableDIPs dips ON dips.DID = sm.RDID

    And that fixed the issue. The query plan actually changed and there was no longer a warning on the "Nested Loop" operation saying there was "No Join Predicate"

    I'm baffled. Can anyone explain this to me?

    Thanks!

  • On a guess, the optimizer timed out trying to unpack the views and the "best" plan at the time of the timeout was missing the predicate because the optimizer hadn't been able to get to it yet.

    Take a look at the SELECT operator properties. It will tell you if the optimizer stopped because it found the best plan or because it timed out.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey (4/1/2011)


    On a guess, the optimizer timed out trying to unpack the views and the "best" plan at the time of the timeout was missing the predicate because the optimizer hadn't been able to get to it yet.

    Take a look at the SELECT operator properties. It will tell you if the optimizer stopped because it found the best plan or because it timed out.

    Which Grant posted about HERE [/url]....

    Unfortunately the links to the pictures have gone bad.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • David Benoit (4/1/2011)


    Grant Fritchey (4/1/2011)


    On a guess, the optimizer timed out trying to unpack the views and the "best" plan at the time of the timeout was missing the predicate because the optimizer hadn't been able to get to it yet.

    Take a look at the SELECT operator properties. It will tell you if the optimizer stopped because it found the best plan or because it timed out.

    Which Grant posted about HERE [/url]....

    Unfortunately the links to the pictures have gone bad.

    Crap. I moved the blog twice & it really messed things up. I'll go fix that post now. Sorry.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Fixed it. Sorry about that.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey (4/1/2011)


    Fixed it. Sorry about that.

    No worries here. That is a great post to cover the topic though. Thanks for fixing it. 🙂

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • That's awesome, thanks! I'm going to look this over and let you know what I find.

  • I read through the article and tried checking the properties of the SELECT statement, however I do not see the Reason for Early Termination property. I'm running SQL Server 2005 Standard Edition. Is this only for SQL Server 2008?

  • JohnnyDBA (4/4/2011)


    I read through the article and tried checking the properties of the SELECT statement, however I do not see the Reason for Early Termination property. I'm running SQL Server 2005 Standard Edition. Is this only for SQL Server 2008?

    No it works in 2k5 too.

Viewing 9 posts - 1 through 9 (of 9 total)

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